Find a String and add up corresponding numeric valuse accross different worksheets.

[Nizamuddin]

Hi,

I have a workbook with hundreds of worksheets, with data in the first 5 coloumns the first 3 coloumns have string with ID, Name, and Description and the rest 2 coloumns have integer numbers.
I would like to have a summary sheet with the list of few Names for which I need to find the total sum of corresponding values in every sheet present in the workbook.
I want to pick up the first name in the summary sheet find it in Col 2 and when found add up the total of col 4 next to the name in the summary sheet. and similarly the sum of col 5 in the next cell of the summary sheet and loop thru all the worksheets present, similarly continue doing for the next name until a blank cell is reached.
The number of sheets in the workbook may vary and so can the items to be searched for... hence all of this needs to be dynamic.

Any help with a code that can do this task would be greatly appreciated !

Thanks in advance.

 

[Anthony47]

Not so bad for your first topic...
You may try this macro:

Sub nizam()
SumSh = "Summary" '<<<Name of the summary sheet
Sheets(SumSh).Activate
If ActiveSheet.Index > 1 Then
MsgBox ("Please move Summary sheet in position 1, then retry")
Exit Sub
End If
For N = 1 To 1000   '<<< Max 1000 names to search
Sheets(SumSh).Activate
LName = Range("A1").Offset(N, 0).Value
If LName = "" Then
MsgBox ("Completed, maybe"): Exit Sub
End If
RwName = Range("A1").Offset(N, 0).Row
  For Sh = 2 To ThisWorkbook.Worksheets.Count
  Sheets(Sh).Activate
   For Rw = 0 To Cells(Rows.Count, 2).End(xlUp).Row 'Test col 2 to count how many lines in sheet
If Range("B1").Offset(Rw, 0).Value = LName Then
Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(I, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(I, 3).Value
End If
   Next Rw
  Next Sh
Next N
End Sub

From excel, open the vba editor via Alt-F11, the Menu /Insert /Module; copy the macro and paste it into the white frame of vba editor.
Review the lines marked "<<<" to cope with your environment.
Return to excel.
Put a Summary sheet as sheet number 1 in your workbook, with the list of names from A2 down (use line 1 for the headers); the first blank cell means "end of list".
The macro should read the name on Summary, then scan all the rows of the sheets, and if the "name" corresponds to the value in Col. B then it will get Col. D and E and sum these values to Summary's col. B and C.

My testing was fair, but my testing bed is limited... so before any test create 2 backup copies of your data!


Let us know.

 

[Nizamuddin]

Thank you for your help Anthony...
But I got a compile Error unfortunately...

It read : Compile Error : End If with out Block If....

 

[Anthony47]

The top of the macro should have been:
Sub nizam()
SumSh = "Summary" '<<<Name of the summary sheet
Sheets(SumSh).Activate
If ActiveSheet.Index > 1 Then
MsgBox ("Please move Summary sheet in position 1, then retry")
Exit Sub

I don't know what happened before.

Try again, bye.

 

[Nizamuddin]

I got rid of that compile erro and the macro seems to be working fine as coded...
but dint get the desired results due to my bad...

what changes will I have to do if the Name coloumn is C instead of B as mentioned before...
So its.... : Date, ID, NAME, Description, Num1, Num2

Thanks for your help....

 

[Anthony47]

Modify this: If Range("B1").Offset(Rw, 0).Value = LName Then
to this:

If Range("C1").Offset(Rw, 0).Value = LName Then

bye.

 

[Nizamuddin]

Thanks...

Here's the final code that i'm using..
Sub nizam()
SumSh = "Summary" '<< Sheets(SumSh).Activate
If ActiveSheet.Index > 1 Then
MsgBox ("Please move Summary sheet in position 1, then retry")
Exit Sub
End If
For N = 1 To 1000 '<<< Max 1000 names to search
Sheets(SumSh).Activate
LName = Range("A1").Offset(N, 0).Value
If LName = "" Then
MsgBox ("Completed, maybe"): Exit Sub
End If
RwName = Range("A1").Offset(N, 0).Row
For Sh = 2 To ThisWorkbook.Worksheets.Count
Sheets(Sh).Activate
For Rw = 0 To Cells(Rows.Count, 2).End(xlUp).Row 'Test col 2 to count how many lines in sheet
If Range("C1").Offset(Rw, 0).Value = LName Then
Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(I, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(I, 3).Value
End If
Next Rw
Next Sh
Next N
End Sub

My data looks something like this

Date ID Name Desc Count1 Count2
06/10 001 LDP xxxx 5 10
06/10 002 RDE xxxx 2 8

When I execute I get the Results as

Name
RDE Count1Count1Count1 Count2Count2Count2


( considering RDE was fount 3 times its displaying the coloumn heading 3 times in the summary sheet next to the name that was entered to find )
Also, when I execute the macro again it amends the result set instead of clearing what was in there already... so it displays Count1 6 times and Count2 6 times....

Thanks...

 

[Anthony47]

This means that Count1 and Count2 are currently formatted as text.
Try modifying this
Sheets(Sh).Activate

to this:

  Sheets(Sh).Activate
  Columns("D:E").Select
  Selection.NumberFormat = "0.00"

Bye.

 

[Nizamuddin]

I tried this code ( Actullay "E:F" in my case ) and also manually changed the format of the coloumns to number...
Sitll getting the same out put with the colounm headings instead of the total sum...

Thanks,
Nizam.

 

[Anthony47]

Oh my God!
I misinterpreted your point, and now realize what you mean, that require a correction to the macro as follows.
Wrong:
Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(I, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(I, 3).Value
Right:

Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(Rw, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(Rw, 3).Value

Hope now it's ok...
Bye.