Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with summative column (VLOOKUP???)
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with summative column (VLOOKUP???)
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. Brian B. wrote: 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 wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with summative column (VLOOKUP???)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with summative column (VLOOKUP???)
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 wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with summative column (VLOOKUP???)
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 wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with summative column (VLOOKUP???)
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 wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup for Column b | Excel Worksheet Functions | |||
vlookup 1st column time 2nd column text | Excel Worksheet Functions | |||
VLookup - Column name? | Excel Discussion (Misc queries) | |||
vlookup a value in column B but return value in Column A | Excel Discussion (Misc queries) | |||
VLookup & Column Sum | Excel Worksheet Functions |