Joining columns of different lengths from different sheets

[David Obeid]

Hi,

I'm using excel 2007.

If I have 6 worksheets, and 5 of them have data, arranged in columns of varying length, how can I get the columns to run as one continuous column (ie with no blanks) on the first sheet?

A non-VBA answer please.

[outofthehat]

Here's code for only two sheets of data:

=IF(ROW()<=COUNTA(Sheet1!A:A),Sheet1!A1,INDIRECT("Sheet2!A"&TEXT(ROW()-COUNTA(Sheet1!A:A),"0")))

Roughly translates as:

1. If the row you're in is <= the number of rows on sheet 1 col A, then take the data from sheet 1
2. Otherwise, take it from sheet 2 column A, row number defined by the row you're in less the number of rows on sheet A (using INDIRECT)).

You can extend the logic to work out how many rows are populated from sheets 1 and 2, and start taking data from the top of sheet 3, etc.

Will be interested to see if the gurus have a better, non-VBA method...

[David Obeid]

Here's code for only two sheets of data:

=IF(ROW()<=COUNTA(Sheet1!A:A),Sheet1!A1,INDIRECT("Sheet2!A"&TEXT(ROW()-COUNTA(Sheet1!A:A),"0")))

Roughly translates as:

1. If the row you're in is <= the number of rows on sheet 1 col A, then take the data from sheet 1
2. Otherwise, take it from sheet 2 column A, row number defined by the row you're in less the number of rows on sheet A (using INDIRECT)).

You can extend the logic to work out how many rows are populated from sheets 1 and 2, and start taking data from the top of sheet 3, etc.

Will be interested to see if the gurus have a better, non-VBA method...

How do I do this if the names of different sheets will vary (from time to time different users will name the sheets using their initials).

[JBeaucaire]

I would create a Named Range listing all the sheet names to collect from.

I would add an INDEX column to each sheet to uniquely "number" each row of data that I want to show.

Then an array formula can search all the search for each unique index number and grab the matching data.

=VLOOKUP(A3, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!A1:A50"), A3), 0)) & "'!A:B"), 2, 0)

So, this is doable with non-VBA, but it comes at a price. The example sheet is only searching the first 50 rows of each of the 6 sheets... but with only 6 cells of data on each row found and only a total of 50 rows to grab total, you can already feel the "slow down" any time you edit any other cell.

Try it, open this sheet and type anything into an empty cell on the summary or change of of the bits of data on the other sheets and you'll see the "pause" as all the calcs run again...2500 calcs per cell and 300 cells of calcs...that's 75,000 calc each time you change anything. Oh my!

[outofthehat]

I'd be interested in the Guru's view on the attached approach, and whether it costs as much in system slowdown. I've developed the initial approach, but using a VLOOKUP instead of nested IF's. The vlookup table (named controldata on 'Control' sheet) is created from entering sheet names manually - COUNT formulae determine how many rows have been used by other sheets. The 'Output' sheet uses VLOOKUPs inside an INDIRECT function to adjust which sheets and rows it's taking the data from.

From a maintenance point of view, all you have to do is type in the sheet names, and make sure the formula is copied down far enough (you could add checksums on the control sheet to make sure it added up). Change the order of the sheet names, and it changes the order the data appears in the output.

The thing to watch out for is header rows, both blank and non-blank. These could affect the COUNT functions, and will definitely affect how you calculate which row to reference.

@JBeaucaire - how does that compare with your approach in system resource usage? This is an area I'm not very familiar with...

[outofthehat]

Of course, for multiple columns you'd have to change the !A to !B, !C etc in the formula on the Output sheet... or do something copyable with COLUMN()

[JBeaucaire]

Use of helper columns to reduce calculation weight is nicely done. I expanded your technique to 6 columns with no discernable "lag" on my system.

[WHER]

Based on outofthehat's attachment, a (slightly) different approach. No idea/claims about efficiency either.

[outofthehat]

You've quoted my first attempt, which is very hard to maintain. Using the method in Post 4, i.e. the control sheet and VLOOKUPs, all you have to do is change the names in the list of sheets (on the Control sheet), and it'll work - provided you've typed them right! Very easy to maintain.