# Consolidate duplicate rows based on upper/lowercase

1. ## 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.

2. ## Re: Consolidate duplicate rows based on upper/lowercase

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.

3. ## Re: Consolidate duplicate rows based on upper/lowercase

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

4. ## Re: Consolidate duplicate rows based on upper/lowercase

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

5. ## Re: Consolidate duplicate rows based on upper/lowercase

Thank you for the replies.

I used alansidman's macro as it preserves all the data and creates convenient grouped rows, worked great.

6. ## Re: Consolidate duplicate rows based on upper/lowercase

There are currently 1 users browsing this thread. (0 members and 1 guests)