Solved: vba to ID info in cells and find other cells with same info

[mr, whipple]

I was handed a real mess and i think frustration has taken hold.

I have a spreadsheet with a column of case numbers. The next column contains dollar amounts associated with the case number. Case numbers have been entered more than once in almost all cases as more money was issued. I am not allowed to arrange the data in a more usable form, thus most of my frustration.

I created a unique list of case numbers to use in a search. What I need is a MACRO that will look at the first number in that unique list, locate the same number in the case number column and then gather up all of the money associated with that case number in a single cell. Then it needs to take the second number on the unique list and do it all again until all numbers in the unique list have been considered.

[austenr]

No need for VBA here. Subtotals to the rescue. Try this:

1. Select the columns with the Case numbers and dollar amounts
2. Sort the columns on the case number
3. Make sure you have a column header for the case number and amounts
4. Find the Subtotal button its in a different place depending on version.
5. Select what you want the subtotals on and click ok.

[Bob Phillips]

Or use SUMIF

=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

and copy down

[mr, whipple]

No need for VBA here. Subtotals to the rescue. Try this:

1. Select the columns with the Case numbers and dollar amounts
2. Sort the columns on the case number
3. Make sure you have a column header for the case number and amounts
4. Find the Subtotal button its in a different place depending on version.
5. Select what you want the subtotals on and click ok.

Actually, there is a very great need for a MACRO. I am not going to be the one manipulating that data, my boss will be. He is a typical boss. I seriously need a macro that will do the job correctly and with very little interaction between my boss and the file.

thanks anyway.

[mr, whipple]

Or use SUMIF

=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

and copy down

that doesn't seem to work.

[Bob Phillips]

that doesn't seem to work.

Care to share a bit more with us?

[mr, whipple]

Care to share a bit more with us?

I am supposed to put this together for my boss. He will be the one that will use the file and make the updates. I have no idea why he will not let me do it because it is a simple process if you do it manually.

He is not at all versed in anything beyond the basics of Excel. What I will ultimately do is take the code and assign it to a button that he can click and had the date updated on sheet1 of the file.

[austenr]

I think its time to see an example workbook

[Bob Phillips]

I am supposed to put this together for my boss. He will be the one that will use the file and make the updates. I have no idea why he will not let me do it because it is a simple process if you do it manually.

He is not at all versed in anything beyond the basics of Excel. What I will ultimately do is take the code and assign it to a button that he can click and had the date updated on sheet1 of the file.

That is very interesting, but I meant tell us in what way it doesn't work, what do you get, what do you expect/want.

[mr, whipple]

That is very interesting, but I meant tell us in what way it doesn't work, what do you get, what do you expect/want.

It doesn't work because it is not automated.

I am toiling under a very weird setup and the solution needs to be push button fast and fool proof. My boss knows nothing of Excel and HIS boss expects to walk into my boss's office and get instant answers. It is basically me working for Mr. Bean who is working for Stalin. Very funny when I am not involved.

[mr, whipple]

I think its time to see an example workbook

The actual workbook is at work and (thank God) I am not. The data is imported from some accounting type software. It simple takes the new data and adds it row by row to the spreadsheet. One column contains all of the case numbers (the only constant in any row) and two columns over has the amount that was paid on that run of checks. Case numbers get multiple payments over the course of time.

