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

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

Boris, thanks for the reply.

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

Sorry. Would likely require a bit of time put in. Happy to discuss doing
it, but hard to describe the different steps to go through that I'd consider
trying.
--
Boris


"Todd" wrote:

Boris, thanks for the reply.

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