JarredSpec
New Member
- Joined
- Jan 7, 2018
- Messages
- 3
Hey all, got a hairy one for ya. not sure if its a Vlookup/If combo or something best solved with Macros (or both!). Kinda stuck on where to start hah.
I work in a lab, I'm currently building a spread sheet to perform unit conversions based on a test code for a range of tests. I currently have a worksheet that I have managed to get importing the raw test data and the associated test codes on two sheets within the workbook. What I'm wanting is to make a replica for the raw test data sheet, but have it with the corrected values based on the test code (therefore unit) it requires. Its tricky as the way the data is imported spreads stuff out a bit and I want to make it so the technicians can't manipulate this (in error) before applying the calculations.
So!
Sheet2 gets the raw test data. With the sample ID (Format: XXXXXXXXXX-XXXXXXXXX, sometimes XX+XX-XXXXX-XXXX) in Column A and then the 16 analytes from columns G to V. Test code format is TXXXX-XX. Sheet4 (called Test Codes) has a list of all the test codes and their corresponding units. For example T2000-05 is Aluminium in mg/100g.
Sheet3 gets the Sample info Data. The first half of the sample ID (before the "-") in column D and the 2nd hand in column E (After the "-"). The test codes for that sample number are in column N.
Sheet4 has a list of all the test codes (column B) with their unit (Column D) and Detection limit (Column E, Will get to that last bit later)
Now heres the kicker.
Columns D and E will have multiple instances of each Sample number as they have multiple tests
eg:
D E .......... N O
1234567890|123456789|T1234-01| Arsenic
1234567890|123456789|T1235-05| Chromium
1234567890|123456789|T1500-13| Lead
What I'm after is on Sheet5 is for it to make a copy of whats on Sheet2, but for a macro/formula to look at the sample numbers in column A, reference its test code for each analyte on Sheet3 and perform a unit calculation from ppb to whatever units the test code specifys and if its below the detection limit return a less than X.XX value.
The plan is to make this as simple and as quick as possible with as little data manipulation by the user as can be managed. Ideally import the two sets of data (Sheet2 and Sheet3), click a button and perform the calculation, so they can print out the resulting calculated data in a format that's identical to the original import, just with the corrected values.
Phew! I think that's everything hah. Thanks in advance!
I work in a lab, I'm currently building a spread sheet to perform unit conversions based on a test code for a range of tests. I currently have a worksheet that I have managed to get importing the raw test data and the associated test codes on two sheets within the workbook. What I'm wanting is to make a replica for the raw test data sheet, but have it with the corrected values based on the test code (therefore unit) it requires. Its tricky as the way the data is imported spreads stuff out a bit and I want to make it so the technicians can't manipulate this (in error) before applying the calculations.
So!
Sheet2 gets the raw test data. With the sample ID (Format: XXXXXXXXXX-XXXXXXXXX, sometimes XX+XX-XXXXX-XXXX) in Column A and then the 16 analytes from columns G to V. Test code format is TXXXX-XX. Sheet4 (called Test Codes) has a list of all the test codes and their corresponding units. For example T2000-05 is Aluminium in mg/100g.
Sheet3 gets the Sample info Data. The first half of the sample ID (before the "-") in column D and the 2nd hand in column E (After the "-"). The test codes for that sample number are in column N.
Sheet4 has a list of all the test codes (column B) with their unit (Column D) and Detection limit (Column E, Will get to that last bit later)
Now heres the kicker.
Columns D and E will have multiple instances of each Sample number as they have multiple tests
eg:
D E .......... N O
1234567890|123456789|T1234-01| Arsenic
1234567890|123456789|T1235-05| Chromium
1234567890|123456789|T1500-13| Lead
What I'm after is on Sheet5 is for it to make a copy of whats on Sheet2, but for a macro/formula to look at the sample numbers in column A, reference its test code for each analyte on Sheet3 and perform a unit calculation from ppb to whatever units the test code specifys and if its below the detection limit return a less than X.XX value.
The plan is to make this as simple and as quick as possible with as little data manipulation by the user as can be managed. Ideally import the two sets of data (Sheet2 and Sheet3), click a button and perform the calculation, so they can print out the resulting calculated data in a format that's identical to the original import, just with the corrected values.
Phew! I think that's everything hah. Thanks in advance!
