Hello everyone,
I have a spreadsheet with thousands of IDs, each with several random dates of tests and in each row of that date the test results in numbers. I was wondering if someone could help me to classify automatically all the IDs based on the following rules, with a macro to be more efficient as real spreadsheet as thousands of data:
A negative result is anything <30
A positive result is anything exactly 30 or greater
Within test wording counts the test itself.
Tests and cells with any numerical value (including zero, an empty cell doesn't count)
The number of rows varies from file to file but all start in the same position and follows the same format
I think working from T5 to t0 might be easier? Think it will flow better with the rules and avoid certain rules errors. If it doesn’t match T5 requirements move to T4, if doesn’t match T4 requirements move to T3 etc but just my opinion you are the experts!
Just for here, P is positive and N is negative. Some examples after the explanation to be easier to explain. Examples flow are left to right where left are the oldest tests and right the latest:
T5 = Repeat Positive - Minimum two tests. Two or more positive results in any four consecutive tests at any time in individual test history. Once a T5 always a T5 (NPNP, PNNP, NNPP, PP, PPP, PPPP, NNNPPNNNNNNN, NPNNPNNNNNP)
T4 = Latest test is Positive (P, NNNNNP, NNP, NNPNNNNP)
T3 = Latest test is Negative but Positive on previous test Minimum 2 tests (PN, NPN, NNNNPN)
T2 = Latest test is Negative but one Positive within three previous tests. Minimum 3 tests (PNN, NNNPNN)
T1 = Negative one test only in all the history, that was the only test the ID had ever and it was negative (N)
T0 = two or more tests have been done and the two more recent ones are negative. Minimum 2 tests (NN, PNNNNNN, PNNNNPNNNNNPNNNNNNN)
If an ID exists but no test ever was done, then no classification (blank)
Any doubt or unsure about a rule in this post please do ask, I've tried to explain the best as as specific as I could.
Example spreadsheet with results done manually is attached by onedrive (couldn't find where to simply attach the file sorry)
Example Spreadsheet
I have a spreadsheet with thousands of IDs, each with several random dates of tests and in each row of that date the test results in numbers. I was wondering if someone could help me to classify automatically all the IDs based on the following rules, with a macro to be more efficient as real spreadsheet as thousands of data:
A negative result is anything <30
A positive result is anything exactly 30 or greater
Within test wording counts the test itself.
Tests and cells with any numerical value (including zero, an empty cell doesn't count)
The number of rows varies from file to file but all start in the same position and follows the same format
I think working from T5 to t0 might be easier? Think it will flow better with the rules and avoid certain rules errors. If it doesn’t match T5 requirements move to T4, if doesn’t match T4 requirements move to T3 etc but just my opinion you are the experts!
Just for here, P is positive and N is negative. Some examples after the explanation to be easier to explain. Examples flow are left to right where left are the oldest tests and right the latest:
T5 = Repeat Positive - Minimum two tests. Two or more positive results in any four consecutive tests at any time in individual test history. Once a T5 always a T5 (NPNP, PNNP, NNPP, PP, PPP, PPPP, NNNPPNNNNNNN, NPNNPNNNNNP)
T4 = Latest test is Positive (P, NNNNNP, NNP, NNPNNNNP)
T3 = Latest test is Negative but Positive on previous test Minimum 2 tests (PN, NPN, NNNNPN)
T2 = Latest test is Negative but one Positive within three previous tests. Minimum 3 tests (PNN, NNNPNN)
T1 = Negative one test only in all the history, that was the only test the ID had ever and it was negative (N)
T0 = two or more tests have been done and the two more recent ones are negative. Minimum 2 tests (NN, PNNNNNN, PNNNNPNNNNNPNNNNNNN)
If an ID exists but no test ever was done, then no classification (blank)
Any doubt or unsure about a rule in this post please do ask, I've tried to explain the best as as specific as I could.
Example spreadsheet with results done manually is attached by onedrive (couldn't find where to simply attach the file sorry)
Example Spreadsheet
Last edited:
