Forum Discussion
Hi! - asking for a solution to an equation that has me stumped, if I may...
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!! 🙂
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)- Thank you again for this.... it works wonderfully!!! I am now wondering if there is a way to make this equation adaptable to work with different numbers of rows, without having to change the ranges to fit the number of rows each time? How can I make the cell references of $A$8 and $B$8 just automatically match how ever many rows I cut and paste into the spreadsheet?
I tried using Index and that didn't seem to work...I tried using a COUNTA function to determine the number of rows and then use the VALUE of that cell as a quantity reference inside your equation, and THAT didn't work... so once again, I am stumped. Any further ideas? : )
I'm using Excel 2019 Home and Student.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:
- Oh my GOODNESS!! yay!!! Thank you!! I will try this!! : ))))
