Vlookup with sum and multiple returns

[Indominus]

Hello. I have workbook that lists badge numbers in column A. Hours in column H. I want to be able to add up all the hours by badge id. The problem is the a badge number can appear multiple times. So a regular Vlookup will not work. I have also tried a sumif formula. This adds them up however, it shows the duplicate and I am not looking for the duplicate to be there. This workbook can have about 5,000 rows so I am not sure exactly how this can be done. The final result I just want all the hours added up in one row with no duplicates. Attached is a sample file for an easy example. John should be added up to 5 hours. Mary added up to 11 hours. David 7 hours. Thank you!

 

[Fluff]

Can you post some sample data including expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[kweaver]

How about this?

(edited)

Book3
	A	B	C	D	E	F	G	H	I
1	Badge	Emp	M Name	Units	QTY	Hrs D	Hrs I	Hrs T
2	1234	John						3	5
3	9876	Mary						5	21
4	1234	John						2
5	9876	Mary						6
6	5555	David						7	7
7	9876	Mary						10
8	1111	Kevin						40	40
Sheet1

Cell Formulas
Range	Formula
I2:I8	I2	=IF(1=COUNTIF($A$2:A2,A2),SUMPRODUCT((A2=$A$2:$A6)*($H$2:$H6)),"")

 

[kweaver]

On second thought, I think I'd change I2 formula where there's a 6 (A6 and H6) to be something like $A$10 and $H$10 or $A$100 and $H$100 ?

 

[Indominus]

(edited)

Book3
	A	B	C	D	E	F	G	H	I
1	Badge	Emp	M Name	Units	QTY	Hrs D	Hrs I	Hrs T
2	1234	John						3	5
3	9876	Mary						5	21
4	1234	John						2
5	9876	Mary						6
6	5555	David						7	7
7	9876	Mary						10
8	1111	Kevin						40	40
Sheet1

Cell Formulas
Range	Formula
I2:I8	I2	=IF(1=COUNTIF($A$2:A2,A2),SUMPRODUCT((A2=$A$2:$A6)*($H$2:$H6)),"")

This would work! Now is there anyway to remove each row when the respective I cell is not showing a value? That way I can have just a list going down. Names and hours worked going down with no extra rows showing. Not sure if there’s a way as these cells in the rows I want to delete are obviously being referenced by the formula. I just want it to show somewhere in the following format if possible. Thank you so much for your help by the way!
John 5
Mary 21
David 7
Kevin 40

 

[kweaver]

I don't think so. I suspect, on first thought, you'd have to use VBA elsewhere on the sheet or filter this column to show only values.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Badge	Emp	M Name	Units	QTY	Hrs D	Hrs I	Hrs T
2	1234	John						3			John	5
3	9876	Mary						5			Mary	21
4	1234	John						2			David	7
5	9876	Mary						6			Kevin	40
6	5555	David						7
7	9876	Mary						10
8	1111	Kevin						40
9
10
Data

Cell Formulas
Range	Formula
K2:K8	K2	=IFERROR(INDEX(B2:B100,AGGREGATE(15,6,(ROW(B2:B100)-ROW(B2)+1)/(ISNA(MATCH(B2:B100,K$1:K1,0)))/(B2:B100<>""),1)),"")
L2:L8	L2	=IF(K2="","",SUMIFS(H:H,B:B,K2))

 

[kweaver]

You can always count on FLUFF to prove almost anything is possible!

 

[Indominus]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Badge	Emp	M Name	Units	QTY	Hrs D	Hrs I	Hrs T
2	1234	John						3			John	5
3	9876	Mary						5			Mary	21
4	1234	John						2			David	7
5	9876	Mary						6			Kevin	40
6	5555	David						7
7	9876	Mary						10
8	1111	Kevin						40
9
10
Data

Cell Formulas
Range	Formula
K2:K8	K2	=IFERROR(INDEX(B2:B100,AGGREGATE(15,6,(ROW(B2:B100)-ROW(B2)+1)/(ISNA(MATCH(B2:B100,K$1:K1,0)))/(B2:B100<>""),1)),"")
L2:L8	L2	=IF(K2="","",SUMIFS(H:H,B:B,K2))

So this works for awhile but then on row 397 it stops and creates large gaps of empty rows. Some cells filled with the names and hours via formula here and there. But large gaps. The first name and hours it does not get is row 479

 

[Fluff]

The ranges in the formula should be locked like

=IFERROR(INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW($B$2:$B$100)-ROW($B$2)+1)/(ISNA(MATCH($B$2:$B$100,K$1:K1,0)))/($B$2:$B$100<>""),1)),"")