Combine multiple sheets to 1 sheet

问题:

Is there any way to combine 2 or more sheets to 1 sheet without using VBA?

I was thinking of having a unique value in the first column, then VLOOKUP for the rest. but i'm having a hard time figuring out how to return to the first cell in the 2nd sheet.

回答1:

Using your sample data image (it's important that numbers remain numbers in column A), these two standard formulas are destined for Sheet3!A2:B2.

=IFERROR(INDEX(Sheet1!A$2:INDEX(Sheet1!A:A, MATCH(1E+99, Sheet1!A:A)), ROW(1:1)), IFERROR(INDEX(Sheet2!A$2:INDEX(Sheet2!A:A, MATCH(1E+99, Sheet2!A:A)), ROW(1:1)-COUNT(Sheet1!A:A)), ""))
=IFERROR(VLOOKUP($A2, Sheet1!$A:B, COLUMN(B:B), FALSE), IFERROR(VLOOKUP($A2, Sheet2!$A:B, COLUMN(B:B), FALSE), ""))

Fill B2 right to C2 then fill A2:C2 down far enough to catch all available entries.

You may want to consider a conditional formatting rule used as a warning when there are not enough formulas to cover the entries from Sheet1 and Sheet2. Something like =COUNT(Sheet1!$A:$A)+COUNT(Sheet2!$A:$A)<>COUNT(Sheet3!$A:$A) to turn the header red.

There is a formula for retrieving the appropriate source worksheet name to an additional column in Combine 2 Excel tables into one appending the data.