Formula to find no matches different rows

[Caly]

Hi I am working on a file where there are the same values in one column across different rows and I want to compare those to another column to see if there are different values. How can I do this?

column a will have a list of items some of which will be the same
Column b will have prices
In column c I want to compare the columns to find where If rows in column a match but the values in comlumn b do not match

 

[jasonb75]

Depends what you want shown as the result.

=COUNTIFS(A:A,A2,B:B,"<>"&B2)

Will tell you how many entries that match A2 have a price different to that in B2, but not what those prices are or where they are found.

 

[Caly]

Thank you. I used that formula which helped me to identify the count of items with and without the same value for the same item in the row for column a. I then did a countif against column a to count the number of instances for the item in column a; then I did an equals between the formula in column c and column d to see the variance.

Is there a way to highlight or conditional format a value for the same item in a row for column A where it doesn’t match the values in column b? So like if there are three instances in column A on three different rows where it is for Apple but the price in column B is the same for two rows but not a third row, is there a formula to note there is a difference and show what the minimum value should be?

 

[jasonb75]

the price in column B is the same for two rows but not a third row, is there a formula to note there is a difference and show what the minimum value should be?

That could be interpreted in one of two ways, do you want to highlight the lowest price or the price that occurs less frequently?

Looking at it from a more complex angle, what if you had Apples in 6 rows with 3 different prices (each price appearing in 2 rows)?

Also, could you clarify which version of excel you're using? Newer versions have more functions available, which in turn increases the possibility of being able to find an answer to your question. When you have 4 different versions showing on your profile we don't know which (if any) is the correct one to use.

 

[Caly]

Thank you so much. Attached is the file. I’m trying to highlight which rows are not a match and then also show the lowest price. My excel version is 2016

 

[jasonb75]

Try this one to return the lowest price for each item

=AGGREGATE(15,6,$B$2:$B$8/($A$2:$A$8=A2),1)

then you could use conditional formatting to highlight where the result of this formula is different to the price in column B.

 

[Caly]

This is perfect wow yes both ways work and helped me finish a huge project. I am
Also able to apply this in other files too as a way to check for duplication against the same column across different rows.