asked on

Excel - need to merge rows where certain fields have different data but are otherwise the same record

Let us say I have 3 rows that have information for the same person - person xxxx, three different phone numbers

xxxx 666-7777
xxxx 777-6666
xxxx 676-6767

Is there a technique/software to merge these rows so I end up with a single row
as follows:

xxxx 666-7777 777-6666 676-6767

As there are several thousand records I want to do this to, manual is not an option.

zorvek (Kevin Jones)

Please give some examples of xxxx.

Kevin

Line One

ASKER

Thanks for the quick response.

XXXX would be a name e.g. John so what we have are three rows:

John 666-7777
John 777-6666
John 676-6767

and the end result desired is:

John 666-7777 777-6666 676-6767

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Line One

ASKER

Awesome. I am going to try it right now.

Line One

ASKER

Got debug error and yellow highlight on:

CurrentName = Left(CurrentName, InStr(CurrentName, " ") - 1)

zorvek (Kevin Jones)

Then your data was not presented correctly.

Please give some examples of xxxx.

Real examples.

Kevin

Line One

ASKER

rows-to-be-condensed-example-spr.xlsx

zorvek (Kevin Jones)

The workbook you posted does not contain any data that looks like your data posted above.

Please clarify your objective.

Kevin

Line One

ASKER

Rows 7, 8 and 9 are replaced by a single row like row 12 per the attached.
rows-to-be-condensed-example-spr.xlsx

SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Line One

ASKER

Thanks. I will give this a shot.

Line One

ASKER

Awesomer! Sort of magical.

What I did was substitute:

Public Sub ConsolidateRowsDefault()

ConsolidateRows ActiveSheet.UsedRange, Array(2)

End Sub

for :

Public Sub ConsolidateRowsDefault()

ConsolidateRows ActiveSheet.UsedRange, Array(1)

End Sub

I then ran it. Everything concatenated as you said.

Only one small difference which doesn't seem to have anything to do with concatenation:

Column BZ originally had:
"""Dsilva (dsilva@vertucis.com)"" <Dsilva (dsilva@vertucis.com>
""Dsilva (Dsilva@yetmoremail.com)"" <Dsilva@yetmoremail.com>
"
but the condensed/concatenated result version Column BZ had:

"""Dsilva (dsilva@vertucis.com)"" <Dsilva (dsilva@vertucis.com>
""Dsilva (Dsilva@yetmoremail.com)"" <Dsilva@yetmoremail.com>
"

Some type of formatting issue - it seems to have just been cut off.

Line One

ASKER

Any comment on the problem?

zorvek (Kevin Jones)

I don't see any difference in the before and after strings for the "BZ" value. Is there a problem and, if so, what, exactly, is the problem?

Kevin

Line One

ASKER

I will dig up the original - the cut-and-paste seems to have been the same - must not have changed the focus. Sorry. I will dig up the original files and provide the correct before and after.

Line One

ASKER

I can't seem to find the items - not a big issue, doesn't take away from the great solution provided. Thanks again.