Greetings all,
I have an analytical piece of equipment that exports data for each sample analyzed into a separate .txt file. I would like to compile all of the data from analyses performed on one day into one summary workbook. Ideally, there would be a workbook template in which you enter the desired analysis day from which you want the data compiled and it would pull it into the 10 columns (with one sample per row).
All of the exports are in the same format but the location of the cells for the required information can vary. Specifically, these are the 10 required columns:
Columns A:D will be in the same position in all the files:
A='Sheet1'!$A$19
B='Sheet1'!$C$19
C='Sheet1'!$D$19
D='Sheet1'!$C$23
Columns E:J will not be cell-specific but can be located in each file:
E=INDIRECT("'Sheet1'!C"&Sheet1!O2)
F=INDIRECT("'Sheet1'!D"&Sheet1!O2)
G=INDEX('Sheet1'!A23:A264,MATCH("$S props V2",'Sheet1'!A23:A264,0)+1,1)
H=INDEX('Sheet1'!E23:E264,MATCH("$S props V2",'Sheet1'!A23:A264,0)+1,1)
I=INDEX('Sheet1'!B23:B264,MATCH("N",'Sheet1'!A23:A264,0),1)
J=INDEX('Sheet1'!B23:B264,MATCH("PROTEIN",'Sheet1'!A23:A264,0),1)
My questions are:
1) Will Excel be able to scan and interpret a .txt file without being converted first? Or would each file have to be saved as an excel-compatible format before proceeding? (unfortunately, for whatever reason, the software will only export as .txt)
2) Would there be a way on a new workbook to input the desired analysis date and then the target folder would search any files created on that date and the respective data would be extracted?
3) Is it even possible to extract the data with this set of requirements since they're not all in the same target cell?
4) Because it is coming in as a text file, “Sheet1” will actually always just be named after the name of the file and would therefore not be a set name.
Thank you so much in advance and please let me know if there's any further info you require!
I have an analytical piece of equipment that exports data for each sample analyzed into a separate .txt file. I would like to compile all of the data from analyses performed on one day into one summary workbook. Ideally, there would be a workbook template in which you enter the desired analysis day from which you want the data compiled and it would pull it into the 10 columns (with one sample per row).
All of the exports are in the same format but the location of the cells for the required information can vary. Specifically, these are the 10 required columns:
Columns A:D will be in the same position in all the files:
A='Sheet1'!$A$19
B='Sheet1'!$C$19
C='Sheet1'!$D$19
D='Sheet1'!$C$23
Columns E:J will not be cell-specific but can be located in each file:
E=INDIRECT("'Sheet1'!C"&Sheet1!O2)
F=INDIRECT("'Sheet1'!D"&Sheet1!O2)
G=INDEX('Sheet1'!A23:A264,MATCH("$S props V2",'Sheet1'!A23:A264,0)+1,1)
H=INDEX('Sheet1'!E23:E264,MATCH("$S props V2",'Sheet1'!A23:A264,0)+1,1)
I=INDEX('Sheet1'!B23:B264,MATCH("N",'Sheet1'!A23:A264,0),1)
J=INDEX('Sheet1'!B23:B264,MATCH("PROTEIN",'Sheet1'!A23:A264,0),1)
My questions are:
1) Will Excel be able to scan and interpret a .txt file without being converted first? Or would each file have to be saved as an excel-compatible format before proceeding? (unfortunately, for whatever reason, the software will only export as .txt)
2) Would there be a way on a new workbook to input the desired analysis date and then the target folder would search any files created on that date and the respective data would be extracted?
3) Is it even possible to extract the data with this set of requirements since they're not all in the same target cell?
4) Because it is coming in as a text file, “Sheet1” will actually always just be named after the name of the file and would therefore not be a set name.
Thank you so much in advance and please let me know if there's any further info you require!
