Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper 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.






 BYROW dynamic array approach. See the attachment.








And if you do not have Office365 then could use below formula for all versions of excel.






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.



Keeping the SUMPRODUCT solution, you could create two dynamic ranges to put in the formula:









 Please see attached:


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