How to merge or combine two excel files onto 1 worksheet

[Manny74]

So I have 2 Microsoft Excel files....(see below)
How do merge/combine the two files onto 1 worksheet? Without having to copy & paste.....


1 is called, let's say Test1

NSU	PNAME	MED REC	DISCH_DISPOSITION
5W	BROWN, CHARLIE	999	SNF
5N	WOODSTOCK, JOHN	888	Home Health
4E	BENNETT, LYLE	777	TRANSFER

The other is called, let's say, Test2

NURSE_UNIT	PATIENT	PERSON ID	DISCHARGE_ORDER_DETAILS
3E	DOE, JOHN	666	SNF
3S	DOE, JOSEPH	555	HOME HEALTH
3N	DORSEY, LUKE	444	HOME HEALTH

 

[Joe4]

Well, here is one way.

Let's say that on sheet "Test1", your data is in cells A1:D4
And on sheet "Test2", your data is in cells A1:D4 also

On sheet Test1, you could go to cell A5, and enter this formula:
=Test2!A2
now autofill/drag that formula down to row 7 and out to column D, and you will have all your data on sheet "Test1".

 

[Manny74]

Hi Joe4, This is a great workaround!
Its working well.
But is there a way to merge and/or combine two worksheets, without using formulas?

 

[Manny74]

I tried clicking on the Data tab, clicking on Consolidate but I see my options are count, sum, average, etc.
It seems its more for number totals?

 

[alansidman]

I'm a bit confused about your term to merge/combine. What do you want your end result to look like based upon the example provided.

 

[Manny74]

Hi Alan, I'd like to combine/merge the two excel spreadsheets onto one spreadsheet, to look like this:

NSU	PNAME	MED REC	PERSON ID	DISCH_DISPOSITION	DISCHARGE_ORDER_DETAILS
5W	BROWN, CHARLIE	999		SNF
5N	WOODSTOCK, JOHN	888		Home Health
4E	BENNETT, LYLE	777		TRANSFER
3E	DOE, JOHN		666		SNF
3S	DOE, JOSEPH		555		HOME HEALTH
3N	DORSEY, LUKE		444		HOME HEALTH

 

[alansidman]

If you change the headers to be exactly the same for the Unit and Patient, you can do an append very quickly using Power Query. The end result will look like the following:

Book1
	A	B	C	D	E	F
1	NSU	PNAME	MED REC	DISCH_DISPOSITION	PERSON ID	DISCHARGE_ORDER_DETAILS
2	5W	BROWN, CHARLIE	999	SNF
3	5N	WOODSTOCK, JOHN	888	Home Health
4	4E	BENNETT, LYLE	777	TRANSFER
5	3E	DOE, JOHN			666	SNF
6	3S	DOE, JOSEPH			555	HOME HEALTH
7	3N	DORSEY, LUKE			444	HOME HEALTH
Sheet3

 

[Joe4]

The only other option I have to offer you other than copy/paste or using formula is some sort of a VBA solution (and Alan has offered a Power Query solution).
I think that pretty covers all the bases.
Excel hasn't come up with a mind-reading solution yet, maybe in the next release!