Turning Multiple Columns into a Single Column

[Bill Bisco]

I am really trying hard to follow CPearson's Example at: Table To Column

The relevant bit is:

Table To Column

First, we'll look at the formulas to convert the table to a single column. The data table has been assigned the defined name DataTable and the resulting column of data has been assigned the name ColumnData. (It is sufficient to name only the first cell, not the entire range.) Enter the following formula in the first cell of ColumnData and fill down for as many rows as there are elements (rows x columns) in DataTable:

=OFFSET(DataTable,MOD(ROW()-ROW(ColumnData),ROWS(DataTable)),TRUNC((ROW()-ROW(ColumnData))/ROWS(DataTable),0),1,1)

Can someone create the example for me and post a full formula without named ranges? I have been having a very difficult time following these instructions.

 

[Roderick_E]

If you are using Excel 2016, it has a new function; concat.

You could use =concat(a1," ",b1," ",c1) for example

 

[lilloscar]

But if we need to join different columns into single one, for example, resume only in Column A:A data from B:B, D:D, F:F, etc. and keep the order by the rows (1, 2, 3, 4, etc.), what formula can be used?
I found this one but the problem is that this didn't repeat the same data, I need it to accept repeated values.

This formula will keep in the column S2:S the resume of the column data for 9 different columns: $X$2:$X$99, $AC$2:$AC$99, $AH$2:$AH$99, $AM$2:$AM$99, etc.

=IF(LEN(S1),IFERROR(INDEX($X$2:$X$99,MATCH(0, IF(LEN($X$2:$X$99),COUNTIF(S$1:S1,$X$2:$X$99),1),0)),IFERROR(INDEX($AC$2:$AC$99,MATCH(0, IF(LEN($AC$2:$AC$99),COUNTIF(S$1:S1,$AC$2:$AC$99),1),0)),IFERROR(INDEX($AH$2:$AH$99,MATCH(0, IF(LEN($AH$2:$AH$99),COUNTIF(S$1:S1,$AH$2:$AH$99),1),0)),IFERROR(INDEX($AM$2:$AM$99,MATCH(0, IF(LEN($AM$2:$AM$99),COUNTIF(S$1:S1,$AM$2:$AM$99),1),0)),IFERROR(INDEX($AR$2:$AR$99,MATCH(0, IF(LEN($AR$2:$AR$99),COUNTIF(S$1:S1,$AR$2:$AR$99),1),0)),IFERROR(INDEX($AW$2:$AW$99,MATCH(0, IF(LEN($AW$2:$AW$99),COUNTIF(S$1:S1,$AW$2:$AW$99),1),0)),IFERROR(INDEX($BB$2:$BB$99,MATCH(0, IF(LEN($BB$2:$BB$99),COUNTIF(S$1:S1,$BB$2:$BB$99),1),0)),IFERROR(INDEX($BG$2:$BG$99,MATCH(0, IF(LEN($BG$2:$BG$99),COUNTIF(S$1:S1,$BG$2:$BG$99),1),0)),IFERROR(INDEX($BL$2:$BL$99,MATCH(0, IF(LEN($BL$2:$BL$99),COUNTIF(S$1:S1,$BL$2:$BL$99),1),0)),""))))))))),"")

So if you can help me to modify this just to accept repeated data please...

 

[jeffreybrown]

Hello lillosca & Welcome to the Forum,

Since this thread is a couple of years old, it's probably best if you post a new thread with your specific question.