How to merge rows with Excel?

I have a worksheet containing:

ID        VALUE1    VALUE2
--------------------------
ABC       8274
DEF       613
GHI       236
ABC                 11.13
XYZ                 173   
GHI                 999

I'd like to merge rows and have:

ID    VALUE1   VALUE2
---------------------
ABC   8274     11.13
DEF   613
GHI   236      999
XYZ            173

How to do such a row merging with Excel?

This is a little bit different from How to merge Excel worksheets by stacking columns, using an ID column?, in the sense that we only have one input worksheet, with columns already separated.

Basj

Posted 2019-12-20T14:05:44.547

Reputation: 1 356

If you want to do this in-place - VBA is the only option. If you want to do this in separate region - use PowerPivot, create pivot, or use SUMIF(). – Akina – 2019-12-20T14:08:24.317

@Akina I'd like to avoid doing it with VBA programming ; I already do this with CSV files + Python programming, it works, but I was curious if there exists a way directly in the GUI, without code. PS: Could you show your method with PowerPivot / pivot? – Basj – 2019-12-20T14:20:05.960

Create pivot table using master. Get MAX() value and disable totals. – Akina – 2019-12-20T14:33:26.860

are the IDs and VALUE names unique? If so, just create a table on another sheet and use INDEX MATCH to populate the values – Alex M – 2019-12-20T18:05:23.280

@AlexM Rows with same IDs should be merged. VALUE names are not unique, it can be a price, etc. so many products can have the same price – Basj – 2019-12-20T20:05:08.007

@Akina Can you give an example showing how to do this (pivot table using master)? – Basj – 2019-12-20T20:05:36.133

If VALUE names are not unique, I don't know that there's a way to accomplish this. You're saying there could be a row ABC with data under VALUE1 in column B and another row ABC with data under VALUE1 in a different column, and you want to be able to merge those rows to show both values? – Alex M – 2019-12-20T20:11:54.407

@AlexM This is not possible, VALUE1 is always in column B, VALUE2 is always in column C. About uniqueness: if a row with ID ABC has VALUE1 8274, then other rows with ID ABC won't have any VALUE1. (Otherwise it would be impossible to merge). Maybe this is what you meant about uniqueness? – Basj – 2019-12-20T20:25:16.540

Just use a simple pivot table. Id -->Rows area; Value1 & Value2 to Values area. – Ron Rosenfeld – 2019-12-22T01:09:27.557

How to merge rows with Excel?

No answers