SOLVED

Combining rows of Data into Single ROW

%3CLINGO-SUB%20id%3D%22lingo-sub-2486740%22%20slang%3D%22en-US%22%3ECombining%20rows%20of%20Data%20into%20Single%20ROW%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2486740%22%20slang%3D%22en-US%22%3E%3CP%3EVersion%3A%20EXCEL%20365%20for%20MS%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20have%20to%20explain%20the%20issue%20without%20posting%20the%20actual%20excel%2C%20as%20it%20is%20corporate%20information.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%201000's%20of%20rows%20of%2015%20columns.%26nbsp%3B%20The%20first%2012%20columns%20identify%20the%20individual%20and%20the%20last%203%20columns%20identify%20certain%20confidential%20information.%26nbsp%3B%20Individuals%20may%20appear%20up%20to%2010%20times%20in%20the%20dataset%20and%20the%20information%20is%20always%20the%20same%20for%20that%20individual%20in%20the%20first%2012%20columns%3B%20however%2C%20the%20last%203%20columns%20will%20be%20different%20each%20time.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20create%201%20row%20for%20each%20individual%20that%20contains%20the%20data%20from%20columns%201-12%20and%20then%20add%20the%20information%20from%20the%20last%20three%20columns%20to%20that%20row%20each%20time%20the%20individual%20appears.%26nbsp%3B%20The%20data%20cannot%20be%20combined%20into%20a%20single%20cell.%26nbsp%3B%20Therefore%20a%20person%20appearing%201x%20in%20the%20list%20will%20have%2015%20columns%20of%20information%2C%20a%20person%20appearing%202x%20will%20have%2018%2C%203x%20will%20have%2021%2C%20etc%20etc.%26nbsp%3B%20I%20can%20sort%20or%20manipulate%20the%20data%20by%20any%20column%20to%20facilitate...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2486740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2486949%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20rows%20of%20Data%20into%20Single%20ROW%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2486949%22%20slang%3D%22en-US%22%3EI%20am%20not%20familiar%20with%20Power%20Query.%20I%20see%20that%20it%20is%20an%20add-on%20for%20excel.%20Once%20I%20download%2C%20how%20would%20I%20set%20it%20up%20to%20make%20the%20manipulations%20I%20need%3F%3C%2FLINGO-BODY%3E
New Contributor

Version: EXCEL 365 for MS 

 

I will have to explain the issue without posting the actual excel, as it is corporate information.  

 

I have 1000's of rows of 15 columns.  The first 12 columns identify the individual and the last 3 columns identify certain confidential information.  Individuals may appear up to 10 times in the dataset and the information is always the same for that individual in the first 12 columns; however, the last 3 columns will be different each time.  

 

I need to create 1 row for each individual that contains the data from columns 1-12 and then add the information from the last three columns to that row each time the individual appears.  The data cannot be combined into a single cell.  Therefore a person appearing 1x in the list will have 15 columns of information, a person appearing 2x will have 18, 3x will have 21, etc etc.  I can sort or manipulate the data by any column to facilitate...

 

 

 

 

 

6 Replies

@Lumberjack92 

That's job for Power Query if you consider such option. On simple model it looks like

image.png

I am not familiar with Power Query. I see that it is an add-on for excel. Once I download, how would I set it up to make the manipulations I need?

@Lumberjack92 

Staring from Excel 2016 it build in, nothing to install. On Excel 365 that everything what is in these sections of ribbon

image.png

I apologize for my ignorance - I have found those items in my excel. I also looked at the example you supplied. The example seems to work, but it does not show the method or actions you performed?
best response confirmed by allyreckerman (Microsoft)
Solution

@Lumberjack92 

You may click on 

image.png

and in the pane double click on query name to open Power Query editor

image.png

In the pane of it you may select on steps one by one to check which transformations they perform

image.png

If you see all this first time you could have a lot of question, but that's better discuss on the sample which is more close to actual data, at least have same number of colums.

Thank you - that gets me moving again!!