If value is true, identify if value is also true in the following columns

[SamyHH]

Hi Community,

I have a question: I would like to test if ID A has a "1" in one of the data columns (Typ A 2016 etc), is the the value in the following colums (years) also 1 or 0. Any ideas on how I could perform that analysis?

Thanks a lot in advance!

[BrandonB]

What about transposing the data and then using a multi-row formula to compare one to the next? See below as an example. Workflow also attached.

 

[SamyHH]

Thank you BrandonB. that already helps but I only want to check if the value is the same as in the previous column if there is 1 in the previous column. (Don't want two zeros to give me back a true value) I believe I have to use a filter before?

[atcodedog05]

Hi @SamyHH 

Here is a modification workflow of @BrandonB to meet the requirement for not considering 0's

Is this what you are looking for. If not give me more details so that i can help you out.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[SamyHH]

Thank you that makes sense. I will give you a little more details of what I want to do:

1. Assign the different IDs to according to their type (the IDs have either Type A positive (value 1) or Type B).  I guess I have to define a range for both Type A and B to say: Type A 2016, Type A 2017, Type A 2018, Type A 2019, Type A 2020 are all "Type A" and do the same for "Type B" -> then assign all IDs to either group Type A or Type B

2. Then I want to apply the previous mentioned multi-row formula to identify if a specific ID has a positive value (1) in the following columns or if this positive values only happens once. I would like to check if they have a positive value (1) in the following two years after appearance of the positive value (1)

Does this make sense to you?

[atcodedog05]

@SamyHH 

Really sorry its still unclear😅 Can you please provide sample data and expected output

We will try to calibrate the workflow accordingly🙂

[SamyHH]

I added a screenshot of the sample data and of my desired output (with an example of the counting for Typ Z)

[atcodedog05]

Hi @SamyHH 

Can you share the question pic as a excel file. I will be able to help you out.

[SamyHH]

v