Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Data on multiple rows, need to have them on the same row.

Answered Data on multiple rows, need to have them on the same row.

  • Wednesday, July 07, 2010 2:22 AM
     
     
    Sign In to Vote
    0

    Good evening all, been a long term lurker have learned lots on these forums and I thank everyone for their input.

    I have a excel question, I'm working with a large amount of data that looks like the sample below

    435334086437 34
    435334086437 43
    435334086437 2342
    435334086437 3223
    435334086437 94543
    435334086439 343
    435334086439 33434
    435334086439 4343534
    435334086450 3453453
    435334086450 32123
    435334086450 6564
    435334086450 8786
    435334086450 454444

    I need to make the output look like this

    435334086437 34 43 2342 3223 94543
    435334086439 343 33434 4343534
    435334086450 3453453 32123 6564 8786 454444

    Where all the data is on a single row, but on a seperate column, most preferably on the same column that they were originally at.  Just moved up to correspond with the first column which is a unique number.

    I have tens of thousands of lines of this sort of data, that I need to get on the same row, otherwise I'm back to the manual cut and paste of the data up to the first row.

    Any help would be greatly appreciated!

    Thanks,

     

    Gary

     

     

     

     

     

All Replies

  • Wednesday, July 07, 2010 6:30 AM
     
     
    Sign In to Vote
    0

    Hi Gary,

    Here is the solution for you!

    Step 1:  Get the Unique Records

    - Select the entire column that has duplicate records (435334086437,435334086437 in ur example) , Go to Data> Filter>Advanced Filter . Select Copy to Another Location check box. Select the Destination .( Copy To)  Select Unique Records Only Check box.This is the place where you get Unique Records. Please choose the same sheet. ( first record may come twice, just remove that)

    Step2: Putting Formula

    After step 1,  Your  New Column ( let say Col6 ) will contain unique records ( 3 here).

    Now Use the below formula:  in Col7

    =SUMIF(B:B,I8,C:C)  where Col B is the column that contains duplicate records. I8 is the column the column that has unique record ( Col6 here) and 8 th row contains the first unique number. and C:C will just add the blank spaces and the values.

    Now your data will be like this.

    Before :

    435334086437 34        

    435334086437

    		   43

    After:

    435334086437 34  43      
     

    Now Use the above formula in Col8 to get 43 displayed. Step2 is to put the formula in Col8. You can repeat the same to put formula in Col9. etc. Only thing you have to change is C:C is 1st col after the duplicate columns.

    For Col8 the formula would be :

    =SUMIF(B:B,I8,D:D) where D:D is the second column from the duplicate columns.

    Now by this we can get the formula for first Unique Record.

    Just drag the same formual to apply for all your 1000 unique records !

    All the best.

    Let me know if you have any issues.

    I can email the excel attachment that have the above formula with dummy data.

    Thanks,Venki

     

     
  • Thursday, July 08, 2010 1:51 PM
     
     Answered
    Sign In to Vote
    0

    Good day,

    If all your data looks like this where there is one data point on each row for each unique number in the first column you could use a Pivot Table to just create the expected look. However, if you have 2 column 2 entries for the same value in column one the result in this method will not be what you want unless you need a sum of the two entries.  I am basing this on an assumption that each column has a header that can be used.

    Start by creating a Pivot Table on a new sheet that references the data range you are wanting to use. You can make this a little more dynamic by using a Named Range that you expand each time you make an entry.

    Once you have a Pivot Table add Column 1 to the Row Headers then add each of the other columns to the Values section.  The result will look something like the below grid.

     

      Values        
    Row Labels Count of Column 2 Count of Column 3 Count of Column 4 Count of Column 5 Count of Column 6
    435334086437 1 1 1 1 1
    435334086439 1 1 1    
    435334086450 1 1 1 1 1
    Grand Total 3 3 3 2 2

    Select the Column header and either choose the "Field Settings" button on the Pivot Table Tools > Options ribbon or right click and select the "Value Field Settings" option.  Change the "Summarize value field by" to Sum and if you like you can change the column name display value in the "Custom Name" box.

      Values        
    Row Labels Sum of Column 2 Sum of Column 3 Sum of Column 4 Sum of Column 5 Sum of Column 6
    435334086437 34 43 2342 3223 94,543
    435334086439 343 33434 4343534    
    435334086450 3453453 32123 6564 8786 454,444
    Grand Total 3453830 65600 4352440 12009 548,987

    You can also use the Number format option to change the look of the numbers to have thousand seperators, make them currency, or even have them show red if they are negative values.  Just as you can within the Excel Cell.

    Remove the Grand Totals (unless you want them) through the Pivot Table Options. and remove the Field Headers as well and you will end up with a list that shows the grouped data as you desire.

      Sum of Column 2 Sum of Column 3 Sum of Column 4 Sum of Column 5 Sum of Column 6
    435334086437 34 43 2342 3223 94,543
    435334086439 343 33434 4343534    
    435334086450 3453453 32123 6564 8786 454,444

    You can always hide the Row headers by hiding the row they are on.

    Just remember to make this more flexible you name the data range with a blank row at the end and then insert a row each time you want to add a new value.  Then when you refresh the Pivot Table the information will be updated.   Additionally if you have two values in the second column that match to the first column value in this example they will be added together and will no longer be seperate records.

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Regards,

    Chris Nicholas

    Microsoft Online Community Support