Consolidate duplicate rows based on upper/lowercase

Hello

I have a worksheet filled with rows of data where multiple rows may share the same heading, just in lower/uppercase. Is there a way to consolidate duplicates based on a certain column, then add their values for the rest of the columns? This is what I mean:

Say I have this to start:

MIUoIEm.png

I want to consolidate all duplicates of "name" into one row, then add their metrics, so I end up with only 2 rows instead of 6:

5Ok8AIx.png

Can anyone point me in right direction? Thanks.

Firstly, run the following macro to put all the names in Proper order

Please Login or Register  to view this content.

Then if the names are in alpha order, you can use the sub totals functionality found on the Data tab of the ribbon.

Hi,

If in G1 put Name, in H1:J1 is Metric1 to Metric3 then,
in G2 John and G3 JANE (no matter how you write that name)
In H2 =SUMPRODUCT(($A$2:$A$7=$G2)*(B$2:B$7)) then drag formula to right, then down.


....G..............H...............I...............J
Name........Metric 1.....Metric 2.....Metric 3
John.............7.............10..............11
JANE.............2............10..............17

Enter formula to get unique name list in F2 and copy down

Formula:

Please Login or Register  to view this content.

then enter in G2 and copy across and down

Formula:

Please Login or Register  to view this content.

Or this

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G	H	I
1	Name	Metric 1	Metric 2	Metric 3			Metric 1	Metric 2	Metric 3
2	John	2	3	4		John	7	10	11
3	john	2	2	1		Jane	2	10	17
4	jOhn	3	5	6
5	jAne	1	5	7
6	jane	0	3	4
7	JANE	1	2	6

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.