CODEWITHSUNDEEP
excelexcel-formula
user1079617
user1079617

Reputation: 11

Grouping by an ID, to find if there are variations in another column

So I have a spreadsheet with products listed. There is a product ID which represents different colors and sizes of the product. There is also a column for price.

I want to ensure that all the products with the same ID have the same price and there is no variation.

How do I group all cells with the same ID, and then run a check to ensure all those IDss have the same value in the price column?

I would prefer not to use VBA, if possible.

Upvotes: 0

Views: 4915

Answers (2)

devuxer
devuxer

Reputation: 42384

Given this example spreadsheet:

Spreadsheet containing three columns with headings Product ID, Price, and Valid

  1. Sort by Product ID
  2. Enter this formula into C1: =OR(A2<>A1,B2=B1)
  3. Drag the formula down to C8
  4. All the items with FALSE indicate values where the Product ID matches the Product ID above but the price doesn't match the price above.

The formula returns TRUE if either the Product ID of the current row is different than the Product ID of the row above (which means the prices don't need to match) or the price of the current row is the same as the price of the previous row (if the prices match, it can't be invalid). Otherwise, it returns FALSE.

Upvotes: 3

dosdel
dosdel

Reputation: 1138

Depending on the size of your dataset and how often you want to do this, you might manage to do this manually by using Excel's "Filter" capability. This is a basic solution as I am not sure of your needs or experience:

  • Click anywhere in your dataset

  • Under the "Data" tab, click Filter. Your column names should have a dropdown button on them.

  • Click the dropdown button on your ID field, and only select the ID you want to evaluate.

  • Click the dropdown button on your price field, if there are any different values showing then they are not all the same.

Upvotes: 1

Related Questions