SOLVED

Combine data from multiple rows into a single row

%3CLINGO-SUB%20id%3D%22lingo-sub-198553%22%20slang%3D%22en-US%22%3ECombine%20data%20from%20multiple%20rows%20into%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198553%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20apologies%20for%20the%20long%20post...%20I've%20been%20wrestling%20with%20this%20for%20awhile%20and%20am%20at%20wit's%20end...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20of%20data%20that%20contains%20the%20results%20of%20chemical%20analyses%20of%20water%20samples.%20There%20is%20a%20column%20with%20a%20single%20unique%20ID%20for%20each%20sample%2C%20and%20over%2090%20columns%20with%20the%20various%26nbsp%3Bchemical%20parameters%20(i.e.%2C%20variables)%26nbsp%3Band%20the%20associated%20data.%20The%20problem%20is%20that%20the%20data%20are%20scattered%20across%20multiple%20rows%20for%20each%20sample%2Fanalysis.%20Here's%20an%20example%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20have%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3ESampleID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ERow%23%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar1%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar2%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar3%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar4%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar5%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar6%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar7%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar8%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar9%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar10%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4660905_1%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E-14.8%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2574%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4660905_1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E6.73%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E30.1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26lt%3B2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E3291%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4660905_1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E123%3C%2FTD%3E%3CTD%3E122%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4660905_1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E123%3C%2FTD%3E%3CTD%3E122%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4661103_2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2235%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4661103_2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E6.94%3C%2FTD%3E%3CTD%3E-214%3C%2FTD%3E%3CTD%3E27.6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E114%3C%2FTD%3E%3CTD%3E%26lt%3B1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E3213%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4661103_2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E114%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20consolidate%20the%20samples%20into%20this%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3ESampleID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ERow%23%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar1%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar2%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar3%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar4%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar5%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar6%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar7%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar8%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar9%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EVar10%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4660905_1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E6.73%3C%2FTD%3E%3CTD%3E-14.8%3C%2FTD%3E%3CTD%3E30.1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E123%3C%2FTD%3E%3CTD%3E122%3C%2FTD%3E%3CTD%3E%26lt%3B2%3C%2FTD%3E%3CTD%3E2574%3C%2FTD%3E%3CTD%3E3291%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4661103_1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E6.94%3C%2FTD%3E%3CTD%3E-214%3C%2FTD%3E%3CTD%3E27.6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E110%3C%2FTD%3E%3CTD%3E114%3C%2FTD%3E%3CTD%3E%26lt%3B1%3C%2FTD%3E%3CTD%3E2235%3C%2FTD%3E%3CTD%3E3213%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20the%20complicating%20issues%3A%3C%2FP%3E%3CUL%3E%3CLI%3EThere%20is%20no%20rhyme%20or%20reason%20to%20the%20distribution%20of%20data%20-%20it's%20not%20like%2C%20for%20example%2C%20row%201%20always%20has%20Var2%2C%203%2C%204%2C%207%2C%3B%20row%202%20always%20has%20Var1%2C%205%2C%206%2C%20etc.%3B%20so%20I%20can't%20assume%20that%20each%20sample%20will%20have%20a%20consistent%20distribution%20of%20data%3C%2FLI%3E%3CLI%3ESome%20of%20the%20variables%20have%20the%20same%20data%20in%20multiple%20rows%20(e.g.%2C%20Var6%20and%20Var7%20in%20the%20example%20above)%3C%2FLI%3E%3CLI%3ESome%20of%20the%20values%20are%20not%20considered%20numbers%20by%20Excel%20(see%20Var8%20above)%3C%2FLI%3E%3CLI%3EThe%20overall%20data%20table%20has%20thousands%20of%20samples%3B%20hundreds%20of%20them%20have%20multiple%20rows%20and%20the%20number%20of%20multiple%20rows%20ranges%20from%202-5%20rows%20per%20sample.%20I%20can%20do%20this%20manually%2C%20but%20it%20will%20take%20at%20least%20an%20entire%20day%20to%20get%20through%20everything.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EDoes%20anyone%20know%20of%20a%20simple%20way%20to%20do%20this%3F%20Pivot%20tables%20won't%20work%20because%20of%20the%20non-numeric%20entries.%20I've%20looked%20into%20the%20Power%20Query%20tool%2C%20but%20can't%20figure%20out%20how%20to%20do%20this%20with%20so%20many%20variable%20columns.%20Any%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Matt%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-198553%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EGet%20%26amp%3B%20Transform%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198606%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20data%20from%20multiple%20rows%20into%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198606%22%20slang%3D%22en-US%22%3E%3CP%3EMatt%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESure%2C%20will%20be%20glad%20to%20help.%20The%20only%20do%20not%20promise%20immediate%20answers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198601%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20data%20from%20multiple%20rows%20into%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198601%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%20for%20your%20response.%20I%20think%20I%20understand%20what%20you%20did%20there%2C%20but%20I'm%20not%20sure%20if%20I%20can%20re-created%20it%20with%20my%20main%20spreadsheet%20of%20data.%20I'll%20give%20it%20a%20try%2C%20and%20I%20may%20ask%20for%20more%20guidance%20if%20you're%20willing%20to%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Matt%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-198585%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20data%20from%20multiple%20rows%20into%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-198585%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Matt%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAttached%20is%20the%20sample%20with%20Power%20Query.%20That's%20done%20not%20optimal%20way%20to%20avoid%20coding%20and%20do%20all%20from%20UI%2C%20just%20to%20demonstrate%20the%20approach.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20brief%2C%20we%20unpivot%20other%20than%20SampleID%20columns%2C%20remove%20duplicates%20(as%20Var6%2C%20Var7)%2C%20in%20second%20query%20Columns%20generate%20list%20of%20columns%20to%20be%20sure%20empty%20ones%20will%20be%20included%20(Var4%2C%20Var5)%2C%20append%20that%20query%20to%20first%20one%20and%20pivot%20on%20attribute%20finally.%20Not%20sure%20if%20you%20need%20empty%20Row%23%20column%20in%20final%20result%2C%20could%20be%20added.%3C%2FP%3E%0A%3CP%3EPlease%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

