Extract data from multiple file and create summary

[Aoic]

Hi Macro experts,

I have around 1000 files (all in similar format) stored in one folder. In each file, there is a table like below example - 1. The first row of the table can be any row between 3rd and 16th; the columns and sequence of the header are fixed, always from column A to I (headers are from "Piece name to "Y Dimension")

I want to run a macro, and it goes to all the file in the folder and retrieve data from the table based on column E ("Material"). And create a summary report in a new workbook like example - 2

The Summary records target file name in column A; followed by the unique material code from that file which starts with "L"; followed by the total quantity of each Material code (retrieve from column B in example - 1), and finally the total area of each Material code (retrieve from column B and C using sumproduct in example - 1).

Thank you so much for your help!!

Example - 1

	A	B	C	D	E	F	G	H	I
1	Piece name	Quantity	Area	Perimeter	Material code	Piece description		X Dimension	Y Dimension
2	Part1	1	0.2239	28.04	F	Fabric		8.27	4.22
3	Part2	1	0.8154	45.10	F	Fabric		10.94	10.84
4	Part3	1	0.8091	43.25	F	Fabric		10.94	10.84
5	Part4	2	0.0168	6.09	L	leather		1.14	2.17
6	Part5	1	0.2564	30.37	L	leather		10.98	3.32
7	Part6	1	0.0492	10.38	L1	leather - 1		2.77	2.38
8	Part7	1	0.2494	27.11	L1	leather - 1		5.75	7.27
9	Part8	1	0.4402	33.77	L1	leather - 1		5.75	10.84
10	Part9	1	0.0666	26.28	L2	leather - 2		11.42	0.79
11	Part10	4	0.0344	17.06	L2	leather - 2		7.37	0.75
12	Part11	2	0.0230	11.04	L3	leather - 3		4.72	1.10
13	Part12	2	0.3406	33.67	T	Polyester		9.00	7.91
14	Part13	1	0.0454	22.81	XB	Filler		8.77	1.99
15	Part14	2	0.0069	31.10	XS	Filler		9.93	2.65

<tbody>
</tbody>

Example - 2

File name	Material	Quantity	Total Area
datasheet-1	L	3	0.2899
datasheet-1	L1	3	0.7388
datasheet-1	L2	5	0.2042
datasheet-1	L3	2	0.0461

<tbody>
</tbody>

 

[oldbrewer]

first of all well done giving us such good clear information.

Is it possible to amalgamate all your data into one summary sheet, creating a new column A with sheet1 sheet2 etc in it

 

[oldbrewer]

sheet	Piece name	Quantity	Area	Perimeter	Material code	Piece description	X Dimension	Y Dimension
sheet1	Part1	1	0.2239	28.04	F	Fabric		8.27	4.22
sheet1	Part2	1	0.8154	45.1	F	Fabric		10.94	10.84
sheet1	Part3	1	0.8091	43.25	F	Fabric		10.94	10.84
sheet1	Part4	2	0.0168	6.09	L	leather		1.14	2.17
sheet1	Part5	1	0.2564	30.37	L	leather		10.98	3.32
sheet1	Part6	1	0.0492	10.38	L1	leather - 1		2.77	2.38
sheet1	Part7	1	0.2494	27.11	L1	leather - 1		5.75	7.27
sheet1	Part8	1	0.4402	33.77	L1	leather - 1		5.75	10.84
sheet1	Part9	1	0.0666	26.28	L2	leather - 2		11.42	0.79
sheet1	Part10	4	0.0344	17.06	L2	leather - 2		7.37	0.75
sheet1	Part11	2	0.023	11.04	L3	leather - 3		4.72	1.1
sheet1	Part12	2	0.3406	33.67	T	Polyester		9	7.91
sheet1	Part13	1	0.0454	22.81	XB	Filler		8.77	1.99
sheet1	Part14	2	0.0069	31.1	XS	Filler		9.93	2.65
sheet2	Part1	7	0.2239	28.04	F	Fabric		8.27	4.22
sheet2	Part2	7	0.8154	45.1	F	Fabric		10.94	10.84
sheet2	Part3	7	0.8091	43.25	F	Fabric		10.94	10.84
sheet2	Part4	7	0.0168	6.09	L	leather		1.14	2.17
sheet2	Part5	7	0.2564	30.37	L	leather		10.98	3.32
sheet2	Part6	7	0.0492	10.38	L1	leather - 1		2.77	2.38
sheet2	Part7	7	0.2494	27.11	L1	leather - 1		5.75	7.27
sheet2	Part8	7	0.4402	33.77	L1	leather - 1		5.75	10.84
sheet2	Part9	7	0.0666	26.28	L2	leather - 2		11.42	0.79
sheet2	Part10	7	0.0344	17.06	L2	leather - 2		7.37	0.75
sheet2	Part11	7	0.023	11.04	L3	leather - 3		4.72	1.1
sheet2	Part12	7	0.3406	33.67	T	Polyester		9	7.91
sheet2	Part13	7	0.0454	22.81	XB	Filler		8.77	1.99
sheet2	Part14	7	0.0069	31.1	XS	Filler		9.93	2.65
					L	L1
				sheet1	0.2900	0.7388
				sheet2	1.9124	5.1716
					L	L1
				sheet1	3	3
				sheet2	14	21
			the tables use simple sumproduct formulas

<colgroup><col span="12"></colgroup><tbody>
</tbody>

 

[Aoic]

Hi oldbrewer,

Yes, the summary is amalgamated with all the file in that designated folder (like below revised example)
Thank you very much for your help!

File name	Material	Quantity	Total Area
datasheet-1	L	3	0.2899
datasheet-1	L1	3	0.7388
datasheet-1	L2	5	0.2042
datasheet-1	L3	2	0.0461
datasheet-2	L	…	…
datasheet-2	L1	…	…
datasheet-3	L	…	…
datasheet-3	L1	…	…

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

first of all well done giving us such good clear information.

Is it possible to amalgamate all your data into one summary sheet, creating a new column A with sheet1 sheet2 etc in it

 

[Aoic]

Hi oldbrewer,

I think I didn't explain clearly about my needs. Sorry about my English as I'm not native speaker.
The example 1 table is existed in all the excel files in a folder (always in sheet1). I need a macro to pull data from each single excel file from that folder and only extract those with a material code starts with L. Then, the macro creates a summary sheet that list down all unique L material code for every file in that folder, and calculated quantity and total area of each file. Hopefully this time I made it clearer....or not >_<

sheet	Piece name	Quantity	Area	Perimeter	Material code	Piece description	X Dimension	Y Dimension
sheet1	Part1	1	0.2239	28.04	F	Fabric		8.27	4.22
sheet1	Part2	1	0.8154	45.1	F	Fabric		10.94	10.84
sheet1	Part3	1	0.8091	43.25	F	Fabric		10.94	10.84
sheet1	Part4	2	0.0168	6.09	L	leather		1.14	2.17
sheet1	Part5	1	0.2564	30.37	L	leather		10.98	3.32
sheet1	Part6	1	0.0492	10.38	L1	leather - 1		2.77	2.38
sheet1	Part7	1	0.2494	27.11	L1	leather - 1		5.75	7.27
sheet1	Part8	1	0.4402	33.77	L1	leather - 1		5.75	10.84
sheet1	Part9	1	0.0666	26.28	L2	leather - 2		11.42	0.79
sheet1	Part10	4	0.0344	17.06	L2	leather - 2		7.37	0.75
sheet1	Part11	2	0.023	11.04	L3	leather - 3		4.72	1.1
sheet1	Part12	2	0.3406	33.67	T	Polyester		9	7.91
sheet1	Part13	1	0.0454	22.81	XB	Filler		8.77	1.99
sheet1	Part14	2	0.0069	31.1	XS	Filler		9.93	2.65
sheet2	Part1	7	0.2239	28.04	F	Fabric		8.27	4.22
sheet2	Part2	7	0.8154	45.1	F	Fabric		10.94	10.84
sheet2	Part3	7	0.8091	43.25	F	Fabric		10.94	10.84
sheet2	Part4	7	0.0168	6.09	L	leather		1.14	2.17
sheet2	Part5	7	0.2564	30.37	L	leather		10.98	3.32
sheet2	Part6	7	0.0492	10.38	L1	leather - 1		2.77	2.38
sheet2	Part7	7	0.2494	27.11	L1	leather - 1		5.75	7.27
sheet2	Part8	7	0.4402	33.77	L1	leather - 1		5.75	10.84
sheet2	Part9	7	0.0666	26.28	L2	leather - 2		11.42	0.79
sheet2	Part10	7	0.0344	17.06	L2	leather - 2		7.37	0.75
sheet2	Part11	7	0.023	11.04	L3	leather - 3		4.72	1.1
sheet2	Part12	7	0.3406	33.67	T	Polyester		9	7.91
sheet2	Part13	7	0.0454	22.81	XB	Filler		8.77	1.99
sheet2	Part14	7	0.0069	31.1	XS	Filler		9.93	2.65
					L	L1
				sheet1	0.2900	0.7388
				sheet2	1.9124	5.1716
					L	L1
				sheet1	3	3
				sheet2	14	21
			the tables use simple sumproduct formulas

<tbody>
</tbody>

 

[oldbrewer]

to be totally clear then, in one folser you have 1000 .xls files, and in EACH file, sheet1 contains the data you are interested in. You want to pull every row where material code starts with L putting the results into a single sheet.

How many rows in each of the 1000 files ?

 

[Aoic]

Yes, and when pulling out every row starts with L, the macro calculates quantity subtotal and area subtotal (sumproduct of quantity and area) for each L type. for example, there are 3 kinds of material code L1, L2, and L3. the summary will have 3 rows, L1 row with total L1 quantity / area (combining all rows with L1), L2 row with total L2 quantity and area, and same for L3 row.
In those 1000 files, the number of rows changes, but no more than 100.
Thank you very much for your patience

to be totally clear then, in one folser you have 1000 .xls files, and in EACH file, sheet1 contains the data you are interested in. You want to pull every row where material code starts with L putting the results into a single sheet.

How many rows in each of the 1000 files ?