Compare Dollar Amounts and Names

[tizdall]

Hello!

I have a scenario I am trying to solve and I hope this forum can help me.

I have 2 spreadsheets with data that I need to compare and flag if a certain fact is found.

There would be a table of information on each that looks like this:

Number	Name	Amount
1	Louis CK	$100,000
2	Sandler Adam	$50,000
3	Rock Chris	$40,000
4	Carrey Jim	$25,000
5	Carrey Jim	$56,000
6	Sandler Adam	$23,000
7	Jocey Steve	$43,000
8	Woods Tiger	$12,000
9	Rock Chris	$15,000
10	Rock Chris	$45,000

<tbody>
</tbody>

The second spreadsheet may not contain the same number of rows as the first. If money is owed, then the row will appear in the second spreadsheet. If not then it will be absent.

I want to check to see whether the sum of the dollar amounts corresponding to each name on one spreadsheet is greater or equal to the sum of the dollar amounts corresponding to each name on the other spreadsheet. If this proves false, i'd like the font in the offending rows to turn bold and red.

Is there a way to do this using a macro or VBA script? If so, what would it look like?

Any help would be greatly appreciated!


Mark

 

[venkat1926]

you should have given the second sheet also
you use the term one sheet and other sheet . this is vague.
i assusme first sheet is souce sheet and sheet 2 result
what is false is not clear
my second sheeet is as follows copy this in sheet2 from a1

Excel Workbook
	A	B	C
1	Number	Name	Amount
2	1	Louis CK	$100,000
3	2	Sandler Adam	$50,000
4	3	Rock Chris	$40,000
5	4	Carrey Jim	$20,000
6	7	Jocey Steve	$43,000
7	8	Woods Tiger	$15,000
8	9	Rock Chris	$15,000
9	10	Rock Chris	$45,000
Sheet2

now try this macro. carefully go through the codes one by one and see you agree with the logic OF THE MACRO. if not modify macro suitably

Sub test()
Dim nname As Range, cname As Range, strname As String, amt As Long, findname As Range
With Worksheets("sheet1")
Set nname = Range(.Range("b2"), .Range("b2").End(xlDown))
For Each cname In nname
strname = cname.Value
amt = cname.Offset(0, 1).Value
With Worksheets("sheet2")
Set findname = .Columns("B:B").Cells.Find(what:=strname, lookat:=xlWhole)
If findname Is Nothing Then
With Worksheets("sheeet1")
cname.Font.Bold = True
cname.Interior.ColorIndex = 3
End With
ElseIf findname.Offset(0, 1) < amt Then
With Worksheets("sheet1")


cname.Font.Bold = True
cname.Interior.ColorIndex = 3
End With


End If
End With
Next cname
End With


End Sub

 

[venkat1926]

sorry not fully urderstod your problem AS IT IS THE MACRO IS WORNG. first get in another sheest sum of amt for each name and then modify the macro
i MEAN TO SAY USE THE LOGIC IN THE MACRO TO GET YOUR SOLUTION

 

[tizdall]

Hi Venkat,

Sorry about the second sheet, I will try to make myself more clear:

This is the table on the first sheet:

Number	Name	Amount
1	Louis CK	$100,000
2	Sandler Adam	$50,000
3	Rock Chris	$40,000
4	Carrey Jim	$25,000
5	Carrey Jim	$56,000
6	Sandler Adam	$23,000
7	Jocey Steve	$43,000
8	Woods Tiger	$12,000
9	Rock Chris	$15,000
10	Rock Chris	$45,000

<tbody>
</tbody>

Here is what the second sheet might Look like:

Number	Name	Amount
1	Louis CK	$90,000
2	Sandler Adam	$73,000
3	Rock Chris	$60,000
4	Carrey Jim	$24,000
5	Carrey Jim	$57,000
6	Sandler Adam	$23,000
7	Jocey Steve	$43,000
8	Louis CK	$11,000

<tbody>
</tbody>

What I am trying to highlight are instances where the total $ amount for a given name on sheet 2 is greater than the total $ amount on sheet 1. In the example I gave, Louis CK has a greater (combined) amount on sheet 2 than on sheet 1 so his name and dollar amount should be highlighted red and bolded.

I will mess around with the script you gave and see if I can get it to work, any additional advice would be amazing!

Mark

 

[venkat1926]

based on your data in sheet1 and she2 same configuration try this macro and see sheet2

Sub TESTRV()
    Dim r2 As Range, unq2 As Range, cunq2 As Range, amt2 As Range, ssum2
    Dim r1 As Range, unq1 As Range, cunq1 As Range, ssum1, amt1 As Range
    Dim nname As String, myformula2, myformula1, addname2 As String, addamt2 As String
    With Worksheets("sheet2")


        Set r2 = Range(.Range("B1"), .Range("B1").End(xlDown))
        Set amt2 = r2.Offset(0, 1).Offset(1, 0)
        Set unq2 = .Range("A1").End(xlDown).Offset(5, 0)
        Range(unq2, unq2.End(xlDown)).Cells.Clear
        r2.AdvancedFilter xlFilterCopy, , unq2, True
        Set unq2 = Range(unq2.Offset(1, 0), unq2.End(xlDown))
        For Each cunq2 In unq2
            nname = cunq2.Value
            'msgbox nname


            myformula2 = "=sumproduct((" & r2.Offset(1, 0).Address & "=" & """" & nname & """" & ")*" & amt2.Address & ")"
            ssum2 = Worksheets("SHEET2").Evaluate(myformula2)
            'msgbox ssum2
            With Worksheets("sheet1")
                Set r1 = Range(.Range("B2"), .Range("B2").End(xlDown))
                Set amt1 = r1.Offset(0, 1)
                'msgbox nname
            End With
            With Worksheets("sheet1")
                myformula1 = "=sumproduct((" & r1.Address & "=" & """" & nname & """" & ")*" & amt1.Address & ")"


                ssum1 = Worksheets("sheet1").Evaluate(myformula1)
                'msgbox ssum1
            End With
            If ssum2 >= ssum1 Then
                With cunq2
                    .Font.Bold = True
                    .Interior.ColorIndex = 3
                End With
            End If
nnext:
        Next cunq2
        MsgBox "macro over)"
    End With




End Sub