Find variables occurring between status changes

[lilybickel]

Hello everyone,
I have attached an example workbook showing records of 2 tests carried out with outcomes of "normal" or "abnormal", and the dates of 2 variables (in reality I have 100s). I need a way of identifying which variables were present between test dates where there is an outcome change from "normal" to "abnormal" or from "abnormal" to "normal".
ie. a way of searching through the test outcomes and finding where 2 consecutive outcomes are not the same; identifying the range between the dates this change occurs; searching through the dates of Variable1,Variable2... to find if any variables are within the time range where the change occurred; outputting the direction/type of change (normal->abnormal or abnormal->normal) into a column next to the variable date

I have absolutely no idea where to start with this or if it is even possible... Any ideas??? Thanks

[Marc L]

Why this duplicate thread ?‼

Better to read forum rules before creating any thread …

[lilybickel]

Hi, this isn't a duplicate thread I am asking a different question to my previous post?
The example databases are a similar layout however the problems I have asked for help with are completely unrelated. I don't know if I have explained badly or you have misread so please let me know if further clarification on my question is needed, thanks.

[JeteMc]

Assuming that you want the output in columns AB:AC and AE:AF, please manually show us the output that you would like formulas/code to produce, so that we have something with which to compare the results of our proposals.
Let us know if you have any questions.

[lilybickel]

Please see the edited worksheet that hopefully illustrates what I'm after.
Please note, I have coloured the text/cells of the tests that correspond to the outputs so that they can be referred to easier however I am not interested in having this formatting in the actual output.
It is also be worth mentioning that the test type of test2 is irrelevant to the output of the desired calculation, I have simply included it to illustrate the different test layouts I have in case this affects the actual calculations.
Where there has been a status change between two tests, I am looking for variables occurring >= [Date of the earlier test] and < [Date of the later test].
Thank you

[JeteMc]

The basic structure for the four formulas is (as modeled in cell AB3):

=IFERROR(IF(INDEX(Table1[@[Test1 result ]:[Test1 result5]],AGGREGATE(14,6,(COLUMN(Table1[@[Test1 date]:[Test1 date5]])-COLUMN([@[Test1 result ]]))/(ISNUMBER(Table1[@[Test1 date]:[Test1 date5]]))/(Table1[@[Test1 date]:[Test1 date5]]<=[@[Date of variable 1 ]]),1))=INDEX(Table1[@[Test1 result ]:[Test1 result5]],AGGREGATE(15,6,(COLUMN(Table1[@[Test1 date]:[Test1 date5]])-COLUMN([@[Test1 result ]]))/(ISNUMBER(Table1[@[Test1 date]:[Test1 date5]]))/(Table1[@[Test1 date]:[Test1 date5]]>[@[Date of variable 1 ]]),1)),"",PROPER(INDEX(Table1[@[Test1 result ]:[Test1 result5]],AGGREGATE(14,6,(COLUMN(Table1[@[Test1 date]:[Test1 date5]])-COLUMN([@[Test1 result ]]))/(ISNUMBER(Table1[@[Test1 date]:[Test1 date5]]))/(Table1[@[Test1 date]:[Test1 date5]]<=[@[Date of variable 1 ]]),1)))&" to "&PROPER(INDEX(Table1[@[Test1 result ]:[Test1 result5]],AGGREGATE(15,6,(COLUMN(Table1[@[Test1 date]:[Test1 date5]])-COLUMN([@[Test1 result ]]))/(ISNUMBER(Table1[@[Test1 date]:[Test1 date5]]))/(Table1[@[Test1 date]:[Test1 date5]]>[@[Date of variable 1 ]]),1)))),"")

Let us know if you have any questions.

[lilybickel]

Sorry for late reply! thank you so much, this works

[JeteMc]

Been away for a while and just seeing post #7.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.