Filtering text data into n subsets then summing

[mbbx5va2]

Hi

Suppose in sheet1 in column A I have integervalues in the cells until row n and column B contains surnames of individualsagain with n rows. So I have a 2 by n matrix consisting of numbers and text. NowI have a situation where there are multiple duplicated surnames so that I mayget Smith 5 times or Jones twice for instance in column B.

Now I want to go through column B and find allthe groups of duplicate surnames and then add the corresponding integer incolumn A. So if there are two instances of Jones each giving 35 and 45respectively then I would like the number 80 to appear in sheet2 cell A1together with the surname Jones in cell B1.

So the end result is another matrix in sheet2but with unique surnames and no duplicates in column B. I would like all ofthis done using a macro button in sheet1.

Now if I wanted to do this in Excel I would use the sort function to get all the surnames grouped together then one would create a vlookup for each unique surname but this is too time consuming. I don't want to spend time writing a different surname within the first argument of the vlookup.
Initially I am thinking of defining a 2 dimensional array and assigning it to the data set. Next I am thinking of a for next loop from x =1 To y which tells me the position each time a specific surname appears in column B. Now the Match function e.g
Match(x,"B:B",0) tells me the position of one but not all occurrences. If I know the position of all occurrences then I suppose I can just sum a set of references to the array.

Any thoughts much appreciated? :)

[p45cal]

Pivot table, see attached, less than 30 seconds to create.

[mbbx5va2]

Thanks for the reply. Whilst this gives me the required figures for each surname one issue is that the summed output data calculated in the pivot table isn't written out in full within the cells. Usually this is fine but I want to then loop through rows of data for another mailmerge procedure so ideally I need the summed output data in two columns and 5 rows like in the attached file – see my second attachment. But if I have a pivot table then is it possible to loop through the different possibilities somehow to do my mailmerge? Of course with only 5 rows I can do it manually copying the results of the different queries from the pivot table into the spreadsheet but the spreadsheet I'm working on has 900 rows and the data here is a simplified version. :)

[p45cal]

In the last file I attached I left only one name showing to mimic what you had in that sheet. You only have to clear the filter in the drop down to see all results.
Yes, you can then use vba to run through all the results.
I probably won't get time until tomorrow to give you some sample code.

[mbbx5va2]

Ok thanks I will have a proper look a little later.

[apo]

Hi.. try this.



Private Sub CommandButton1_Click()
Dim Z, x, i As Long
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
Z = Range("A1").CurrentRegion

For i = LBound(Z) To UBound(Z)
If Z(i, 2) <> "" Then .Item(Z(i, 2)) = .Item(Z(i, 2)) + Z(i, 1)
Next i

x = Application.Transpose(Array(.keys, .items))

With Range("D1").Resize(UBound(.keys) + 1, 2)
.Value = x
.Sort .Cells(1, 1), 1
End With
End With
End Sub

[mbbx5va2]

Many thanks I will have a go using this a little later.

[p45cal]

I probably won't get time until tomorrow to give you some sample code.The attached is just apo's file with an extra button with code behind it that uses the Advanced Filter to obtain unique entries onto a new sheet and a SumIf formula to get the sums (later converted to hard values).

[mbbx5va2]

Hi.. try this.



Private Sub CommandButton1_Click()
Dim Z, x, i As Long
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
Z = Range("A1").CurrentRegion

For i = LBound(Z) To UBound(Z)
If Z(i, 2) <> "" Then .Item(Z(i, 2)) = .Item(Z(i, 2)) + Z(i, 1)
Next i

x = Application.Transpose(Array(.keys, .items))

With Range("D1").Resize(UBound(.keys) + 1, 2)
.Value = x
.Sort .Cells(1, 1), 1
End With
End With
End Sub

Hi this works perfectly. Thank you. I knew that I needed to use Arrays and a loop of some sort but wasn't sure how to put it all together. The choice of object sorts this out. Hmmm for the if statement your saying that if the text column is non empty then each cell of column 2 becomes each text item e.g jones plus the corresponding integer. I would not have thought about transposing an array with dimensions keys, item.
So each key is a unique subset of the surnames and each item is every row of column 2? Also resizing the rows to match number of subsets is neat too.

[apo]

Hi..


So each key is a unique subset of the surnames and each item is every row of column 2?

Kinda.. There are others here that could explain it better.. I am still trying to get my head around it all... put it this way.. I spend a lot of time looking at my Locals window.. studying why/how/what about the variables involved..:)

Take a look at snb's website...

Oops.. I can't post url's yet.. :)

Did you try p45cals?.. it works nicely too..

[mbbx5va2]

The attached is just apo's file with an extra button with code behind it that uses the Advanced Filter to obtain unique entries onto a new sheet and a SumIf formula to get the sums (later converted to hard values).

Thanks for this p45cal. I am having a look at the additional code now!

[mbbx5va2]

Hi..



Kinda.. There are others here that could explain it better.. I am still trying to get my head around it all... put it this way.. I spend a lot of time looking at my Locals window.. studying why/how/what about the variables involved..:)

Take a look at snb's website...

Oops.. I can't post url's yet.. :)

Did you try p45cals?.. it works nicely too..

Yeah I've tried it just now. I will have a look at snb's site if I can find it.

[snb]

My signature might help....