 New Contributor

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

5 Replies

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

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

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

Oh my GOODNESS!! yay!!! Thank you!! I will try this!! : ))))

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

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.

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

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