Excel - VBA Help - Two Sheets matching amounts

%3CLINGO-SUB%20id%3D%22lingo-sub-3300509%22%20slang%3D%22en-US%22%3EExcel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300509%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20sheets%20that%20I%20need%20to%20compare%20from%20each%20other.%26nbsp%3B%20On%20sheet%201%2C%20in%20column%20CR%2C%20I%20have%20a%20column%20of%20amounts%2C%20that%20I%20need%20to%20verify%20if%20all%20are%20listed%2C%20compare%20to%20Sheet%202%2C%20columns%2C%20S-AC.%20there%20are%20duplicate%20amounts%20listed%2C%20so%20I%20need%20to%20make%20sure%20if%20for%20example%2C%20%2415.00%20is%20listed%204%20times%20on%20sheet%201%2C%20then%20on%20sheet%202%20%2415.00%20is%20listed%20four%20times.%20I%20am%20just%20wanting%20a%20true%20or%20false%20response.%3C%2FP%3E%3CP%3EI%20was%20using%20the%20following%20VBA%20Script%2C%20but%20what%20I%20found%20is%20that%20it%20is%20not%20counting%20how%20many%20are%20listed.%20So%20in%20column%20CR%20of%20sheet%201%20I%20have%20%2415.00%20listed%204%20times%2C%20however%20in%20sheet%202%2C%20%2415.00%20is%20only%20showing%20listed%202%20times%20in%20columns%20S-AC.%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EVBA%20Script%20using%3A%3C%2FP%3E%3CP%3ESub%20ERROR_REVIEW()%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20ERROR_REVIEW%20Macro%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20ActiveCell.FormulaR1C1%20%3D%20%22%3DCOUNTIF(Sheet3!S%3AAC%2C%20Sheet4!CR)%26gt%3B0%22%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Range(%22CS1%22).%20Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Selection.AutoFill%20Destination%3A%20%3DRange(%22CS1%3ACS100%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Range%20(%22CS1%3ACS100%22).Select%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Selection.FormatConditions.Add%20Type%3A%3DxlCellValue%2C%20Operator%3A%3DxlEqual%2C%20_%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Formula%3A%3D%22FALSE%22%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20With%20Selection.FormatConditions(1).Font%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Color%20%3D%20-16383844%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.TintAndShade%20%3D%200%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20End%20With%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20With%20Selection.FormatConditions(1).Interior%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.PatternColorIndex%20%3D%20xlAutomatic%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Color%20%3D%2013551615%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.TintAndShade%20%3D%200%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20End%20with%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Selection.FormatConditions(1).StopIfTrue%20%3D%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20Range%20(%22CT1%22).Select%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3300509%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302295%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20conditional%20formatting%20of%20type%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3ESelect%20A2%20on%20the%20second%20sheet%2C%20then%20click%20Conditional%20Formatting%20%26gt%3B%20Manage%20Rules...%20%26gt%3B%20Edit%20Rule%20to%20see%20the%20formula%20that%20I%20used.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302288%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302288%22%20slang%3D%22en-US%22%3EI%20understand%20on%20the%20Ttl_Fee.%20My%20question%20is%20how%20did%20you%20figure%20it%3F%20Formula%3FVBA%3F%20Conditional%20Formating%3F%20I%20have%20to%20do%20this%20every%20month.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301834%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20You'll%20see%20that%20the%20entire%20column%20B%20is%20highlighted.%20This%20is%20because%20the%20values%20in%20column%20B%20are%20text%20instead%20of%20numbers.%3C%2FP%3E%0A%3CP%3EAnd%20most%20of%20column%20M%20is%20highlighted.%20This%20is%20because%20the%20category%20Ttl_Fee%20does%20not%20occur%20on%20Sheet1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301536%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301513%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301513%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20attach%20a%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301496%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301496%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20that%20will%20take%20a%20list%20of%20values%20from%20one%20sheet%2C%20and%20compare%20them%20to%20a%20different%20sheet.%20Sheet%201%20has%20column%20A%20with%20amounts%20and%20then%20column%20B%20with%20type%20of%20charge.%20Sheet%202%20has%20the%20type%20of%20charges%20going%20across%20in%20a%20row%20-%20travel%2C%20training%2C%20meals%2C%20etc%2C%20and%20then%20amounts%20going%20down%20in%20each%20column.%20I%20need%20a%20formula%20to%20verify%20that%20sheet%201%20has%20all%20of%20the%20information%20from%20sheet%202.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300802%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20can't%20say%20that's%20entirely%20clear%20to%20me%2C%20but%20if%20I%20understand%20you%20correctly%2C%20it's%20the%20opposite%20of%20what%20you%20originally%20asked.%3C%2FP%3E%0A%3CP%3EI%20created%20a%20conditional%20formatting%20rule%20for%20Sheet2.%20See%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300796%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300796%22%20slang%3D%22en-US%22%3EI%20need%20to%20verify%20that%20all%20amounts%20from%20sheet%202%20or%20listed%20on%20sheet%201.%20There%20are%20going%20to%20be%20times%20that%20the%20amounts%20will%20not%20be%20there%2C%20and%20that%20is%20okay.%20I%20had%20it%20as%20a%20TRUE%20or%20FALSE%20output%2C%20so%20the%20FALSE%20once%20I%20will%20review.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300779%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20request%20in%20the%20first%20post%20was%20%22I%20need%20to%20make%20sure%20if%20for%20example%2C%20%2415.00%20is%20listed%204%20times%20on%20sheet%201%2C%20then%20on%20sheet%202%20%2415.00%20is%20listed%20four%20times.%22%3C%2FP%3E%0A%3CP%3EThat%20suggests%20creating%20a%20rule%20for%20column%20A%20on%20Sheet1.%3C%2FP%3E%0A%3CP%3ENow%20you%20write%20%22I%20need%20to%20verify%20that%20all%20the%20amounts%20from%20sheet%202%20are%20on%20sheet%201.%22%3C%2FP%3E%0A%3CP%3EThat%20suggests%20creating%20a%20rule%20for%20Sheet2.%3C%2FP%3E%0A%3CP%3EWhich%20is%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300748%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3EI%20have%20attached%20a%20sample%20workbook.%20I%20need%20to%20verify%20that%20all%20the%20amounts%20from%20sheet%202%20are%20on%20sheet%201.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300690%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300690%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20(without%20sensitive%20data)%2C%20or%20if%20that%20is%20not%20possible%2C%20make%20it%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300634%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300634%22%20slang%3D%22en-US%22%3EThat%20did%20not%20work.%20It%20only%20worked%20for%20some%20of%20the%20cells%2C%20but%20did%20not%20count%20on%20all%20of%20them.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3300539%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20VBA%20Help%20-%20Two%20Sheets%20matching%20amounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3300539%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1340231%22%20target%3D%22_blank%22%3E%40Dfuhrman8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20change%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EActiveCell.FormulaR1C1%20%3D%20%22%3DCOUNTIF(Sheet3!S%3AAC%2C%20Sheet4!CR)%26gt%3B0%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eto%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EActiveCell.Formula%20%3D%20%22%3DCOUNTIF(Sheet3!S%3AAC%2CCR1)%3DCOUNTIF(CR%3ACR%2CCR1)%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

 

13 Replies

@Dfuhrman8 

Perhaps change

 

ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet3!S:AC, Sheet4!CR)>0"

 

to

 

ActiveCell.Formula = "=COUNTIF(Sheet3!S:AC,CR1)=COUNTIF(CR:CR,CR1)"

That did not work. It only worked for some of the cells, but did not count on all of them.

@Dfuhrman8 

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.

@Hans VogelaarI have attached a sample workbook. I need to verify that all the amounts from sheet 2 are on sheet 1.

@Dfuhrman8 

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?

I need to verify that all amounts from sheet 2 or listed on sheet 1. There are going to be times that the amounts will not be there, and that is okay. I had it as a TRUE or FALSE output, so the FALSE once I will review.

@Dfuhrman8 

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.

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.

@Dfuhrman8 

Please attach a sample workbook.

@Hans Vogelaar 

Attached

@Dfuhrman8 

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.

I understand on the Ttl_Fee. My question is how did you figure it? Formula?VBA? Conditional Formating? I have to do this every month.

@Dfuhrman8 

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.