Combine rows based on values in column 1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marty L - 04 Sep 2006 17:09 GMT

Is there an excel feature that would let you combine rows based on the
unique value of a column? The DATA, SUBTOTAL almost does what I want,
except it doesn't work with text. For example:

A X
B Y
C Z
A X
C Z
C J
B M

Combine to:

A X X
B M Y
C Z J Z

I can write a routine that will do this - but it is a little tedious: sort,
then row by row & column by column moving the last non-empty value down to
the next row until col 1 changes, then deleting all rows prior to my 'total'
row with the same value in col 1.

On a related topic - it would be nice to have an easy way to take a list
such as col 1 above and convert it to a unique list, as in col 1 in the
'combine to' example above.

Reply to this Message

Pete_UK - 04 Sep 2006 21:07 GMT

On your last point, you can obtain unique values quite easily using
Advanced Filter. Copy your list of values (including a heading) into
column A of a new sheet. With the data still highlighted, select Data |
Filter | Advanced Filter - in the pop-up select "Unique Records only"
and "Copy to another location" (specify $C$1). Click OK, and you will
have your unique list in column C. If you do not have a header, then
the first value will appear twice in the list.

Hope this helps.

Pete

> Is there an excel feature that would let you combine rows based on the
> unique value of a column? The DATA, SUBTOTAL almost does what I want,
[quoted text clipped - 22 lines]
> such as col 1 above and convert it to a unique list, as in col 1 in the
> 'combine to' example above.

Reply to this Message