Extract data – Multiple Files – to Single File – in Excel 2007

[nostradamus]

Hi all,

I need help with appending data from several excel files with the same “data titles” into one single Excel MasterFile. I know there is lot of help/codes out there for simple merge/append, but this one has to match column/row titles and extract relevant data, and some Titles may not be in the same order.

I want all this data to be brought in a single row in the new Excel MasterFile.
So data from each data file will be arranged under appropriate Column Titles without error.

Some files may have info only from few rows (only T1, T2, A1, EdgeA - as shown below).

Location where files saved are:
Master excel file is saved in Folder C:\Users\Nostro\Documents\MasterFile
Multiple excel files are saved in Folder C:\Users\Nostro\Documents\MultipleFiles

Sample Data file

	A	B	C	D
1	GB_40X_064
2	FeatureName	LocationX(mm)	LocationY(mm)	Length(mm)
3	Core	1.50	1.71
4	T1			1.43
5	T2			1.33
6	A1	2.10	1.61
7	A2	2.34	1.57
8	A3	2.55	1.53
9	A4	2.75	1.51
10	EdgeA	2.91	1.48
11	A1b	1.35	1.21
12	A2b	1.29	1.00
13	A3b	1.18	0.67
14	A4b	1.14	0.52
15	EdgeB	1.11	0.44
16	Inc_1			0.61
17	Inc_2			0.85
18	Inc_3			1.06
19	Inc_4			1.27
20	Inc_EdgeA			1.43
21	Inc_1b			0.52
22	Inc_2b			0.74
23	Inc_3b			1.09
24	Inc_4b			1.25
25	Inc_EdgeB			1.33

<tbody>
</tbody>

Master File where I need data to be extracted to based on the column/row title

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

1

Fish#

Core_LocationX(mm)

Core_LocationY(mm)

A1_LocationX(mm)

A2_LocationX(mm)

A3_LocationX(mm)

A4_LocationX(mm)

EdgeA_LocationX(mm)

A1b_LocationX(mm)

A2b_LocationX(mm)

A3b_LocationX(mm)

A4b_LocationX(mm)

EdgeB_LocationX(mm)

A1_LocationY(mm)

A2_LocationY(mm)

A3_LocationY(mm)

A4_LocationY(mm)

EdgeA_LocationY(mm)

A1b_LocationY(mm)

A2b_LocationY(mm)

A3b_LocationY(mm)

A4b_LocationY(mm)

EdgeB_LocationY(mm)

T1_Length(mm)

T2_Length(mm)

Inc_1_Length(mm)

Inc_2_Length(mm)

Inc_3_Length(mm)

Inc_4_Length(mm)

Inc_EdgeA_Length(mm)

Inc_1b_Length(mm)

Inc_2b_Length(mm)

Inc_3b_Length(mm)

Inc_4b_Length(mm)

Inc_EdgeB_Length(mm)

2

3

4

5

6

<tbody>
</tbody>

So I need data from all the small files stored in the MultipleFiles folder to be pulled in the Master file in MasterFile folder location.

I think a vba code here would be appropriate. I can post a few MultipleExcelFiles & the MasterExcel file here if it would help (just not sure how I could go about doing it tho).

Thanks a million for going thru my long arduous post.