Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

I have a column of numbers which refer to letters in the adjacent column. There are three similar column pairs, of varying length. How do I make one continuous column pair from all the data? From time to time the lengths of the columns will vary, depending on what is being put into the columns, of course. And I don't want any gaps in the resulting column-pair!

See attached 'sample'.

In fact the columns are on different worksheets, but I don't suppose that matters. All I want to do is put all the data into one continuous column-pair (i.e. the data from the three different column pairs will all string on one below the other). I want the results to go in the yellow shaded boxes. (See attached).

Thanks.

Sample-151130.xlsx

See attached 'sample'.

In fact the columns are on different worksheets, but I don't suppose that matters. All I want to do is put all the data into one continuous column-pair (i.e. the data from the three different column pairs will all string on one below the other). I want the results to go in the yellow shaded boxes. (See attached).

Thanks.

Sample-151130.xlsx

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

pls try

```
Sub macro()
For Each sh In Array("Sheet1", "Sheet2")
If Sheets("Results").Range("M3") = "" Then
Set rngDest = Sheets("Results").Range("M3")
Else
Set rngDest = Sheets("Results").Range("M3").End(xlDown).Offset(1)
End If
Sheets(sh).Range("C3:D" & Range("C3").End(xlDown).Row).Copy Destination:=rngDest
Next
End Sub
```

Regards
Example attached, please click on macro button to see the desired output.

Best,

Sample-151130.xlsb

please find attached file with formula solution.

i used Excel tables references because of its dynamic range benefit.

Sample-151130.xlsx

look into the attached modified formula. i added one more table making it total 4 tables and one union table

so, i created a new table. first i renamed my union table to table5 and then i created the new table as table4

then i added one additional IFERROR with adding the Index of (Table 4 but this time, see the pattern for previous ones, i added ROW([@Column1])-ROW(Table5

try to compare the previous file and this one. you will learn how to add new table.

please let me know, if you have any question.

Sample-151130.xlsx

i saw your workbook. i tried to understand it. but i could not find any sheet called "csv file"

so, all the yellow tables need to be combined in one table without their headers but this one table where is it located and what is the sheet name or range location for example

i need to know the sheet name and the range where the combined table need to be put for example from column A to Column D.

please see attached.

i created a worksheet named it "MAIN" and then all of the tables are combined with formula on this table. it is dynamic if your data tables on other sheets grow it will automatically populate in this Main sheet table. please note that the empty gaps you have in the main table is because some of your tables were empty and the first row after header had no data.

the formula was long and somehow complicated ;-) but i figured it out. here is the formula

```
=IFERROR(IFERROR(INDEX(Table1, ROW([@Employee])-ROW(Table24[#Headers]),COLUMN(A:A)),IFERROR(INDEX(Table2, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1),COLUMN(A:A)),IFERROR(INDEX(Table3, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2),COLUMN(A:A)),IFERROR(INDEX(Table4, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3),COLUMN(A:A)),IFERROR(INDEX(Table5, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4),COLUMN(A:A)),IFERROR(INDEX(Table6, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5),COLUMN(A:A)),IFERROR(INDEX(Table7, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6),COLUMN(A:A)),IFERROR(INDEX(Table8, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table7),COLUMN(A:A)),IFERROR(INDEX(Table9, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8),COLUMN(A:A)),IFERROR(INDEX(Table10, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9),COLUMN(A:A)),IFERROR(INDEX(Table11, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10),COLUMN(A:A)),IFERROR(INDEX(Table12, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11),COLUMN(A:A)),IFERROR(INDEX(Table13, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12),COLUMN(A:A)),IFERROR(INDEX(Table14, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13),COLUMN(A:A)),IFERROR(INDEX(Table15, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14),COLUMN(A:A)),IFERROR(INDEX(Table16, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15),COLUMN(A:A)),IFERROR(INDEX(Table17, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16),COLUMN(A:A)),IFERROR(INDEX(Table18, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17),COLUMN(A:A)),IFERROR(INDEX(Table19, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18),COLUMN(A:A)),IFERROR(INDEX(Table20, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19),COLUMN(A:A)),IFERROR(INDEX(Table21, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19)-ROWS(Table20),COLUMN(A:A)),IFERROR(INDEX(Table22, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19)-ROWS(Table20)-ROWS(Table21),COLUMN(A:A)),INDEX(Table23, ROW([@Employee])-ROW(Table24[#Headers])-ROWS(Table1)-ROWS(Table2)-ROWS(Table3)-ROWS(Table4)-ROWS(Table5)-ROWS(Table6)-ROWS(Table8)-ROWS(Table9)-ROWS(Table10)-ROWS(Table11)-ROWS(Table12)-ROWS(Table13)-ROWS(Table14)-ROWS(Table15)-ROWS(Table16)-ROWS(Table17)-ROWS(Table18)-ROWS(Table19)-ROWS(Table20)-ROWS(Table21)-ROWS(Table22),COLUMN(A:A)))))))))))))))))))))))),"")
```

EE.xlsm
the problem is caused with your formulas in the yellow tables.

it has nothing to do with the MAIN sheet table formula.

for example , please open the attached file. i added more data in the Jerrum' worksheet and i put Professor in all cells and all of them appears in the Main Sheet table automatically.

you need to get your yellow sheets formulas fixed. becuase the MAIN sheet table formula is robust and i tested it and have no problem at all.

EE.xlsm

Phew! it was a complicated work. this was the longest formula, i ever written.

well, i had to work again in your original file. now i understood that your yellow cell tables have to have their empty cells.

i reworked the formula with some other tricks and now it is working. i did not touch your other 23 yellow tables they are all intact. please find attached workbook. when any new data pops into the yellow tables. it will automatically pop into the Main Sheet Table24

plz let me know how it goes. hopefully this is the final wrap up

EE.xlsb

you are most welcome. i always like to answer challenging questions and go beyond the expectation.

anyone could answer simple questions like vlookup ;-)

thanks for spotting the 'Lee Brown' glitch. i fixed the formula it was missing addtional parathesis. so it is fixed in the attached version.

the slowness is of calculation is due to the formula being in hundreds of rows, so i am affraid you have to live with it :-)

here is the final formula that resides in cells B3 to E4696

=IFERROR(IFERROR(INDEX(Tab

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial