Announcement

Collapse
No announcement yet.

Consolidate multiple rows of data into single row

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        Re: Consolidate multiple rows of data into single row

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

        Comment


        • #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!

          Comment


          • #6
            Re: Consolidate multiple rows of data into single row

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

            Comment


            • #7
              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!

              Comment


              • #8
                Re: Consolidate multiple rows of data into single row

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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11


                      Re: Consolidate multiple rows of data into single row

                      I'm not exactly sure what I was thinking about when I said you can't manipulate a pivot table. Something they don't do, I'm sure. Anyway, after some consultation, I think it will fit what they need.

                      So, problem solved! Thanks to everyone for your assitance. Mucho apreciado!

                      Comment

                      Working...
                      X