Aug 31 2022 05:38 PM - edited Aug 31 2022 05:41 PM
I am a bit further along than beginner, but by no means advanced, so please forgive me if my query seems obvious to someone. But I have tried to build an equation to solve the following problem, and I'm not getting anywhere. Thank you to all in advance!
The problem I am trying to solve, is that for all instances of a particular number in one column, I want to compare their corresponding associated values in a different column with each other, to see if they are the same. I will explain this a little differently, for nuance:
In a regular Excel spreadsheet I have rows of data, and each one has data points in columns A and B. In each cell in column A, there is a six digit number. Within column A, some of these numbers repeat, but not all. All of the same numbers are right next to each other in column A, because I ordered them (and their respective rows) by their number values, low to high.
In column B, there is a text string, either "TRUE" or "FALSE".
What I am trying to do, for all instances of a particular number in column A, is to compare their corresponding values in the same rows in column B with each other, to see if ALL of the corresponding values in the B column are saying "TRUE".
So in the following example, taking the first number, 133235 (A2), there are three examples of that in column A: A2, A3, and A4. So what I want to do, is to compare B2, B3, and B4 with each other, to see if B2, B3, and B4 all have the value as "True". If ALL THREE B values are "True", then I want to put the value "TRUE" into C2, C3, and C4 (which is messy, but only because I will be putting this same formula into every cell in the C column). I've supplied several result examples for you based on the data below, so you can see what I am looking for:
Column A Column B Column C
133235 True False
133235 False False
133235 False False
182374 True True
171532 False False
922161 True True
922161 True True
If even one of the corrseponding values in column B is false, column C needs to return a value of false. For all of the rows in column A that are the same number, only if all of the corresponding values in column B are True, should the value in column C return as "True".
I hope that makes sense. I'm having quite a difficult time with this. Thank you all so much!!
Aug 31 2022 07:09 PM - edited Aug 31 2022 07:17 PM
@Tamara9927 With Microsoft-365 you can try below formulas. BYROW will give you result without entering it to each cell. It will spill all the results automatically.
=AND(FILTER($B$1:$B$7,$A$1:$A$7=A1))
BYROW dynamic array approach. See the attachment.
=BYROW(A1:A7,LAMBDA(x,AND(FILTER($B$1:$B$7,$A$1:$A$7=x))))
And if you do not have Office365 then could use below formula for all versions of excel.
=IF(SUMPRODUCT(--($A$2:$A$8=A2)*(1/COUNTIFS($A$2:$A$8,$A$2:$A$8,$B$2:$B$8,$B$2:$B$8)))=1,B2,FALSE)
Aug 31 2022 07:40 PM
Oct 03 2022 08:14 PM
Oct 05 2022 11:47 AM - edited Oct 05 2022 11:48 AM
Keeping the SUMPRODUCT solution, you could create two dynamic ranges to put in the formula:
"DynamicID"
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000000),1)
"DynamicTF"
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$A$2:$A$1000000),1)
Please see attached:
Oct 05 2022 05:02 PM
Ohmygoodness. You did it AGAIN!!!!!!!!!!!!!!!
I cannot thank you enough!! I can see now that I was apparently on the right track, but I definitely did it incorrectly. Thank you for teaching me!!! You're the best!!!! : )
Tamara <3!!!