Not sure what is wrong

[Areallusernamestaken]

Hi,

I am puzzled by the following question.

This is the WRONG chart I have:

AF2: =(IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10000)/ISNUMBER($AC$1:$AD$10000),ROWS(AF$2:AF2))),""))

Instead, the AF column should correlate to the hours. Each ID should appear once if the person has only regular time and twice if the person has regular and OT time.

It should look like this:

Any idea what is wrong and how to fix it?

Thank you in advance!

 

[Fluff]

Try it like

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10000)/($AC$1:$AD$10000>0),ROWS(AF$2:AF2))),"")

 

[Areallusernamestaken]

Try it like

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10000)/($AC$1:$AD$10000>0),ROWS(AF$2:AF2))),"")

Thank you.
I don't believe it's working... For example, 2468 should appear twice in column AF because she has Reg and OT, but even with the change, it only appears once. Any thoughts?

 

[Fluff]

It works for me. Can you post some sample data using the XL2BB add-in

+Fluff 1.xlsm
	A																												AC	AD	AE	AF
1
2	1234																												0	8.5		1234
3	1235																												8.25	0		1235
4	1236																												2.25	5.75		1236
5	1237																												0	2		1236
6	1238																												2	0		1237
7	1239																												2	2		1238
8	1240																												2	0		1239
9	1241																													1		1239
10	1242																															1240
11	1243																															1241
12
Main

Cell Formulas
Range	Formula
AF2:AF12	AF2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10000)/($AC$1:$AD$10000>0),ROWS(AF$2:AF2))),"")

 

[Areallusernamestaken]

It works for me. Can you post some sample data using the XL2BB add-in

+Fluff 1.xlsm
	A																												AC	AD	AE	AF
1
2	1234																												0	8.5		1234
3	1235																												8.25	0		1235
4	1236																												2.25	5.75		1236
5	1237																												0	2		1236
6	1238																												2	0		1237
7	1239																												2	2		1238
8	1240																												2	0		1239
9	1241																													1		1239
10	1242																															1240
11	1243																															1241
12
Main

Cell Formulas
Range	Formula
AF2:AF12	AF2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10000)/($AC$1:$AD$10000>0),ROWS(AF$2:AF2))),"")

I appreciate your help.

Book1
	A	B																	S	T	U				Y			AB	AC	AD		AF	AG	AH
1	Emp. ID	Name																	Location	Reg	OT				Total Hours			Total Hours	Reg Hours	OT Hours		ID	CODE TO IMPORT	HOURS TO IMPORT
2	1234	Smith, April																	Florida	0	8.5				8.5			8.5	0	8.5		Emp. ID	REG	0
3	5678	Slacker, Barry																	Iowa	8.25	0				8.25			8.25	8.25	0		Emp. ID	OT	0
4	2468	Sila, Cathy																	Arizona	2.25	5.75				8			8	2.25	5.75		1234	REG	0
5	3579	Solar, Dennis																	Mississippi	0	7.75				7.75			7.75	0	7.75		5678	REG	8.25
6	2222	Slinger, Eddy																	California	7.75	0				7.75			7.75	7.75	0		2468	REG	2.25
7	3333	Sanchez, Fatima																	New York	7.5	0				7.5			7.5	7.5	0		2468	OT	5.75
8	4444	Sniper, Gregory																	Ohio	0	7.75				7.75			7.75	0	7.75		3579	REG	0
9	5555	Starteri, Heather																	Idaho	0	8.25				8.25			8.25	0	8.25		2222	REG	7.75
10	6666	Scuttle, Ian																	Nebraska	0	7.75				7.75			7.75	0	7.75		3333	REG	7.5
11																																4444	REG	0
12																																5555	REG	0
13																																6666	REG	0
Sheet1

Cell Formulas
Range	Formula
AB2:AB10	AB2	=IF(B2=B1,"",SUMIF(B:B,B2,Y:Y))
AC2:AC10	AC2	=IF(B2=B1,"",SUMIF(B:B,B2,T:T))
AD2:AD10	AD2	=IF(B2=B1,"",SUMIF(B:B,B2,U:U))
AF2:AF13	AF2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10000)/($AC$1:$AD$10000>0),ROWS(AF$2:AF2))),"")
AG2:AG13	AG2	=IF(AF2="","",IF(AF2=AF1,"OT","REG"))
AH2:AH13	AH2	=IF(AG2="","",SUMIFS(IF(AG2="REG",$AC$2:$AC$9987,$AD$2:$AD$9987),$A$2:$A$9987,AF2,$AB$2:$AB$9987,">0"))

 

[Fluff]

Other than you are pulling in the headers, I don't see a problem with that formula.
What do you think is wrong?

 

[Areallusernamestaken]

Other than you are pulling in the headers, I don't see a problem with that formula.
What do you think is wrong?

I'm pulling in the headers, but when I change it to $A2, it gets screwed up. Also, my AG and AH columns are no longer operating properly, so I guess I need to adjust those as well?

 

[Fluff]

Changing it to A2 works for me

+Fluff 1.xlsm
	A	B																	S	T	U				Y			AB	AC	AD	AE	AF
1	Emp. ID	Name																	Location	Reg	OT				Total Hours			Total Hours	Reg Hours	OT Hours		ID
2	1234	Smith, April																	Florida	0	8.5				8.5			8.5	0	8.5		1234
3	5678	Slacker, Barry																	Iowa	8.25	0				8.25			8.25	8.25	0		5678
4	2468	Sila, Cathy																	Arizona	2.25	5.75				8			8	2.25	5.75		2468
5	3579	Solar, Dennis																	Mississippi	0	7.75				7.75			7.75	0	7.75		2468
6	2222	Slinger, Eddy																	California	7.75	0				7.75			7.75	7.75	0		3579
7	3333	Sanchez, Fatima																	New York	7.5	0				7.5			7.5	7.5	0		2222
8	4444	Sniper, Gregory																	Ohio	0	7.75				7.75			7.75	0	7.75		3333
9	5555	Starteri, Heather																	Idaho	0	8.25				8.25			8.25	0	8.25		4444
10	6666	Scuttle, Ian																	Nebraska	0	7.75				7.75			7.75	0	7.75		5555
11																																6666
Main

Cell Formulas
Range	Formula
AB2:AB10	AB2	=IF(B2=B1,"",SUMIF(B:B,B2,Y:Y))
AC2:AC10	AC2	=IF(B2=B1,"",SUMIF(B:B,B2,T:T))
AD2:AD10	AD2	=IF(B2=B1,"",SUMIF(B:B,B2,U:U))
AF2:AF11	AF2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$10000)/($AC$2:$AD$10000>0),ROWS(AF$2:AF2))),"")