Highlight rows where the same values in columns A:B but different values in column C
I have 4000+ rows of data need to be working on. Where column A represents the SKU, column B represents the unit and column C represents the Unit Price. The same SKU, Unit and Unit Price may duplicate at their own columns as shown as the picture attached. I need to check and highlight whether which SKU, appears to have different Unit Price but with same Unit. Which mean same SKU (Column A), same Unit (Column B) but different in Unit Price (Column C).
Is there any possible method or formula for doing this checking instead of checking row-by-row manually?
[
How to I find the same cases just like row 4 in the picture (with same SKU, same Unit but different Unit Price?
You can design a Conditional Formatting rule using a formula based on the COUNTIFS function. If you set a rule such that there must be at least two other rows with the same SKU and Unit combination but with different Unit Prices then a formula can be easily derived.
First, select all of columns A:C then go to Home ► Styles ► Conditional Formatting ► New Rule. Opt for Use a formula to determine which cells to format and supply the following for the Format values where this formula is true: text box.
=AND(ROW(A1)>1, COUNTIFS($A:$A, $A1, $B:$B, $B1, $C:$C, "<>"&$C1)>1)
Click Format and apply some formatting the OK to accept the formatting and OK again to create the new rule. Your results should resemble the following.
Note that I've added one more row of data to the sample data.
- Dynamically create variables in for loop
- openxlsx currency style - Is there a rounding option?
- Last key of a Dictionary Excel VBA
- Leave out quotes when copying from cell
- php generating excel and downloading
- How to generate an .xlsx using php
- VBA if cells between number values then delete row
- Create a new table using multiple columns from a list of data that has repeating values in it
- Lookup multiple criteria
- Pandas Styler.bar() not showing on Excel column
- Indicate when there is no match when comparing Excel data to Access recordset
- Difference between 'on error goto 0' and 'on error goto -1' -- VBA
- How to return the column header when "Yes" is found in a specific row in another table?
- VBA Number of rows based on number
- Speed up nested loop in VBA
- Conditional formatting highlight non matching criteria based on min date
- Is it possible to read the value of the Office Theme option that's currently applied in the Microsoft Excel settings and use it in VBA?
- Return all column headers when there are multiple yes entries from a vlookup
- VBA set a dynamic range based on “unique” IDs in a column
- Character encoding in Excel spreadsheet and what Java charset to use to decode it
- Disable warning about Excel text cell containing numeric value in OfficeOpenXml
- Excel LAMBDA + spill
- Using Excel Lambda function to count numbers that meet a condition (in conjunction with groupby)
- How do I filter results from a list based on previously-filtered results?
- Sort rows based on multiple criteria
- Convert Range to a table
- Problems with Excel handling of the month of March in German language
- Excel IF multiple columns
- Increase row height of Excel cell (merged cell & text wrapped), if cell has multiple lines
- Copy Formula To Multiple Cells Using EPPlus