Consolidate multiple entries

[Karthik]

Hi All,

I've a query data which has multiple entries in Column A and B and different
info in Columns C-H.

I'm trying to consolidate this data, but couldn't find a solution. Is there
any way to delete blank fields and reduce the number of rows. I've over
45,000 rows data.

A B C D E F G H
123 Zip EMP1
123 Zip EMP2
123 Last Name
123 Last Name EMP2 STAPLETON
112 ODS SC066 111208
112 ODS SC284
112 ODS
112 ODS SC176

I want the final data to look like this

A B C D E F G H
123 Zip EMP1 EMP2
123 Last Name EMP1 EMP2 STAPLETON
112 ODS SC284 SC176 SC066 111208


Thanks for your help in advance....

 

[Daryl S]

Karthik -

Here is one way to do it. I am assuming there is always something in
columns A and B. Make a copy of your spreadsheet and work on the copy. I
will assume your data is in rows A-H, and there is a title row in column 1
(this is needed for the formulas to work). I like to skip a column when I am
adding new data, so I would skip column I and then starting in J2, enter 1
and increment this down the column (you can enter a few and drag, or in J3
enter the formula =J2+1 and copy/paste or drag that down. This column will
preserve sort order for later.

You can copy the column titles from C2:H2 into K22.

In cell K2 enter this formula:
=IF(AND($A2=$A1,$B2=$B1),CONCATENATE(K1,C2),IF(LEN(C2)>0,C2,""))

Copy/Paste or drag this formula to the range of cells under columns K
through P for all rows in the spreadsheet. Notice that the last row for
each unique combination in columns A and B has the data you wish, but we need
to remove the duplicate incomplete rows.

In cell Q2, enter this formula:
=IF(AND($A2=$A1,$B2=$B1),"","PickMe")

Copy/Paste or drag this down the column This will put the text "PickMe" in
the last row of each combination of A and B, and leave the rest blank.

Select columns J through Q and copy / paste special values. Don't miss
this step, or the rest won't work...

Now, select columns A through Q and sort on column Q. You can delete all
rows below the header that don't have PickMe in them. You can return the
sort order by selecting the entire range and sorting by column J. You can
also delete columns B through J. You can delete column Q (they are all
PickMe anyway).

Only slightly tedious, but for a one-time conversion it will work. If you
missed a step, just pull another copy of your original data.

Hope that helps!