Join 2 worksheets in a 3rd

**leviathan185** · 07-02-2009, 10:06 PM

Hi,

I am trying to consolidate 2 reports into 1. Both sheets have exacly the same heading structure. I want to be able to open the 3 sheets at once and have the 2 existing reports 1 on top of the other in the 3rd spreadsheet.

If someone could help I would be so greatful this will turn a mundane timeconsuming task into a very quick and easy task.

**JBeaucaire** · 07-02-2009, 10:34 PM

Seems simple enough to do. Post up your workbook and make sure the 3rd sheet is in the layout you want, include any notes about things that aren't the same all the time (like # of rows, # of columns)...whatever hurdles are to be overcome.

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

**leviathan185** · 07-02-2009, 11:00 PM

Originally Posted by JBeaucaire

Seems simple enough to do. Post up your workbook and make sure the 3rd sheet is in the layout you want, include any notes about things that aren't the same all the time (like # of rows, # of columns)...whatever hurdles are to be overcome.

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

Thank you for you help JBeaucaire, I have attached the 3 spreadsheets as requesetd. I have only left 3 rows of data in each spread sheet and changed the values that they normally are to protect sensitive data but there is normally at least 200 rows of data in each spreadsheet. the number of row in each spreadsheet will vary each time these reports are run.

Thanks,

Leviathan185

**JBeaucaire** · 07-02-2009, 11:38 PM

This master has two helper formulas added in P:Q. With those value, the table will fill itself out with this INDEX formula:

=IF(ROW()<=$Q$1,INDEX([WorkbookA.xls]Sheet1!A:A,ROW()),INDEX([WorkbookB.xls]Sheet1!A:A,ROW()-$Q$1+1))

That same formula in A2 is copied down 500 rows and across. It pulls back the values from the other workbooks.

**leviathan185** · 07-03-2009, 12:58 AM

I think I may be doing something wrong? when i put that formula in it will only get the data from WorkbookB.xls?

**JBeaucaire** · 07-03-2009, 02:29 AM

Did you put in the helper formulas in P1:Q2?

**leviathan185** · 07-03-2009, 03:13 AM

HaHA I am such a R-Tard. yes it work perfectly now. Thank You so much!

If you wouln't mind i would really like to know how that works so i can learn from that rather that just using it no knowing how it works and possibly implement it in other projects.

you don't have to though you have been a great help already. Thanks again.

Leviathan185

**JBeaucaire** · 07-03-2009, 08:30 AM

You know how to use ROW()? Putting =ROW() in row2 gives an answer of 2. In row4, it equals 4, ok?

INDEX(Range,2) gives you the 2nd item in the range. So INDEX(A:A,2) gives you the second item in column A.

If you put this formula in row5: =INDEX(A:A,ROW()), it gives you the 5th item in column 5. Make sense so far? That's all we're doing here.

In Q1 we are counting how many rows of data there are in WbA. Then we are pulling that many rows of data FROM wbA.
=IF(ROW()<=$Q$1,INDEX([WorkbookA.xls]Sheet1!A:A,ROW()),INDEX([WorkbookB.xls]Sheet1!A:A,ROW()-$Q$1+1))

When our formula reaches a row() greater than the number in Q1, it switches to the second formula... =INDEX([WorkbookB.xls]Sheet1!A:A,ROW()-$Q$1+1) This one is a little trickier because we have to convert the first ROW() formula back to a value of 2 since the first thing we want from wbB is the data in row2, right?

So, let's say Q1 = 4, that means when we get to row5, it switches. Now we don't want to start with row5 in wbB, so ROW() won't work by itself. We have to convert it back to 2. Since we're on row5, and Q1 = 4, we subtract those two numbers, then add 1 to get back to a value of 2. =ROW()-Q1+1 Now, that half of the formula is drawing information back from the second wbB.

I copies that formula down 500 rows to cover what you said would be about 200 rows in each book. Then copied to the right to fill out the table.