Combine data from different rows matching multiple criteria into one row

[pr1ncess426]

I have a large amount of data that represents colors at different time points for specific identifiers (a, b, c, and so on...). The identifiers are listed with their corresponding color/time point, however if a single identifier has multiple colors (and therefore time points) assigned to it then that identifier is listed several times (each new color/time point combo gives populates as a new row assigned to that identifier).

I need to create a cleaned up spreadsheet where each identifier is only listed once (one row per identifier) with the columns next to it containing all of the corresponding color and time point data on that same row.

I would like the final product to look something like this:

1:00PM 2:00PM 3:00PM
a yellow purple
b red
c black violet pink
d orange green


Due to the specifics of my project, I need to clean up the data using only an excel formula that will update automatically even as my "given data" input changes. I'm also trying to avoid using helper columns or clunky formulas as much as possible, as my original data set is rather large. Any suggestions?

 

[pr1ncess426]

Toadstool - I copied and pasted your "Pr1ncess426.xlsx" example into a new worksheet and it is working well for me too.

However, when I copy and paste my original data values into your example in this new worksheet, the cleaned up data only shows some of the values again.

I too was worried there might be some hidden characters or formatting involved however the problem is not solved when I re-enter the numbers by directly typing them into my data table.

Any thoughts? We are so close!

 

[pr1ncess426]

I can copy in my original data identifiers just fine (column A), but the problem arises when I try to copy in the numbers that correspond to each time point (columns B through Y).

 

[KRice]

@pr1ncess426 , would you consider installing the XL2BB add-in (links on this page and in my signature block)? Then select A1:D17 in your worksheet and use the "MrExcel" that will appear on your Excel toolbar to copy that portion of your sheet to the clipboard, which can then be pasted directly into your post on this board. That should give us a sampling of some values that are and are not being picked up.