+ Reply to Thread
Results 1 to 3 of 3

merging information from partial duplicate rows

  1. #1
    Todd
    Guest

    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. #2
    BorisS
    Guest

    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. #3
    Todd
    Guest

    RE: merging information from partial duplicate rows

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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