Combining multiple sheets

Hi I have a multiple sheet excel file each sheet have rows of names that begin with same letter of alphabet e.g. sheet1 has rows of names that begin with A and sheet2 has rows of names beginning with B and so on. I'd like to automate combining all sheets together in one sheet in alphabetical order and output in a separate sheet, and also have that separate sheet updated whenever any names are added.

Is there a quick and efficient way of doing this rather than manually copying. I am hoping that there is something in the tools or a function that can do this all within excel. No figures to be updated whatever so its just 1 row 1 column on each sheet. There is a second column but is not required for the combined sheet

Thanks

First, I have to ask, is there any good reason why you need to have your data spread across 26 worksheets? Can't you consolidate it into one worksheet once and for all? It would make it much more maintainable!

If you must stay with your current structure then here is one possible way to automate the consolidation.

The following description is with reference to the attached workbook.

I have introduced a "helper" worksheet. Col-A is a list of your worksheet names. I have used "a", "b", "c", and so on. Change these names to reflect your real workbook.

Col-B simply counts the number of entries (names) on each worksheet. In B3 copied down:

Formula:

Please Login or Register  to view this content.

Col-C is a cumulative count of the names. In C3 copied down:

Formula:

Please Login or Register  to view this content.

Col-D is unused in the final version. It has been merged into the "Consolidated" worksheet formula. I have retained it here as it may help debug if extra header rows or other changes are made to the layout that may break the formulas. It has a row for every name and provides an index back to the source worksheet for each name. In D3 copied down:

Formula:

Please Login or Register  to view this content.

The "Consolidated" worksheet is the required output worksheet. It lists all the names from all 26 worksheets.
In A2 and copied down until you see blank rows

Formula:

Please Login or Register  to view this content.

The attached workbook provides 3 sample worksheets for A, B and C named "a", "b", "c". To adapt this workbook to your needs:
1) Add further worksheets for D-Z.
2) If you use different worksheet names then update helper!A:A accordingly.
3) Populate each worksheet
4) Make sure that the formula in Consolidated!A:A is copied down far enough to cover all names.

That's it!

Hope this helps - let me know.

Hi

Im trying to get the list for each page MP's MEP's and Lords) to populate the combined list with one starting where the last one finishes.
Some of these lists have over 800 entires so I've just done a few rows in each one.

Regards

Mark

Hi Mark,

Is this really your first post, after joining the Forum in 2008 ?

It looks like you have just tagged on your query to an existing thread, rather than start a new thread yourself. This is known as "hijacking" a thread, and is against the rules (view these at the top of the screen).

Please start your own (new) thread.

Hope this helps.

Pete

Thanks Pete