I was thinking that the best was to accomplish this is to copy the case numbers to another location and eliminate dupes (I've done that). Then using that data, locate the other cells in the case number column that are the same and pull the dollar amount from the cell two columns over.

Maybe I am over complicating things, but I need a solution that is fast and automated.

[Bob Phillips]

SUMIF is fast, and has the advantage that the user doesn't need to do anything.

[mr, whipple]

SUMIF is fast, and has the advantage that the user doesn't need to do anything.

I agree that it is workable with a static worksheet. The one I have changes all the time. Yes, you can continue to add lines, but my boss is not going to do that because it takes time.

[GTO]

Greetings,

If I understand correctly...

• We have a bunch of case numbers in one column.
• Some are listed once, some a few times, etc.
• This list continues to grow.
• We cannot sort the list, it is probably added to as billings or outlays or whatever occur.
• We'd like to be able to beat the boss stupid, but in lieu of that, we'd settle for pushing a button and a listing of all cases and ea case's current total monies should be produced.
• We'd still like to smack the boss around though, if only those pesky bills didn't keep showing up in the mail.

Would this work?

In a Standard Module:

Option Explicit
    
Sub exa()
Dim _
rngData         As Range, _
i               As Long, _
aryCollection   As Variant
    
    '// set a reference to the range containing Case Numbers.  Change sheetname,    //
    '// column, and first cell (if no header row) to suit.                          //
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngData = Range(.Range("A2"), _
                            RangeFound(.Range(.Range("A2"), .Cells(Rows.Count, 1))))
    End With
    
    '// Return an array of unique case numbers.                                     //
    aryCollection = RetCollection(rngData)
    
    '// SAA, change dest sheet, col, start row to suit.                             //
    With ThisWorkbook.Worksheets("Sheet2") _
            .Range("A2").Resize(UBound(aryCollection) - LBound(aryCollection) + 1)
        
        '// Plunk the array of case numbers in.                                     //
        .Value = Application.Transpose(aryCollection)
        '// Plunk in the SUMIF formula...                                           //
        .Offset(, 1).Formula = _
            "=SUMIF(" & rngData.Parent.Name & "!" & rngData.Address(True, True) & _
                    ",A2," & rngData.Parent.Name & "!" & _
                    rngData.Offset(, 2).Address(True, True) & ")"
        '// ...and optionally, overwrite it.                                        //
        .Offset(, 1).Value = .Offset(, 1).Value
        
        '// If your boss changes sheetnames, beat him/her stupid.                   //
    End With
End Sub
    
Function RetCollection(DataRange As Range) As Variant
Dim CaseNo As Variant
    
    With CreateObject("Scripting.Dictionary")
        For Each CaseNo In DataRange.Value
            .Item(CaseNo) = CaseNo
        Next
        RetCollection = .Items
    End With
End Function
    
Function RangeFound(SearchRange As Range, _
                    Optional FindWhat As String = "*", _
                    Optional StartingAfter As Range, _
                    Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
                    Optional LookAtWholeOrPart As XlLookAt = xlPart, _
                    Optional SearchRowCol As XlSearchOrder = xlByRows, _
                    Optional SearchUpDn As XlSearchDirection = xlPrevious, _
                    Optional bMatchCase As Boolean = False) As Range
    
    If StartingAfter Is Nothing Then
        Set StartingAfter = SearchRange(1)
    End If
    
    Set RangeFound = SearchRange.Find(What:=FindWhat, _
                                      After:=StartingAfter, _
                                      LookIn:=LookAtTextOrFormula, _
                                      LookAt:=LookAtWholeOrPart, _
                                      SearchOrder:=SearchRowCol, _
                                      SearchDirection:=SearchUpDn, _
                                      MatchCase:=bMatchCase)
End Function

Hope that helps,

Mark

[Bob Phillips]

I agree that it is workable with a static worksheet. The one I have changes all the time. Yes, you can continue to add lines, but my boss is not going to do that because it takes time.

You can have dynamic named ranges that will cater for spreadsheet expansion.

[GTO]

mr whipple,

I hope you won't mind the intrusion.

Hi Bob,

Hope it is a lovely Saturday afternoon there. Still morning here of course, but very nice for June, as its usually a lot hotter by now (a nice cool down for thie weekend).

Anyways, I get the part about a dynamic range to check against, but how would we make dynamic the number of values to return, as additional case numbers are added?

Thank you,

Mark

[Bob Phillips]

You add a count in the range name formula of the rows with data, like

=OFFSET($A$1,,,COUNTA($A:$A),1)

then as rows of data are added, the count increases, so the dynamic range name refers to more data.

[mr, whipple]

Greetings,

If I understand correctly...

• We have a bunch of case numbers in one column.
• Some are listed once, some a few times, etc.
• This list continues to grow.
• We cannot sort the list, it is probably added to as billings or outlays or whatever occur.
• We'd like to be able to beat the boss stupid, but in lieu of that, we'd settle for pushing a button and a listing of all cases and ea case's current total monies should be produced.
• We'd still like to smack the boss around though, if only those pesky bills didn't keep showing up in the mail.

Would this work?

That works perfectly. Thank you very much. You also have a strong grasp of my situation with my boss. Just in case, I removed the remarks in the code about beating him stupid. Somebody already did that long ago.


THANKS AGAIN!
Mark

[mr, whipple]

thank you to all of the people who offered advice on this problem. I appreciate it all very much!