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]

Changing the format won't actually change the way it's treated. Here's an example with all numeric and works OK.
Try the K18 and L18 TYPE entries. Both should return 1 to indicate a number and then the ($A$18:$A$28=$F18) will work.

Pr1ncess426.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1			GIVEN DATA				CLEANED UP DATA
16
17		1:00	2:00	3:00			1:00	2:00	3:00
18	1			22		1	33		22		1	1
19	1	33				2	44
20	2	44				3	66	55	77
21	3		55			4	88		99
22	3	66				5		111	222
23	3			77		6		333
24	4	88
25	4			99
26	5		111
27	5			222
28	6		333
Numbers3

Cell Formulas
Range	Formula
F18:F23	F18	=INDEX($A$18:$A$28,MATCH(0,INDEX(COUNTIF($F$17:$F17,$A$18:$A$28),),0))
G18:I23	G18	=IFERROR(INDEX(B$18:B$28,AGGREGATE(15,6,ROW($A$18:$A$28)-ROW($A$17)/((B$18:B$28<>"")*($A$18:$A$28=$F18)),1)),"")
K18	K18	=TYPE(A18)
L18	L18	=TYPE(F18)

 

[KRice]

I agree, it sounds as if the cell contents are being treated as text. You might try placing a 1 in some cell outside the table. Copy to the clipboard and then select your F column cells where the formulas are giving a problem and perform Paste > Special (Multiply) to force a conversion to numeric values.

 

[pr1ncess426]

Thanks, Kirk. Would highlighting all of my data and formatting those cells as "Number" accomplish the same thing? I tried doing this but am still not getting outputs beyond the first instance (as described in my previous message).

 

[Toadstool]

As previously stated the changing of the format doesn't change how it's treated.
What were the results of checking the type of your data and the unique list?
=TYPE(A18)
=TYPE(F18)

 

[KRice]

@Toadstool's advice is spot on...trying to format the cells as Number is not sufficient. Sometimes you have to coerce the values to convert by doing something different from just changing the cell format.

 

[pr1ncess426]

Checking the data type yields numbers (1 and 1).

Sorry for the tiny text, but here is an example with my original data that depicts what I am seeing now.

 

[KRice]

So it looks as if the formula is not picking up some values in the source/data table? For example, 111,11.9 and 111,15 are missing in the summary? I don't see any issues with your version. Here's a working version that I set up to maintain the same row/column setup as you. I used a slightly modified version of the unique list formula (that must be confirmed as a array formula by entering with Ctrl-Shift-Enter), but that shouldn't affect the other formula. Copy my AB3 formula and paste it into your AB3 cell and then copy across that row to see if it pulls all of the values...I may be missing something in the screenshot you sent.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2	ID	0:00	1:00	2:00	3:00	4:00																					ID	0:00	1:00	2:00	3:00	4:00
3	111	12.5																									111	12.5	99	11.9	14
4	111		99																								222	66	10	16.8
5	111			11.9																							333	10	12	15	16	17
6	111				14
7	222		10
8	222	66
9	222			16.8
10	333		12
11	333			15
12	333	10
13	333				16
14	333					17
Sheet5

Cell Formulas
Range	Formula
AA2:AF2	AA2	=A2
AA3:AA14	AA3	=IFERROR(INDEX($A$3:$A$1000,MATCH(0,COUNTIF($AA$2:$AA2,$A$3:$A$1000&"") + IF($A$3:$A$1000="",1,0), 0)), "")
AB3:AF14	AB3	=IFERROR(INDEX(B$3:B$1000,AGGREGATE(15,6,ROW($A$3:$A$1000)-ROW($A$2)/((B$3:B$1000<>"")*($A$3:$A$1000=$AA3)),1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[pr1ncess426]

I copied in both the new AA3 and AB3 formulas and pressed CTRL+SHIFT+ENTER as you suggested. When I drag the AB3 formula to fill the remaining cells in that row (AB3->AY3), my cleaned up data is still missing values (11.9, 14, 15, etc... for identifier 111 for example).

I noticed your working version only goes through column F, but mine goes through column Y. Could this have anything to do with it?

 

[Toadstool]

It's working for me as well. I've added an IFERROR around the unique Id column AA and you can get rid of the last 0 by formatting as Custom ##### but I'm getting the multiple entries.

Please note: The 222 Id has a 66 and a 12 in column B on rows 17 and 24 so this will only pick up the 66, i.e. the first.

I've only entered data for 111 and 222 and I've hidden the columns with empty data/results.

Pr1ncess426.xlsx
	A	B	C	D														R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD															AS	AT	AU	AV	AW	AX	AY
2	ID	12:00 AM	1:00 AM	2:00 AM														4:00 PM	5:00 PM	6:00 PM	7:00 PM	8:00 PM	9:00 PM	10:00 PM	11:00 PM			12:00 AM	1:00 AM	2:00 AM															5:00 PM	6:00 PM	7:00 PM	8:00 PM	9:00 PM	10:00 PM	11:00 PM
3	111																		12.5								111	10.4	99	14															12.5	11.9	15	9.2	11	9.4	11
4	111		99																								222	66																		16.8		9.2		14.5
5	111																			11.9							0
6	111			14
7	111																				15
8	111																					9.2
9	111
10	111																						11
11	111																							9.4
12	111
13	111																								11
14	111	10.4
15	111
16	222	66																10
17	222
18	222																			16.8
19	222
20	222																					9.2
21	222
22	222																							14.5
23	222
24	222	12
25	222
Numbers4

Cell Formulas
Range	Formula
AS2:AY2,AC2:AD2,R2:Y2,C2:D2	C2	=B2+TIME(1,0,0)
AA3:AA25	AA3	=IFERROR(INDEX($A$3:$A$1000,MATCH(0,INDEX(COUNTIF($AA$2:$AA2,$A$3:$A$1000),),0)),"")
AS3:AY16,AB3:AD16	AB3	=IFERROR(INDEX(B$3:B$1000,AGGREGATE(15,6,ROW($A$3:$A$1000)-ROW($A$2)/((B$3:B$1000<>"")*($A$3:$A$1000=$AA3)),1)),"")
AS17:AY25,AB17:AD25	AB17	=IFERROR(INDEX(B$3:B$13,AGGREGATE(15,6,ROW($A$3:$A$13)-ROW($A$2)/((B$3:B$13<>"")*($A$3:$A$13=$AA17)),1)),"")

 

[KRice]

I'm curious...for any one of the missing values, try deleting that value from your data table and then reenter the number and tell us if the summary table picks it up. I'm wondering if some hidden characters might be involved.