My apologies for the long post... I've been wrestling with this for awhile and am at wit's end...

 

I have a table of data that contains the results of chemical analyses of water samples. There is a column with a single unique ID for each sample, and over 90 columns with the various chemical parameters (i.e., variables) and the associated data. The problem is that the data are scattered across multiple rows for each sample/analysis. Here's an example: 

 

This is what I have:

SampleIDRow#Var1Var2Var3Var4Var5Var6Var7Var8Var9Var10
4660905_10 -14.8      2574 
4660905_116.73 30.1    <2 3291
4660905_12     123122   
4660905_13     123122   
            
4661103_20     110  2235 
4661103_216.94-21427.6   114<1 3213
4661103_22      114   

 

I need to consolidate the samples into this:

SampleIDRow# Var1Var2Var3Var4Var5Var6Var7Var8Var9Var10
4660905_1 6.73-14.830.1  123122<225743291
4661103_1 6.94-21427.6  110114<122353213

 

Here are the complicating issues:

  • There is no rhyme or reason to the distribution of data - it's not like, for example, row 1 always has Var2, 3, 4, 7,; row 2 always has Var1, 5, 6, etc.; so I can't assume that each sample will have a consistent distribution of data
  • Some of the variables have the same data in multiple rows (e.g., Var6 and Var7 in the example above)
  • Some of the values are not considered numbers by Excel (see Var8 above)
  • The overall data table has thousands of samples; hundreds of them have multiple rows and the number of multiple rows ranges from 2-5 rows per sample. I can do this manually, but it will take at least an entire day to get through everything.

Does anyone know of a simple way to do this? Pivot tables won't work because of the non-numeric entries. I've looked into the Power Query tool, but can't figure out how to do this with so many variable columns. Any help would be greatly appreciated.

 

-Matt

3 Replies
Highlighted
Solution

Hi Matt,

 

Attached is the sample with Power Query. That's done not optimal way to avoid coding and do all from UI, just to demonstrate the approach.

 

In brief, we unpivot other than SampleID columns, remove duplicates (as Var6, Var7), in second query Columns generate list of columns to be sure empty ones will be included (Var4, Var5), append that query to first one and pivot on attribute finally. Not sure if you need empty Row# column in final result, could be added.

Please see attached.

Highlighted

Sergei,

 

Thanks so much for your response. I think I understand what you did there, but I'm not sure if I can re-created it with my main spreadsheet of data. I'll give it a try, and I may ask for more guidance if you're willing to help. 

- Matt

Highlighted

Matt,

 

Sure, will be glad to help. The only do not promise immediate answers.