Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Consolidate multiple rows of data into single row

  1. #1
    Join Date
    28th July 2006
    Posts
    5

    Consolidate multiple rows of data into single row

    I imagine my goal could be achieved via some scripting code, but, alas, I don't do vbs. Here's the situation:
    I have a spreadsheet generated by another office which lists individuals' names and information about participation in various programs. Each line lists name, address, program title and amount. The next line may be the same name, address with a different program and amount. Each person may have 4-5 entries. I want to make single entries for each individual, with colums showing the programs and amounts.
    Current:
    ID Name Address Program Amount
    1 Bob home A 25
    1 Bob home B 37
    2 Dave home A 22
    2 Dave home B 10
    2 Dave home C 21

    Need:
    ID Name Address Program A Program B Program C
    1 Bob home 25 37 0
    2 Dave home 22 10 21

    Hopefully this makes it a bit more clear. The only way I know how to accomplish this is the "old fashioned" long hand approach of cut & paste. There are several thousand individuals, with (currently) tens of thousands of rows.

    Can anyone help? Thank you, in advance, for your support!!!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th July 2006
    Location
    San Diego
    Posts
    215

    Re: Consolidate multiple rows of data into single row

    Try this:

    First, in your "current" table, add a 6th column. In this column, put "= A2 & D2"

    Now for your "need" table:

    For column1, just write in 1,2,3,4,5,etc.

    For column2 and 3, use vlookup, using column 1's ID you just inputted for lookup value, and the "current" table for your table array.

    For columns 4,5,6, use vlookup again, using column 1's ID for look up value, and the "current" table for your table array again (use the new column you created"

    If this is confusing, post a sample of your excel file, and I'll show you what I mean.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th July 2006
    Posts
    5

    Re: Consolidate multiple rows of data into single row

    I followed that somewhat, but here's a sample of the data so you can let me see what you're doing.

    You can see the multiple rows for one individual which need to be pulled together into a single row. The columns will become the program name, with the value entered in that column rather than separate columns for program and amount. It will be much easier to manipulate the data once consolidated.

    The assistance is much appreciated!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Consolidate multiple rows of data into single row

    Are you sure the data will be easier to manipulate in that form?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th July 2006
    Posts
    5

    Re: Consolidate multiple rows of data into single row

    It will be easier to have a single entry for each individual so program totals can be obtained for each individual, etc. Better to have a few thousand records than tens of thousands. Plus, the original multi-row-per-person version is still available if needed.

    Did you have some other suggestion(s)? I'm open to listening/trying just about anything. It's always good to learn new ideas and skills.

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Consolidate multiple rows of data into single row

    Why not take a look at Data>Pivot table...?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    28th July 2006
    Posts
    5

    Re: Consolidate multiple rows of data into single row

    I can see if that will work for what they want. I know it will show data, but you can't manipulate a pivot table like you can raw data... and I'm not sure what all the program folks are going to want/need to do with it. All they requested was consolidation.

    I'll run it by them. Thanks again!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Consolidate multiple rows of data into single row

    What do you mean you can't manipulate a pivot table?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th July 2006
    Location
    San Diego
    Posts
    215

    Re: Consolidate multiple rows of data into single row

    Something like this is what I was thinking of....
    I thought your ID would be easy, and go chronologically, 1,2,3,etc.
    I guess this isn't the case... So, I ended up cutting and pasting. The rest is using VBA. This is the best I can do without VBA - if you want a macro on the otherhand, I could fully automate things.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,716

    Re: Consolidate multiple rows of data into single row

    I know it will show data, but you can't manipulate a pivot table like you can raw data
    Yes you can, you "Pivot" them, that why they are called Pivot Tables. Raw data still exists so you have the best of both Worlds.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: November 9th, 2008, 07:27
  2. Copy Multiple Rows Data To Single Column
    By jasont2x in forum EXCEL HELP
    Replies: 3
    Last Post: October 30th, 2008, 03:51
  3. Consolidate X Rows To Single Row
    By gr8sailor in forum EXCEL HELP
    Replies: 3
    Last Post: March 12th, 2008, 10:47
  4. Data From Multiple Rows Into Single Column
    By buccscott in forum EXCEL HELP
    Replies: 24
    Last Post: October 20th, 2007, 12:12
  5. Organize Data From Multiple Rows Into Single Row
    By hapyman in forum EXCEL HELP
    Replies: 4
    Last Post: June 13th, 2007, 02:46

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
  •  
porno