Extract data from three sheets from several excel files

[abuzahira]

I have data that I want to extract for the daily analysis of our lab, our lab is generating three excel sheets per day for three shifts. Each excel file name is saved as ShiftName+Date
ShiftName(M:morning, A:afternoon, N:night). Each excel file has three sheets with the names CGPI, CGPII, CGPIII.

The results are entered manually by the chemist in the three sheets, the tables are identical in the three sheets but are not organized in a way that could help you to extract the data from easily.

Ref.No.	Date issued	Time issued	Shift	No. of 1st Sheet	Plant	No. of Sheet	SHEET PAGE 2
A-02-01-23	02-01-23	2230	Afternoon	1 OF 1	III	2 OF 2
Liquid & other Liquid	Liquid & other	Liquid & other	Liquid & other	Liquid & other	Liquid & other	Liquid & other Liquid	Liquid & other	Liquid & other	Liquid & other	Liquid & other	Liquid & other
Date of Sampling	02-01-23	02-01-23				Date of Sampling
Time of Sampling	1535	2025				Time of Sampling
Sample Source	NAPHTHA-R/D	NAPHTHA-R/D				Sample Source
R.V.P-PSI(Kpa)	11.4(78.5)					R.V.P-PSI(Kpa)
Colour	+30	+30				Colour
API at 60F°						API at 60F°
Density						Density
IBP						IBP
10 % REC						10 % REC
50 % REC						50 % REC
70 % REC						70 % REC
90 % REC						90 % REC
EPT °C						EPT °C
% REC						% REC
Atmos.Pressure						Atmos.Pressure
Kpa						Kpa
Transfer No.						Transfer No.
PH						PH
Water Content						Water Content
Purity						Purity
Foam test						Foam test
water cont. in amine						water cont. in amine
H2S						H2S
Mercury						Mercury
Remarks						Remarks
Ambient Temperature	Maximum	Minimum	Average	NAPHTHA-TK204D001	NAPHTHA-TK205D001
			NAPHTHA-TK204D002
