merging information from partial duplicate rows

1. merging information from partial duplicate rows

I have a spreadsheet with over 100,000 rows and need to condense this data.
I have multiple spellings of the same town, but the locatioin is identical.
What I have is this:

name / type / province/ country / lat / long / elev / pop /

belks / city / berg / afg / 36.25 / 72.45/ 1400 / 2300
belds/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
bolds/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
bends/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300

etc.
I would like to keep the different spellings but put that into one row in
excel. so that i had something like:

belks / belds / bolds / bends / city / berg / afg / 36.25 /
72.45/ 1400 / 2300

Thanks in advance for the help.

Todd

2. RE: merging information from partial duplicate rows

Assuming that you had all the cities grouped together in adjacent rows, you
could enter a column at the end and have a formula that started each city
(there're a few ways to use filters to make this easier, but it's not a great
thing to have to copy this formula) with the following formula:

Assuming A1-A3 were the following:

Berg
Bergen
Bergensta

B1
B2 formula =B1&" / "&A2
B3 formula (drag down from B2) = B2&" / "&A3

This would give you a building list of cities, as you dragged it down. With
each break in city, however (which you'd have to use some human logic to be
able to determine, but this is where sorting/filtering, if you're good with
it, can help greatly), you'd have to start with the blank, and then use the
formula on the 2nd cell and down to the end of that city. Otherwise, you'd
have each city running onto the next, and building a long list of names.

Hope that makes sense.

--
Boris

"Todd" wrote:

> I have a spreadsheet with over 100,000 rows and need to condense this data.
> I have multiple spellings of the same town, but the locatioin is identical.
> What I have is this:
>
> name / type / province/ country / lat / long / elev / pop /
>
> belks / city / berg / afg / 36.25 / 72.45/ 1400 / 2300
> belds/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
> bolds/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
> bends/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
>
> etc.
> I would like to keep the different spellings but put that into one row in
> excel. so that i had something like:
>
> belks / belds / bolds / bends / city / berg / afg / 36.25 /
> 72.45/ 1400 / 2300
>
> Thanks in advance for the help.
>
> Todd

3. RE: merging information from partial duplicate rows

That is the time intensive thing I was hoping to avoid, but it looks like I
might end up doing that anyways.

The real problem is that I have some records with 1 name, some with 2 some
with as many as 10 names.

And I was hoping that I could figure out a way to delete the duplicate
records once I've got them in a single row.

Todd
"BorisS" wrote:

> Assuming that you had all the cities grouped together in adjacent rows, you
> could enter a column at the end and have a formula that started each city
> (there're a few ways to use filters to make this easier, but it's not a great
> thing to have to copy this formula) with the following formula:
>
> Assuming A1-A3 were the following:
>
> Berg
> Bergen
> Bergensta
>
> B1
> B2 formula =B1&" / "&A2
> B3 formula (drag down from B2) = B2&" / "&A3
>
> This would give you a building list of cities, as you dragged it down. With
> each break in city, however (which you'd have to use some human logic to be
> able to determine, but this is where sorting/filtering, if you're good with
> it, can help greatly), you'd have to start with the blank, and then use the
> formula on the 2nd cell and down to the end of that city. Otherwise, you'd
> have each city running onto the next, and building a long list of names.
>
> Hope that makes sense.
>
> --
> Boris
>
>
> "Todd" wrote:
>
> > I have a spreadsheet with over 100,000 rows and need to condense this data.
> > I have multiple spellings of the same town, but the locatioin is identical.
> > What I have is this:
> >
> > name / type / province/ country / lat / long / elev / pop /
> >
> > belks / city / berg / afg / 36.25 / 72.45/ 1400 / 2300
> > belds/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
> > bolds/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
> > bends/ city / berg / afg / 36.25 / 72.45/ 1400 / 2300
> >
> > etc.
> > I would like to keep the different spellings but put that into one row in
> > excel. so that i had something like:
> >
> > belks / belds / bolds / bends / city / berg / afg / 36.25 /
> > 72.45/ 1400 / 2300
> >
> > Thanks in advance for the help.
> >
> > Todd

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1