Friday, May 6, 2011

yogi_Create An Arrayformula That Displays Only Unique Submitters

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
yachdhoo said:
Column B Lists names of user submissions, which continue to grow.
Unfortunately, these do not represent unique users, as some have the same name.
Furthermore, each user submits more than once, and sometimes under a different name each time! (nick name, initials, etc).
Column C is a URL for each user, ending in that user's unique 9-12 digit alphanumeric, as in:
http://www.xxx~~~php?id=123xyz789
(The URL might be different for each user each time, but not the Unique identifier at the end, after the "=")
So, this 9-12 digit alphanumeric is the only way to correctly identify a unique submitter.
Therefore, on another sheet, I'd like to have one arrayformula that results in one row for each unique user (based on the 9-12 digit alphanumeric at the end of the URL they submit) with just two columns of data, auto-populating with additional submissions:
Column A with their names hyperlinked to a specific alternate URL ending in their alphanumeric (hyperlink("http://www.newphp?id=123xyz789","Name Submitted")
Column B with just their unique 9-12 digit alphanumeric, which is at the end of the URL they submit, after a "=").
...and where B has two entries for A, just return the most recent A (if different by way of initials, nickname, fullname, etc...)
Is this possible?
-------------------------------------------------------------------

No comments:

Post a Comment