May 02 2022 08:52 AM
I have two sheets that I need to compare from each other. On sheet 1, in column CR, I have a column of amounts, that I need to verify if all are listed, compare to Sheet 2, columns, S-AC. there are duplicate amounts listed, so I need to make sure if for example, $15.00 is listed 4 times on sheet 1, then on sheet 2 $15.00 is listed four times. I am just wanting a true or false response.
I was using the following VBA Script, but what I found is that it is not counting how many are listed. So in column CR of sheet 1 I have $15.00 listed 4 times, however in sheet 2, $15.00 is only showing listed 2 times in columns S-AC.
VBA Script using:
Sub ERROR_REVIEW()
ERROR_REVIEW Macro
ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet3!S:AC, Sheet4!CR)>0"
Range("CS1"). Select
Selection.AutoFill Destination: =Range("CS1:CS100")
Range ("CS1:CS100").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula:="FALSE"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End with
Selection.FormatConditions(1).StopIfTrue = False
Range ("CT1").Select
End Sub
May 02 2022 09:22 AM
Perhaps change
ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet3!S:AC, Sheet4!CR)>0"
to
ActiveCell.Formula = "=COUNTIF(Sheet3!S:AC,CR1)=COUNTIF(CR:CR,CR1)"
May 02 2022 10:16 AM
May 02 2022 11:01 AM
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
May 02 2022 12:07 PM
@Hans VogelaarI have attached a sample workbook. I need to verify that all the amounts from sheet 2 are on sheet 1.
May 02 2022 12:50 PM
Your request in the first post was "I need to make sure if for example, $15.00 is listed 4 times on sheet 1, then on sheet 2 $15.00 is listed four times."
That suggests creating a rule for column A on Sheet1.
Now you write "I need to verify that all the amounts from sheet 2 are on sheet 1."
That suggests creating a rule for Sheet2.
Which is it?
May 02 2022 01:17 PM
May 02 2022 01:27 PM
I can't say that's entirely clear to me, but if I understand you correctly, it's the opposite of what you originally asked.
I created a conditional formatting rule for Sheet2. See the attached version.
May 03 2022 08:27 AM
I need a formula that will take a list of values from one sheet, and compare them to a different sheet. Sheet 1 has column A with amounts and then column B with type of charge. Sheet 2 has the type of charges going across in a row - travel, training, meals, etc, and then amounts going down in each column. I need a formula to verify that sheet 1 has all of the information from sheet 2.
May 03 2022 08:42 AM
Please attach a sample workbook.
May 03 2022 08:57 AM
Attached
May 03 2022 02:44 PM
See the attached version. You'll see that the entire column B is highlighted. This is because the values in column B are text instead of numbers.
And most of column M is highlighted. This is because the category Ttl_Fee does not occur on Sheet1.
May 04 2022 05:27 AM
May 04 2022 05:38 AM
It's conditional formatting of type 'Use a formula to determine which cells to format'.
Select A2 on the second sheet, then click Conditional Formatting > Manage Rules... > Edit Rule to see the formula that I used.