+ Reply to Thread
Results 1 to 12 of 12

Merging rows with the same customer name but different data values.

  1. #1
    Registered User
    Join Date
    12-28-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2013 Preview
    Posts
    7

    Merging rows with the same customer name but different data values.

    Hello guys.
    While the title might be a bit confusing, i will try to explain it better here.
    I have a excel spreadsheet of about 10000 lines long. It's build up like this:

    http://img18.imageshack.us/img18/5191/exampledx.png

    There are, i guess, about three thousand different customers.
    For every customer i have three rows. One with the data from 2010,
    one with the data from 2011 and one if the data from 2012.

    As you might be able to guess now, i want to merge them in a way so that
    i have one row with the data for all the years instead of three rows.

    I hope you get my point and that you can help me.

    Thanks in advance,
    Berry

    (PS: I'm Dutch, so there might be a couple grammar mistakes. Sorry for that.)
    Last edited by berryh; 12-28-2012 at 11:18 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Merging rows with the same customer name but different data values.

    Hi berryh,

    Welcome to the forum. You should upload a small sample workbook - contributors are rarely keen to rebuild your spreadsheet as a precursor to assisting you.

    And if I can ever speak (type) a foreign language as well as you can English, well, I'll be pretty pleased! Excellent.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  3. #3
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Merging rows with the same customer name but different data values.

    Easily done in a small number of manual steps.
    Have a look at the attached.
    Attached Files Attached Files
    If I've been of help, please hit the star

  4. #4
    Registered User
    Join Date
    12-28-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2013 Preview
    Posts
    7

    Re: Merging rows with the same customer name but different data values.

    Thanks so much for the quick help!
    But.. i just saw that the document i have is a little messier then i first thought.
    It are statistics from a company here in Holland, so the data is a bit sensitive.
    I'll give you a little part so that i can explain what the next problem is.

    There are some names that have only ordered something in two or one of the three years.
    How can i handle that? Remember, the document is 10000 lines long, so manual work is not really loved...

    Thanks in advance,
    Berry
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-28-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2013 Preview
    Posts
    7

    Re: Merging rows with the same customer name but different data values.

    Quote Originally Posted by BB1972 View Post
    Hi berryh,

    Welcome to the forum. You should upload a small sample workbook - contributors are rarely keen to rebuild your spreadsheet as a precursor to assisting you.

    And if I can ever speak (type) a foreign language as well as you can English, well, I'll be pretty pleased! Excellent.
    Thanks

    See the post above, just added an example!

  6. #6
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Merging rows with the same customer name but different data values.

    Berry, will there definitely only be one entry per year, per company?

  7. #7
    Registered User
    Join Date
    12-28-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2013 Preview
    Posts
    7

    Re: Merging rows with the same customer name but different data values.

    Quote Originally Posted by BB1972 View Post
    Berry, will there definitely only be one entry per year, per company?
    Yes. There will only be one entry per year, per company. I can even get the a separate file for every year if you want it.

  8. #8
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Merging rows with the same customer name but different data values.

    Okay, well if you have a separate file for each year, then let's take the path of least resistance Have a look at the attached, where I have a tab for each year, and have copied your information for each year to the relevant tab.

    I presume you have a master list of all the companies somewhere? If so, put that in Column A of the Yearly Totals tab, as I have done. Then the INDEX/MATCH formula in eg B2 looks for a match for A2 (Haardhout Ferwerd) in Column B of tab 2010, and if it finds a match, it returns the correspnding value from Column D (2010 tab) to Column B (Yearly Totals).

    Changing the tab references etc in Columns C & D (Yearly Totals tab) allows you to look in the other tabs (2011, 2012) in a similar manner.

    Adjust ranges etc to suit, and you should be sorted

    Hope this helps,
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-28-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2013 Preview
    Posts
    7

    Re: Merging rows with the same customer name but different data values.

    Quote Originally Posted by BB1972 View Post
    Okay, well if you have a separate file for each year, then let's take the path of least resistance Have a look at the attached, where I have a tab for each year, and have copied your information for each year to the relevant tab.

    I presume you have a master list of all the companies somewhere? If so, put that in Column A of the Yearly Totals tab, as I have done. Then the INDEX/MATCH formula in eg B2 looks for a match for A2 (Haardhout Ferwerd) in Column B of tab 2010, and if it finds a match, it returns the correspnding value from Column D (2010 tab) to Column B (Yearly Totals).

    Changing the tab references etc in Columns C & D (Yearly Totals tab) allows you to look in the other tabs (2011, 2012) in a similar manner.

    Adjust ranges etc to suit, and you should be sorted

    Hope this helps,
    I'm going to try this now, thanks for the information.
    I'll let you know once i'm done and if it worked or not.
    Thanks again!

    Berry

  10. #10
    Registered User
    Join Date
    12-28-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2013 Preview
    Posts
    7

    Re: Merging rows with the same customer name but different data values.

    Quote Originally Posted by BB1972 View Post
    Okay, well if you have a separate file for each year, then let's take the path of least resistance Have a look at the attached, where I have a tab for each year, and have copied your information for each year to the relevant tab.

    I presume you have a master list of all the companies somewhere? If so, put that in Column A of the Yearly Totals tab, as I have done. Then the INDEX/MATCH formula in eg B2 looks for a match for A2 (Haardhout Ferwerd) in Column B of tab 2010, and if it finds a match, it returns the correspnding value from Column D (2010 tab) to Column B (Yearly Totals).

    Changing the tab references etc in Columns C & D (Yearly Totals tab) allows you to look in the other tabs (2011, 2012) in a similar manner.

    Adjust ranges etc to suit, and you should be sorted

    Hope this helps,
    Brendan, Sir,
    You're a hero!
    After tweaking your code a little so that it compares the customer numbers and not the names, (That created duplicate numbers of some sort) it worked outstandingly!
    I would never have come up with this code! Thanks so much, you saved me a LOT of work!

    Thanks once again!
    Berry

  11. #11
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Merging rows with the same customer name but different data values.

    Berry,

    Delighted to help ;thanks for the feedback, which isn't always given; and fair play to you for tweaking the formula to get it to do what you want. You might be good enough to mark this thread as "Solved", provided you're happy that it is.

  12. #12
    Registered User
    Join Date
    12-28-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2013 Preview
    Posts
    7

    Re: Merging rows with the same customer name but different data values.

    I'll do that now!

    Probably hang around here some more.

    Thanks for the help.
    Berry

+ 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