Merging different columns

Question

I have multiple lists that are in separate columns in excel. What I need to do is combine these columns of data into one big column. I do not care if there are duplicate entries. Below, I have just shown 3 columns, but the actual data has 20 such columns. The columns are not continuous, and I want to avoid printing the blank spaces in the final output.

      A   B   C 
      _   _   _
ROW1| R   U   X       
ROW2| S   V   Y   
ROW3| T   W   Z

should combine into 1 column like this

      A
      _
Row1| R
Row2| S
Row3| T
Row4| U
...
Row12|Z

possible duplicate of [How to convert a matrix to a single column using Excel](http://stackoverflow.com/questions/28282473/how-to-convert-a-matrix-to-a-single-column-using-excel) — Tim Biegeleisen, Sep 01 '15 at 07:58
i have many empty cells in between, i just need to show cells which contains some value @TimBiegeleisen — Akshit Jain, Sep 01 '15 at 08:27

score 0 · Answer 1 · answered Sep 01 '15 at 15:09

I will assume for simplicity that your raw data is in sheet1, and you want the output in sheet2, starting at A1 and going down. This is ideally a VBA project, but you have asked for a formula version - this is possible, but the more dynamic your data is (changing columns, changing row lengths, etc.), the more complicated this will be.

First, on sheet2 you will need to identify which columns hold your data. So in column D starting at D1 and going down, manually type those columns in, like so:

="A:A"
="B:B"
="E:E"

Beside each of those columns, you will want a count of how many rows are in each. Do this using the COUNTA function, which counts all non-blank cells [I am assuming that your data in sheet1 is continous, with no empty rows]. We will also use the INDIRECT function to determine which column to look at. So put this in E1, and drag down:

=COUNTA(INDIRECT("'Sheet1'!"&B1))

Then we want a running total of how many rows we have so far, one column after the other. Put this in F2 and drag down[F1 hardcode as 0]:

=SUM($E$1:E1)+.01

In Column B, we will now show which column that row's data is being pulled from, like so [starting in B1 and copied down]:

=INDEX(D:D,MATCH(ROW(),F:F,1))

In Column C, we will now show which row the data will be pulled from [starting in C1 and copied down]:

=COUNTIF($B$1:B1,B1)

In A1 and dragged down for as many total cells as you have data in, put the following [starting in A1, and dragged down]:

=INDEX(INDIRECT("'Sheet1'!"&B1),C1)

This looks like a lot of set-up - because it is. A lot of data manipulation is required if you aren't using VBA for something like this.

Merging different columns

1 Answers1