Create list of unique records

**SueWithQuestion** · 07-06-2010, 05:44 PM

Attached is a sample file.

I'd column A to have a list of all the unique IDs from columns D, F, H, J, L and N.

For each unique ID, I'd like column B to list each of the queries it was included in.

For example, the ID value BZB472532 appears as the second value for Q1, and as the first value in Q5 (and maybe other places, too, I don't know). So, it will be one of the Unique IDs, and in column B for that Unique value, it should list "Q1, Q5" (or just: 1, 5).

Finally, I'd like column C to list the highest score each Unique ID received.

I hope this makes sense.

I'm open to columns A, B and C staying where they are, or actually not being on the same worksheet as the raw data as it is in this Sample file. I put everything in this one sample worksheet for ease of viewing.

Can someone help me with this - and explain it to me, too, so I can keep learning about Excel?

**shg** · 07-06-2010, 06:18 PM

Do you have any VBA experience? If not, do you have your heart set on using VBA rather than taking two minutes to do it manually?

**SueWithQuestion** · 07-06-2010, 07:05 PM

I definitely do not have my heart set on using VBA! I wish I knew a two minute way of doing it manually.

How can I do that?

Oh - one other thing: I'll potentially want to do this repeatedly over different raw data sets.

What do you suggest?

**shg** · 07-06-2010, 09:06 PM

Delete the first three columns in your example, then insert a new column before each dataset. In those columns, duplicate the Query, so that the data look, in part, like this:

Please Login or Register  to view this content.

That takes 30 seconds (use Ctrl+Enter to enter the same value in multiple cells).

Then select the data (not including headers) for Q2, and drag it to a2:C2 with the Shift key pressed (that means Move). Repeat for Q3 through Q6 data. Now all the data is in three columns, head to tail.

That takes another 30 seconds.

The sort the whole ensemble by ID and Query:

Please Login or Register  to view this content.

That takes five seconds. Now you have a database.

For the remaining 55 seconds, sip your coffee and admire your work.

**SueWithQuestion** · 07-07-2010, 09:55 AM

Thank you!

I still have the problem of removing the duplicate records (i.e., same ID value) and keeping the record with the highest score. How can I do that?

**shg** · 07-07-2010, 10:19 AM

Originally Posted by shg

Then sort the whole ensemble by ID and Query:

Originally Posted by SWQ

removing the duplicate records (i.e., same ID value) and keeping the record with the highest score. How can I do that?

Instead of sorting by ID and query, sort by ID (ascending)and Score (descending).

Then in D1, enter the header myFormula.

In D2, enter and copy down a formula that returns "x" if the row should be deleted.

Copy col D and replace with values.

AutoFilter on col D for x, delete visible rows, unfilter.

The formula I leave to you.