How to stack multiple columns into one column automatically with specific range

Question

Hello there,

I have worksheet which include specific data for specific purpose one of the thing that I need is to specify range of columns for example A1:C10 to be stacked into one column which is D with ignoring the spaces and not listed them and I need this process to be automatically whither it keeps running in the background so any change will reflect the result directly or at least on file open. I hope you got my idea.

         A               B              C              D

1    Value1      Value4      Value7       Value1    

2    Value2      Value5      Value8       Value2

3    Value3      Value6      Value9       Value3

                                                         Value4

                                                         Value..Etc.

Remember no spaces required.

Regards.

Answer 1

Hi AlaaEddin95

Welcome to this community

I'm afraid your requirements only could be achieved using a VBA code in the worksheets event on your workbook

Let's say your scenario is in Sheet1 of your workbook

So.

1- Select the Sheet1 tab\ Right click \ View code 

2-and Paste the code in the VBA panel

as shown in picture below

Here is the Code

***********************************************************************************

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Matrix, K As Range

Dim r As Long

Set Matrix = Range("A2:C20") ''' Your Matrix range

r = 2  ''The starting row in "The List"

If Not Application.Intersect(Target, Matrix) Is Nothing Then

'''' The line below will delete old list

Range(Cells(2, "E"), Cells(Rows.Count, "E").End(xlUp)).ClearContents

''' Loop thru the matrix and update "The List"

        For Each K In Matrix

                If K.Value <> "" Then

                Cells(r, "E").Value = K.Value

                r = r + 1

                End If

        Next K

End If

End Sub

*****************************************************************************************

Note: 

Here I choose the range "A2:C20" and column "E" to make space for headers.

Please, Change the ranges as per your needs.

Please find in the link below and download a file with my approach as a solution to your requirements

https://www.dropbox.com/s/aigsuyrgjuhfmha/Matrix-List.xlsm?dl=0

*******************************************************************************************************

Do let me know if you require any further help on this. Will be glad to help you.

If this answer would be the solution to your question, I'll appreciate you mark it as answered

Please Note:

By marking a Reply as a "Accept as Solution", if it solves your problem. Will give positive reinforcement, not only to the person who responded, but also will help other users with the same or a similar question by directing them to the response.Thanks for your co-operation.

Answer 2

Stacking didn't really ring the bell. But then I saw the answer. Many people call what you want to do "unpivot".

• Define your input data as an Excel "Table" (#1)
• Load your data into PowerQuery: Data tab > Get & Tranform / PowerQuery group > From table command (#2)
• In PowerQuery Select all of the columns
• (#1) Transform tab > Any Column group > (#2) Unpivot Command
• Delete the "Attribute" column
• Click drop down in column header (#3), select "remove blank"
• Click drop down in column header, select sort order
• Home tab > Close group > Close and Load drop down > Close and Load command

This saves your "stacked" data on a new tab, blanks removed and sorted.

Inside PowerQuery.  On right (#4) are the recorded steps listed

To make changes

Enter new data in the original "input" table

On the output tab, right click on the output table, select "refresh" command.

The recorded steps will be applied to the new data, and any other changes to existing data then is loaded to the output table. Everything nice and automatic.

Answer 3

Sorry, meant to include these articles showing some other ways of doing the same thing

Col- **Stack Columns of Data in Excel: Formula Method, Power Query Method** (unpivot)****2017 05 10

https://ozdusoleil.com/2017/05/10/how-to-stack-columns-of-data-in-excel-formula-method-power-query-method/

Lately, there have been a number of requests for stacking data. This video shows 2 methods for stacking 3 columns of data:

– The INDIRECT formula can be used once we know the pattern of how the data needs to be rearranged.

– In Get & Transform (Power Query) it’s simple: add an index column, then “unpivot other columns.” BOOM! DONE!

Col- **Stacking Columns of Data (Unpivot, Split Columns by delimiter, and everything!)**2017 04 16

We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns.  The small model revealed that the last step in our solution should be an UnPivot. Thus, an intermediate step need to be something that looks like it needs to be unpivoted. Therefore, the trick is in getting the data set up right in Excel BEFORE taking it into Power Query (Get and Transform)!!!

@ Reverse PivotTable June 20, 2012 by Mynda Treacy****https://www.myonlinetraininghub.com/excel-factor-entry-1-reverse-pivottable

There comes a time when you are presented with data in a cross-tabular format but your analysis requires that the data be formatted into a traditional table (or normalized) structure.

Use Pivot Table and Pivot Chart Wizard.

Get & Transform: An Alternative to Manually Flattening Data 2016 06 16

http://www.excel-university.com/get-transform-an-alternative-to-manually-flattening-data/****Since many of Excel’s features are designed to work with data stored in a flat, tabular format, we sometimes need to flatten data that is received in other formats. In a previous post, we discussed a manual way to flatten data. In this post, we’ll use a Get & Transform Query as an alternative to that manual process.

ET R 0 Ribbondocx

Answer 4

Thanks so much for the reply actually the first way is really complicated I would choose to use the VBA with micro enabled file and it's work like a char thanks for both of you, you guys are beasts.

Regards.

Answer 5

Thanks you so much, Well done brother.

Best Regard