Liquid & other Liquid	Liquid & other	Liquid & other	Liquid & other	NAPH-TK	NAPH-TK	Liquid & other Liquid	Liquid & other	Liquid & other	Liquid & other	NAPH. TK	NAPH. TK
Date of Sampling				02-01-23		Date of Sampling
Time of Sampling				1705		Time of Sampling
Sample Source				NAPHTHA-TK306D-901A		Sample Source
R.V.P-PSI(Kpa)				10.4(71.5)		R.V.P-PSI(Kpa)
Colour				+30		Colour
API at 60F°						API at 60F°
Density				0.670		Density
IBP				35		IBP
10 % REC				46		10 % REC
50 % REC				58		50 % REC
70 % REC						70 % REC
90 % REC				98		90 % REC
EPT °C				129		EPT °C
% REC				98.1		% REC
Atmos.Pressure				765		Atmos.Pressure
Kpa						Kpa
Transfer No.				N-23-004		Transfer No.
PH						PH
Water Content						Water Content
Purity						Purity
Foam test						Foam test
water cont. in amine						water cont. in amine
H2S						H2S
Mercury						Mercury
Remarks						Remarks
LEAN GLYCOL CS-1	LEAN GLYCOL CS-5	LEAN GLYCOL CS-9	FEED CONDENSATE FROM CS-4	FEED CONDENSATE FROM CS-7
LEAN GLYCOL CS-3	LEAN GLYCOL CS-6	FEED CONDENSATE FROM CS-1	FEED CONDENSATE FROM CS-5	FEED CONDENSATE FROM CS-9
LEAN GLYCOL CS-4	LEAN GLYCOL CS-7	FEED CONDENSATE FROM CS-3	FEED CONDENSATE FROM CS-6
Compressor Station	Station	Station	Station	Station	Station	Compressor Station	Station	Station	Station	Station	Station
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Sample Source						Sample Source
R.V.P-PSI(Kpa)						R.V.P-PSI(Kpa)
Colour						Colour
API at 60F°						API at 60F°
Density						Density
IBP						IBP
10 % REC						10 % REC
50 % REC						50 % REC
70 % REC						70 % REC
90 % REC						90 % REC
EPT °C						EPT °C
% REC						% REC
Atmos.Pressure						Atmos.Pressure
Kpa						Kpa
Transfer No.						Transfer No.
PH						PH
Water Content						Water Content
Purity						Purity
Foam test						Foam test
water cont. in amine						water cont. in amine
H2S						H2S
Mercury						Mercury
Remarks						Remarks
RESIDUE GAS	RESIDUE GAS COMBINED	DE-ETHANIZER O/H	FEED GAS		C-14 OUT LET
Gases	Gases	Gases	Gases	Gases	Gases	Gases	Gases	Gases	Gases	Gases	Gases
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Sample Source						Sample Source
Dew Point						Dew Point
Water Content						Water Content
Olefins						Olefins
O2						O2
H2						H2
N2						N2
C1						C1
CO2						CO2
C2						C2
H2S						H2S
C3						C3
I-C4						I-C4
N-C4						N-C4
I-C5						I-C5
N-C5						N-C5
C6+						C6+
Mercury						Mercury
Specific Gravity						Specific Gravity
Molecular weight						Molecular weight
NET						NET
GROSS						GROSS
Olefins						Olefins
Ammonia						Ammonia
Remarks						Remarks
TOTAL						TOTAL
Gas Discharge from CS-1 to CGP	Gas Discharge from CS-3 to CGP	Gas Discharge from CS-5 to CGP	Gas Discharge from CS-7 to CGP	Associated gas from CS-7 (24 inch inlet line)
Gas Discharge from CS-3 to CGP	Gas Discharge from CS-4 to CGP	Gas Discharge from CS-6 to CGP	Gas Discharge from CS-9 to CGP	Gas from CS-9 junction header outside CS-5
Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS	Gases from CS
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Sample Source						Sample Source
Dew Point						Dew Point
Water Content						Water Content
Olefins						Olefins
O2						O2
H2						H2
N2						N2
C1						C1
CO2						CO2
C2						C2
H2S						H2S
C3						C3
I-C4						I-C4
N-C4						N-C4
I-C5						I-C5
N-C5						N-C5
C6+						C6+
Mercury						Mercury
Specific Gravity						Specific Gravity
Molecular weight						Molecular weight
NET						NET
GROSS						GROSS
Olefins						Olefins
Ammonia						Ammonia
Remarks						Remarks
TOTAL						TOTAL
LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown
Date of Sampling	02-01-23	02-01-23	02-01-23	02-01-23		Date of Sampling
Time of Sampling	1520	1515	2000	2005		Time of Sampling
Sample Source	R/D	R/D	R/D	R/D		Sample Source
Product	PROPANE	BUTANE	PROPANE	BUTANE		Product
Ammonia				NIL		Ammonia
Olefins	NIL	NIL		NIL		Olefins
C2	1.0	NIL		NIL		C2
C3	98.9	0.3		0.4		C3
I-C4	0.1	24.7		27.3		I-C4
N-C4	NIL	75.0		72.3		N-C4
C5 +	NIL	NIL		NIL		C5 +
Vapour Pressure	178	43		43		Vapour Pressure
Specific Gravity						Specific Gravity
Copper Corrosion				1		Copper Corrosion
Water Content	1	1		1		Water Content
Volatile Sulfur	NIL	NIL		NIL		Volatile Sulfur
Hydrogen Sulfide	LESS THAN 1 PPM	LESS THAN 1 PPM	LESS THAN 1 PPM	LESS THAN 1 PPM		Hydrogen Sulfide
Remarks						Remarks
Total	100.00​	100.00​		100.00​		Total
LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown			LPG Rundown	LPG Rundown	LPG Rundown	LPG Rundown
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Sample Source						Sample Source
Product						Product
Ammonia						Ammonia
Olefins						Olefins
C2						C2
C3						C3
I-C4						I-C4
N-C4						N-C4
C5 +						C5 +
Vapour Pressure						Vapour Pressure
Specific Gravity						Specific Gravity
Copper Corrosion						Copper Corrosion
Water Content						Water Content
Volatile Sulfur						Volatile Sulfur
Hydrogen Sulfide						Hydrogen Sulfide
Remarks						Remarks
Total						Total
	PROPANE	BUTANE	BULLET	PUMP DISCHARGE
