Please help with summative column (VLOOKUP???)

[bstevens]

We have a big spreadsheet here at work with about 40 columns. One of
them, let's say it's column H, contains the names of some school
districts. Some of them occur more than once in that column. Column I
contains that district's code number. Another column, say column N,
contains an amount for that district. This also appears more than once.
For example:

H I N
Houston 23 150
Dallas 48 400
Austin 15 250
Dallas 48 250
Houston 23 100
Austin 15 350

.... and so on, for over 2000 rows.

What I need is a formula for Columns P, Q and R where P and Q would
list each district and its code number ONE TIME, and R would list the
total amounts for that district. Note: this can NOT be sorted prior to
executing the formla.

EXAMPLE: for the above example, P, Q and R would look like:

P Q R
Houston 23 250
Dallas 48 650
Austin 15 600

I can't figure this one out; I hope it's not terribly difficult. The
actual spreadsheet includes about 80 different districts appearing
anywhere from once to over 100 times. And again, I can't sort it first
- it's too big and complex and sorting would mess it up. This needs to
be just three nice, neat, additional columns. Of course, those columns
could be sorted later, no problem there.

Anybody want to tackle this one? Our administrative assistants would
be mucho grateful.

Thanks,
Ron M.

 

[Brian B.]

This could could be done a ton of different ways but probably the
simpliest would be using the Advanced Filter option to get out 'Unique
Records Only'. Then simply use a SUMIF or an array formula to either
sum or count the necessary results.

Hope this helps ...
-Brian

 

[bstevens]

I don't think I can get that one working, Brian. When I open Advanced
Filters, we'd have to type all 80+ districts in the "filter range" box,
for starters.

Ron M.

 

[Guest]

P2=IF(COUNTIF(H2:$H$2000,H2)=1,H2,""
Q2=IF(ISERROR(VLOKUP(L2,$H$2:$I$2000,2,FALSE)),"",VLOOKUP(L2,$H$2:$I$2000,2,FALSE))
R2=SUMPRODUCT(($H$2:$H$2000=L2)*($J$2:$J$2000))

Insert and copy down 2000 rows

 

[acampbell012]

Ront, Try:

Sub Macro1()
'
' Macro1 Macro
Sub Summary()
Dim Endrow As Long
Dim endrow2 As Long
Endrow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F1:F" & Endrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("P1"), Unique:=True
endrow2 = Cells(Rows.Count, "P").End(xlUp).Row
Range("Q2").Resize(endrow2 - 1, 1).Formula = "=SumIf(F$1:F$" &
Endrow & ", $P2, G$1:G$" & Endrow & ")"
Range("R2").Resize(endrow2 - 1, 1).Formula = "=SumIf(F$1:F$" &
Endrow & ", $P2, H$1:H$" & Endrow & ")"
'Range("P1").Offset(1, 2).Formula = "=SumIf(F1:F" & Endrow & ", P2,
H1:H" & Endrow & ")"
With ActiveSheet
.AutoFilterMode = False
End With
End Sub

Alan

 

[acampbell012]

Ron:

Try this:

Sub Summary()
Dim Endrow As Long
Dim endrow2 As Long
Endrow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F1:F" & Endrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("P1"), Unique:=True
endrow2 = Cells(Rows.Count, "P").End(xlUp).Row
Range("Q2").Resize(endrow2 - 1, 1).Formula = "=SumIf(F$1:F$" &
Endrow & ", $P2, G$1:G$" & Endrow & ")"
Range("R2").Resize(endrow2 - 1, 1).Formula = "=SumIf(F$1:F$" &
Endrow & ", $P2, H$1:H$" & Endrow & ")"
'Range("P1").Offset(1, 2).Formula = "=SumIf(F1:F" & Endrow & ", P2,
H1:H" & Endrow & ")"
With ActiveSheet
.AutoFilterMode = False
End With
End Sub

Alan

 

[acampbell012]

Ron:

Sorry. I recalled my previous post. I did not read your example closely
enough and totalled both numeric columns. Placed in a general module,
this should give you what you want. Adjust the column references as
needed.

Alan

Sub Summary()
Dim Endrow As Long
Dim endrow2 As Long
Endrow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F1:G" & Endrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("P1"), Unique:=True
endrow2 = Cells(Rows.Count, "P").End(xlUp).Row
Range("R2").Resize(endrow2 - 1, 1).Formula = "=SumIf(F$1:F$" & _
Endrow & ", $P2, G$1:G$" & Endrow & ")"
End Sub