Create list of unique records

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.

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?

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.