4

I have a dataset that looks something like this:

Customer Col1 Col2 Col3
Bob 111 2221
Michael 112
Anna 113 2223
Rachel 114 2224 334
Person5 115

I'd like to put all data into one column, something like this:

Customer Col1
Bob 111
Bob 2221
Michael 112
Anna 113
Anna 2223
Rachel 114
Rachel 2224
Rachel 334
Person5 115

I tried transposing the original array and then merging the data from other columns into one column, but couldn't get it to work. We also tried pivot tables without success. I thought that an Index/Match could work, but it didn't work for merging data into one single column. How should this be done?

We're using Excel 2010 and the same data is in Google Sheets as well.

1 Answer 1

3

Before providing the required solution, I would like to highlight a few important points based on the tags, particularly regarding the use of Excel 2007. It is important to confirm if you are indeed using Excel 2007, as support for Office 2007 ended on October 10, 2017. Continuing to use this version one can expose to serious and potentially harmful security risks.

While this information is readily available online, it is important to reiterate these risks for your awareness and safety. For more details, please refer to the official documentation and resources provided by Microsoft. MSFT Documentations.


enter image description here


Using Excel Formulas that which works with Excel 2010+ (not considering Excel 2007)

• Formula used in cell F8

=INDEX($A$2:$A$6,AGGREGATE(15,7,(ROW($B$2:$D$6)-ROW($B$2)+1)/($B$2:$D$6<>""),ROWS(F$7:F7)))

• Formula used in cell G8

=INDEX($B$2:$D$6,
   MATCH(F8,$A$2:$A$6,0),
   AGGREGATE(15,7,(COLUMN($B$2:$D$6)-COLUMN($B$2)+1)/
  ((F8=$A$2:$A$6)*($B$2:$D$6<>"")),COUNTIF(F$8:F8,F8)))

NOTE: Both the above formulas needs to be filled down!


Using the Excel For Web options to use Modern functions, if one do not have access to the paid subscription version of MS365 desktop app:

=HSTACK(
        TOCOL(IFS(B2:D6<>"",A2:A6),2),
        TOCOL(B2:D6,1))

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

enter image description here


To use Power Query follow the steps:

  • First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1

  • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

  • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

  • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .