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?

 

[Toadstool]

Hi Pr1ncess426,

I think this does what you ask.

Pr1ncess426.xlsx
	B	C	D	E	F	G	H	I	J
1			GIVEN DATA				CLEANED UP DATA
2
3		1:00	2:00	3:00			1:00	2:00	3:00
4	a			purple		a	yellow		purple
5	a	yellow				b	red
6	b	red				c	black	violet	pink
7	c		violet			d	orange		green
8	c	black				e		white	brown
9	c			pink		f		grey
10	d	orange
11	d			green
12	e		white
13	e			brown
14	f		grey
WithOffset

Cell Formulas
Range	Formula
G4:G9	G4	=INDEX($B$4:$B$14,MATCH(0,INDEX(COUNTIF($G$3:$G3,$B$4:$B$14),),0))&""
H4:J9	H4	=IFERROR(INDEX(C$4:C$14,AGGREGATE(15,6,ROW($B$4:$B$14)-ROW($B$3)/((C$4:C$14<>"")*($B$4:$B$14=$G4)),1)),"")

 

[pr1ncess426]

Thank you, Toadstool! This is exactly what I am hoping to do.

I was able to get the G4 formula to work with my original data set, however I am having trouble applying the H4 formula. When I change the identifiers (a,b,c) to match my original data (which is numbers), the formula that outputs the matching colors no longer works.

Any suggestions as to why this might be?

 

[KRice]

Can you confirm some details about the worksheet you're putting together? You're able to construct the list of unique values that are in column B?...and that unique list is being built in column G?
And then the table of GIVEN DATA has column headers in row 3?...and the data begin on row 4?
Could you paste the formula you're using that corresponds to the H4 formula in @Toadstool's solution? I'm wondering if there is a mismatch in column or row specifiers.

 

[Toadstool]

Hi Pr1nces426,

Yes, the G column formula has &"" at the end because you showed the key as characters. To change to numeric just remove the &"" so the comparison works.

Pr1ncess426.xlsx
	B	C	D	E	F	G	H	I	J
1			GIVEN DATA				CLEANED UP DATA
2
3		1:00	2:00	3:00			1:00	2:00	3:00
4	1			purple		1	yellow		purple
5	1	yellow				2	red
6	2	red				3	black	violet	pink
7	3		violet			4	orange		green
8	3	black				5		white	brown
9	3			pink		6		grey
10	4	orange
11	4			green
12	5		white
13	5			brown
14	6		grey
Numbers

Cell Formulas
Range	Formula
G4:G9	G4	=INDEX($B$4:$B$14,MATCH(0,INDEX(COUNTIF($G$3:$G3,$B$4:$B$14),),0))
H4:J9	H4	=IFERROR(INDEX(C$4:C$14,AGGREGATE(15,6,ROW($B$4:$B$14)-ROW($B$3)/((C$4:C$14<>"")*($B$4:$B$14=$G4)),1)),"")

 

[pr1ncess426]

The worksheet I am building will be much larger with the identifiers occupying approximately cells A3:A1000. The built list of unique identifiers will begin in AB3. This formula (G4 from your previous example) works well with my data.

The GIVEN DATA has column headers in row 2, and the data begins in row 3. I was also concerned there might be a mismatch in the column or row specifiers, but when I looked through your formula and compared it to mine everything checked out.

The issue I am having is when I go to change the identifiers from the letters I gave in my example (a, a, b, c, c, c..), to my original data, which happens to be numerical, the H4 formula no longer populates the colors. It seems that I can change the identifiers to any string of letters, but if I try to put numbers there the formula stops working:

 

[pr1ncess426]

Just saw your other response. That did the trick! Thanks so much!

 

[pr1ncess426]

For some reason when I plug my original data into the H4 formula, only the FIRST color instance is showing up for each unique identifier in the Cleaned Up Data area. Any suggestions? Could this have anything to do with the fact that my original data is actually numerical as well? (Sorry - just realizing I should have provided an example with all numerical data since this is what I am actually working with. Won't make that mistake again!)

 

[KRice]

I notice that your unique list of numbers is left justified...are they actually formatted as text?

 

[pr1ncess426]

I'm not sure why they were showing up left justified in the screenshot I sent you. I just checked again and they are right justified now. I set the formatting of that column to "general" just to be sure.