Hi! - asking for a solution to an equation that has me stumped, if I may...

New Contributor

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!!      

 

 

5 Replies

@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))))

 

 

Harun24HR_0-1661998623764.png

 

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)

 

 

 

Oh my GOODNESS!! yay!!! Thank you!! I will try this!! : ))))
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.

@Tamara9927 

 

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:

@Patrick2788 

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!!!