Create list of unique records

[SueWithQuestion]

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]

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]

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]

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:

      --A-- ----B---- --C-- --D-- ----E---- --F-- --G-- ----H---- --I--
  1   Query    ID     Score Query    ID     Score Query    ID     Score
  2   Q1    BZB480392  59    Q2   BZB497865  65    Q3   BZB513731  67  
  3   Q1    BZB472532  59    Q2   BZB498844  65    Q3   BZB463750  64  
  4   Q1    BZB497865  59    Q2   BZB513731  62    Q3   BZB472532  64  
  5   Q1    BZB439374  58    Q2   BZB472532  59    Q3   BZB498844  62

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:

      --A-- ----B---- --C--
  1   Query    ID     Score
  2    Q4   BZB420306  54  
  3    Q4   BZB420676  50  
  4    Q3   BZB425296  53  
  5    Q3   BZB426247  51  
  6    Q3   BZB430604  50  
  7    Q4   BZB432794  53  
  8    Q2   BZB433021  55  
  9    Q3   BZB433021  56

That takes five seconds. Now you have a database.

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

[SueWithQuestion]

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]

Then sort the whole ensemble by ID and Query:

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.