Shyretta Jenkins
asked on
Formula or Macro to determine variance
Attached is a spreadsheet that I use. Normally I would have to manually determine which Item# has different Original Sell$ and highlight them. Is there a formula or macro that can be used to do this for me?
For example: Item# 102356 has 2 different Amount? One is $399 and another is $599. If this is the case I want to have that Item# highlighted to indicate there are multiple amounts for the same Item#.
Test-Variance.xlsx
For example: Item# 102356 has 2 different Amount? One is $399 and another is $599. If this is the case I want to have that Item# highlighted to indicate there are multiple amounts for the same Item#.
Test-Variance.xlsx
ASKER
When I run the conditional formatting, I am coming up with the attached. I am not getting the results that you did.
Please advise
Test-Variance.xlsx
Please advise
Test-Variance.xlsx
What is the issue with that file i.e. how is it not working?
ASKER
This is what I am getting:
Screenshot.docx
Screenshot.docx
You used the wrong reference of the starting cell, see below in bold..
=SUM(--(FREQUENCY(IF($A$2: $A$200=$A3,IF($B$2:$B$200<>"",$B$2:$ B$200)),$B $2:$B$200) >0))>1
While it should be like below...
=SUM(--(FREQUENCY(IF($A$2: $A$200=$A2,IF($B$2:$B$200<>"",$B$2:$ B$200)),$B $2:$B$200) >0))>1
Please refer to the attached.
Test-Variance-2.xlsx
=SUM(--(FREQUENCY(IF($A$2:
While it should be like below...
=SUM(--(FREQUENCY(IF($A$2:
Please refer to the attached.
Test-Variance-2.xlsx
ASKER
Do the 2 columns have to be side by side because if you can see in the snapshot, I am working with column E and G.
In that case you will have to make two rules, one for col. E and another for col. G using the same formula.
If the formula for both rules is the same one rule can apply to multiple ranges. When selecting the range using the Range Browser, select the first range and then press Ctrl key before selecting second range.
ASKER
Guys I can't seem to get this to work. Please help! I have tried both suggestions.
Test-Variance-Formatting.xlsx
Test-Variance-Formatting.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This appears to be a continuation of your previous question which was abandoned.
Are you still using the separate CSV file to determine correct prices?
Are you still using the separate CSV file to determine correct prices?
ASKER
yes
So can you confirm what issues were remaining from the original question.
The file which I uploaded to that question is attached for continuation.
Thanks,
Rob
Sample-Selling--Differes.xlsx
The file which I uploaded to that question is attached for continuation.
Thanks,
Rob
Sample-Selling--Differes.xlsx
ASKER
Thank you so much!
You're welcome Shyretta! Glad to help.
ASKER
Question: Can this conditional formatting be used as a macro?
You should open a new question.
1) Select the range A2:B200.
2) Click on Home --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format.
3) In the formula box, type the formula given below and click on Format to set the format as per your choice and click OK twice to close the conditional formatting window.
Formula for Conditional Formatting:
Open in new window
Please refer to the attached where I have applied the conditional formatting to highlight the target cells with red background and white & bold font.
Test-Variance.xlsx