Macro to classify data based on test results

Status
Not open for further replies.

Newbie73

Board Regular
Joined
Feb 4, 2024
Messages
113
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:
Just a quick Edit, the tests are read from the right to the left (left are oldest ones and towards the right the latest ones)
 
Upvote 0
Reminder:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro to classify data based on test results
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Just a comment - on Excelforum it is already an extremely long thread. As for Feb 18th 09:13 GMT - 61 posts. And seems to be solved now.
 
Upvote 0
I do apologize, don't want to break any rules, other thread I did quite a few mistakes myself and made it more of a mess with those mistakes that needed to be.

Could this thread be closed please? Thank you
 
Upvote 0
Closed as requested.
Just emphasising though that cross-posting is not breaking any rules here provided you also post links to any other forums where you have posted the same question.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,226,771
Messages
6,192,917
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top