Bullet and Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra	Bullet and Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Sample Source						Sample Source
Tank NO.						Tank NO.
Product						Product
Ammonia						Ammonia
Olefins						Olefins
C2						C2
C3						C3
I-C4						I-C4
N-C4						N-C4
C5 +						C5 +
Vapour Pressure						Vapour Pressure
Specific Gravity						Specific Gravity
Copper Corrosion						Copper Corrosion
Water Content						Water Content
Volatile Sulfur						Volatile Sulfur
Hydrogen Sulfide						Hydrogen Sulfide
Batch NO.						Batch NO.
Remarks						Remarks
Total						Total
Bullet and Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra			Bullet and Sitra	Bullet & Sitra	Bullet & Sitra	Bullet & Sitra
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Sample Source						Sample Source
Tank NO.						Tank NO.
Product						Product
Ammonia						Ammonia
Olefins						Olefins
C2						C2
C3				INST. AIR COMP. 204	C3
I-C4				INST. AIR COMP. 205	I-C4
N-C4				GLYCOL CHILLER	N-C4
C5 +				CONTROL ROOM A/C	C5 +
Vapour Pressure				DE-PROPANIZER CHILLER	Vapour Pressure
Specific Gravity				INST. AIR COMP. 207-K151	Specific Gravity
Copper Corrosion				INST. AIR COMP. 207-K152	Copper Corrosion
Water Content						Water Content
Volatile Sulfur						Volatile Sulfur
Hydrogen Sulfide						Hydrogen Sulfide
Batch NO.						Batch NO.
Remarks				DISTILLED WATER	Remarks
Total				WATER TANKER		Total
Water and Oil	water & oil	water & oil	water & oil	water & oil	water & oil	Water and Oil	water & oil	water & oil	water & oil	water & oil	water & oil
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Sample Source						Sample Source
Conductivity						Conductivity
TDS						TDS
PH						PH
Iron						Iron
Chlorine						Chlorine
Nitrite						Nitrite
Oxygen						Oxygen
Calcium hardness						Calcium hardness
Total Hardness						Total Hardness
Flash Point						Flash Point
Viscosity						Viscosity
Remarks						Remarks
Water and Oil	water & oil	water & oil	Liquid Hydro	Liquid Hydro	Liquid Hydro	Water and Oil	water & oil	water & oil	Liquid Hydro	Liquid Hydro	Liquid Hydro
Date of Sampling			Date of Sampling			Date of Sampling			Date of Sampling
Time of Sampling			Time of Sampling			Time of Sampling			Time of Sampling
Sample Source			Sample Source			Sample Source			Sample Source
Conductivity			C2			Conductivity			C2
TDS			C3			TDS			C3
PH			IC4			PH			IC4
Iron			NC4			Iron			NC4
Chlorine			IC5			Chlorine			IC5
Nitrite			NC5			Nitrite			NC5
Oxygen			C6			Oxygen			C6
Calcium hardness			C7			Calcium hardness			C7
Total Hardness			C8			Total Hardness			C8
Flash Point			C9			Flash Point			C9
Viscosity			C10			Viscosity			C10
Remarks			C11			Remarks			C11
			C12						C12
			Remarks						Remarks
			Total						Total
Vopour Tank	Vopour Tank	Vopour Tank	Vopour Tank	Vopour Tank		Vopour Tank	Vopour Tank	Vopour Tank	Vopour Tank	Vopour Tank
Date of Sampling						Date of Sampling
Time of Sampling						Time of Sampling
Vessel Name						Vessel Name
Tank NO.						Tank NO.
Hydrogen Sulfide						Hydrogen Sulfide
Moisture						Moisture
Ammonia						Ammonia
Nitrous Gases						Nitrous Gases
Oxygen						Oxygen
Nitrogen						Nitrogen
Carbon Dioxide						Carbon Dioxide
Vinyl Chloride						Vinyl Chloride
Olefins						Olefins
Remarks						Remarks

The empty cells where we entered the results of our analysis, for the three sheets.
We we want to print all the data, we have made a report on the same sheet for the three sheets by referencing all the cells from the above tables into the report cells. as the attached image.

I tried to extract the data from the print area, because the tables are arranged properly, but I face a problem with the referenced cells when I started the extraction. I got errors and no values. I would appreciate your efforts or any feedback.

 

[Micron]

I dunno but I think you've outgrown Excel and should be using a relational database. It could even be said that the issue isn't really outgrowing something as much as it may be an issue of using a screwdriver to drive in nails. Your process/activity involves multiple workbook files with multiple sheets and it raises issues like data extraction and presentation. The problem for most Excel people is the learning curve for relational databases. The biggest mistake they make is designing tables as if they were spreadsheets. I know that all won't help you solve this problem, but I throw it out there for future consideration. Hope you get an answer to your issue.

 

[abuzahira]

Thanks, Micron
You hit the problem, I said the same when I join this place. Thats why I tried several times to do this complex job, but every time I hesitated to make effort on it. But now I need the data generated for years for manipulation. I tried so many ways to get the data extracted, but unfortunately, I am not that much expert in excel. I do my research and learn always, but I believe I do need someone's advice and help. My regards.