Summing based on a unique ID

I have a worksheet consisting of 5,000 lines with 4 columns of data:

(1) (2) (3) (4)
Portfolio Deal # Reference ID Raw Amount


I have written a macro to create a separate worksheet tab, within the
same workbook, for each of the 40 unique portfolios in column 1. This
works fine.

However within each of these separate worksheets I would like to do a
sum on each Deal#
as there could be as many as 10 line entries for a particular deal.

Is this some thing that could be accomplished in one step, or do I have
to write another macro to do the summing of the deals?

I used an array to create the separate worksheets but couldn't get a
formula to sum up the raw amount based on Deal # within the array.

Any help would be appreciated.

You could use SUMPRODUCT.


=SUMPRODUCT((B2:B5000=YourDealNumberHere)*(D2:D5000))



HTH

Steve

Steve,

Maybe this will help:

What I have What I want

Portfolio Deal# Ref ID# Raw Amt Portfolio Deal# Ref
ID# Raw Amt
FLEX 3467438 CD20030623.3 $267.75
FLEX 3467438 CD20030623.3 ($191.25)
FLEX 3467438 CD20030623.3 ($459.00)
FLEX 3467443 CD20031119.3 ($45.21)
FLEX 3467443 CD20031119.3 ($278.78)
FLEX 3467506 CD20040113.7 ($432.00)
FLEX 3467443 CD20031119.3 $248.64
FLEX 3468027 SI20020320.6 ($9,860.39)
FLEX 3467443 CD20031119.3 ($15.07)
FLEX 3468028 SI20020320.8 ($11,804.67)
FLEX 3467506 CD20040113.7 ($1,036.80)
FLEX 3468106 SI20030815.3 ($3,423.06)
FLEX 3467506 CD20040113.7 $604.80 FLEX 3467692 SI20031118.22 $63.68

FLEX 3468027 SI20020320.6 ($22,242.87) Total
($25,692.90)
FLEX 3468027 SI20020320.6 $12,382.48
FLEX 3468028 SI20020320.8 ($26,605.69)
FLEX 3468028 SI20020320.8 $14,801.02
FLEX 3468106 SI20030815.3 $8,596.67
FLEX 3468106 SI20030815.3 ($12,019.73)
FLEX 3467692 SI20031118.22 ($176.63)
FLEX 3467692 SI20031118.22 $7.71
FLEX 3467692 SI20031118.22 $232.60
Total ($25,692.90)

I don't think SUMPRODUCT works in this case.

SUMPRODUCT should work, but so should SUMIF

=SUMIF(B2:B5000,YourDealNumberHere,D2:D5000)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jackc" <[email protected]> wrote in message
news:[email protected]...
> Steve,
>
> Maybe this will help:
>
> What I have What I want
>
> Portfolio Deal# Ref ID# Raw Amt Portfolio Deal# Ref
> ID# Raw Amt
> FLEX 3467438 CD20030623.3 $267.75
> FLEX 3467438 CD20030623.3 ($191.25)
> FLEX 3467438 CD20030623.3 ($459.00)
> FLEX 3467443 CD20031119.3 ($45.21)
> FLEX 3467443 CD20031119.3 ($278.78)
> FLEX 3467506 CD20040113.7 ($432.00)
> FLEX 3467443 CD20031119.3 $248.64
> FLEX 3468027 SI20020320.6 ($9,860.39)
> FLEX 3467443 CD20031119.3 ($15.07)
> FLEX 3468028 SI20020320.8 ($11,804.67)
> FLEX 3467506 CD20040113.7 ($1,036.80)
> FLEX 3468106 SI20030815.3 ($3,423.06)
> FLEX 3467506 CD20040113.7 $604.80 FLEX 3467692 SI20031118.22 $63.68
>
> FLEX 3468027 SI20020320.6 ($22,242.87) Total
> ($25,692.90)
> FLEX 3468027 SI20020320.6 $12,382.48
> FLEX 3468028 SI20020320.8 ($26,605.69)
> FLEX 3468028 SI20020320.8 $14,801.02
> FLEX 3468106 SI20030815.3 $8,596.67
> FLEX 3468106 SI20030815.3 ($12,019.73)
> FLEX 3467692 SI20031118.22 ($176.63)
> FLEX 3467692 SI20031118.22 $7.71
> FLEX 3467692 SI20031118.22 $232.60
> Total ($25,692.90)
>
> I don't think SUMPRODUCT works in this case.
>

Thanks for the reply, but I still am having a problem. Perhaps I have
not properly explained what I am trying to accomplish.

Starting with a schedule of 5000 lines with 4 columns of data, I would
like to create about 40 separate worksheets based on data from column
1(Portfolios). Within each of these separate worksheets I would like a
total of column 4 (Raw Amount) for each unique item in column 2(Deal
#).

I have written a macro to create the separate worksheets. Within each
worksheet I have valid multiple occurrences of the same Deal # . I
would like, as part of my macro, to sum these deals based on the raw
amount and show only one line for each unique Deal #.

The macro is below

Sub FindUniquePFIDandPutallintoarray()
Dim n As Integer, Found As Boolean

Worksheets("NII Summary").Activate


With Range("A1")

NR = 0
UniquePFN = 1

ReDim Preserve UniquePF(UniquePFN)
UniquePF(UniquePFN) = .Offset(1, 0)

Do While .Offset(NR + 1, 0) <> ""
NR = NR + 1

ReDim Preserve PF(NR)
ReDim Preserve Dealnumber(NR)
ReDim Preserve Valtype(NR)
ReDim Preserve DerID(NR)
ReDim Preserve Desc(NR)
ReDim Preserve INC(NR)
ReDim Preserve Matdate(NR)
ReDim Preserve Notorig(NR)
ReDim Preserve NotionUSD(NR)
ReDim Preserve MV(NR)

PF(NR) = .Offset(NR, 0)
Dealnumber(NR) = .Offset(NR, 1)
DerID(NR) = .Offset(NR, 2)
Valtype(NR) = .Offset(NR, 3)
Desc(NR) = .Offset(NR, 4)
INC(NR) = .Offset(NR, 5)
Matdate(NR) = .Offset(NR, 6)


Found = False

For n = 1 To UniquePFN
If .Offset(NR, 0) = UniquePF(n) Then
Found = True
Exit For
End If
Next
If Found = False Then
UniquePFN = UniquePFN + 1
ReDim Preserve UniquePF(UniquePFN)
UniquePF(UniquePFN) = .Offset(NR, 0)
End If
Loop

If NR = 0 Then Exit Sub

End With


End Sub


Sub Createseparatesheetsandputinfo()
Dim n As Integer, k As Integer, row As Integer

n = 0

For n = 1 To UniquePFN



Sheets.Add 'after:=Worksheets(Worksheets.Count - 3)
With ActiveSheet
.Name = UniquePF(n)

With Range("A1")
.Value = "Net Income Detail for " & ActiveSheet.Name & " as
of " & TradeDate
.Font.Bold = True
End With

With Range("A3")
.Offset(0, 0) = "Portfolio"
.Offset(0, 1) = "Deal#"
.Offset(0, 2) = "Der ID#"
.Offset(0, 3) = "NII"
Range(.Offset(0, 0), .Offset(0, 3)).Font.Bold = True


row = 0
k = 0

For k = 1 To NR
If PF(k) = UniquePF(n) Then
row = row + 1
.Offset(row, 0) = PF(k)
.Offset(row, 1) = Dealnumber(k)
.Offset(row, 2) = DerID(k)
.Offset(row, 3) = INC(k)

End If
Next
.Offset(row + 1, 0) = "Total"
.Offset(row + 1, 3) = "=SUM(R[-" & row + 1 & "]C:R[-1]C)"
.Offset(row + 1, 3).NumberFormat =
"$#,##0.00_);($#,##0.00)"


End With

End With

Columns("A").ColumnWidth = 10
Columns("B:I").EntireColumn.AutoFit
Range("A1").Select

Next

End Sub


Is there a way to incorporate a summing process in the portion of the
macro that creates the separate sheets?


Thanks for your help.

Jack C

hi: Jack

I think the quick and easy solution to your problem maybe the
PivotTable. The PivotTable has the ablity to summing based on a unique
ID in seconds.
I attached a link below that teach you how to create PivotTable and I
believe that maybe your solution to your solution.

http://www.cpearson.com/excel/pivots.htm

Eddie,

Thanks for the reply.

A pivot table will work, however I need to create a separate worksheet
for each of the 40 portfolios.

Sounds like a job for a database to me.


"jackc" <[email protected]> wrote in message
news:[email protected]...
>I have a worksheet consisting of 5,000 lines with 4 columns of data:
>
> (1) (2) (3) (4)
> Portfolio Deal # Reference ID Raw Amount
>
>
> I have written a macro to create a separate worksheet tab, within the
> same workbook, for each of the 40 unique portfolios in column 1. This
> works fine.
>
> However within each of these separate worksheets I would like to do a
> sum on each Deal#
> as there could be as many as 10 line entries for a particular deal.
>
> Is this some thing that could be accomplished in one step, or do I have
> to write another macro to do the summing of the deals?
>
> I used an array to create the separate worksheets but couldn't get a
> formula to sum up the raw amount based on Deal # within the array.
>
> Any help would be appreciated.
>

You should use a pivot table. You could then have deal# in the row
section and raw amount in the data section. It would take you all of 2
minutes to set it up.

the 'show pages' function in the pivot table will automatically create a worksheet for each 'deal' if you have 'deal' in the 'Page' are of the pivot table.

Hi Jack

But following on from Eddie's suggestion, if you do create a Pivot
Table, and make Portfolio Deal a Page field, Deal # as a row Field and
Raw Amount (Sum) as the Data field.
Then you can use the drop down on the Pivot Table toolbar to Show Pages.
This will create a separate Sheet named as each of the Portfolio names,
with each of the Deals summarised and totalled as you require.
--
Regards

Roger Govier


"jackc" <[email protected]> wrote in message
news:[email protected]...
> Eddie,
>
> Thanks for the reply.
>
> A pivot table will work, however I need to create a separate worksheet
> for each of the 40 portfolios.
>

Apologies, Steven.
Your solution only popped up on my screen just after I had hit the send
button.

--
Regards

Roger Govier


"steven1001" <[email protected]>
wrote in message
news:[email protected]...
>
> the 'show pages' function in the pivot table will automatically create
> a
> worksheet for each 'deal' if you have 'deal' in the 'Page' are of the
> pivot table.
>
>
> --
> steven1001
> ------------------------------------------------------------------------
> steven1001's Profile:
> http://www.excelforum.com/member.php...o&userid=30757
> View this thread:
> http://www.excelforum.com/showthread...hreadid=521243
>

In addition to that:
If you double-click on an item of the pivot table, xl creates an extra
worksheet with just that data. Surely you can do that using the macro
recorder and make a (small) macro that does the other 39 ?

Bas Hartkamp.


"Roger Govier" <[email protected]> schreef in bericht
news:[email protected]...
> Hi Jack
>
> But following on from Eddie's suggestion, if you do create a Pivot Table,
> and make Portfolio Deal a Page field, Deal # as a row Field and Raw Amount
> (Sum) as the Data field.
> Then you can use the drop down on the Pivot Table toolbar to Show Pages.
> This will create a separate Sheet named as each of the Portfolio names,
> with each of the Deals summarised and totalled as you require.
> --
> Regards
>
> Roger Govier
>
>
> "jackc" <[email protected]> wrote in message
> news:[email protected]...
>> Eddie,
>>
>> Thanks for the reply.
>>
>> A pivot table will work, however I need to create a separate worksheet
>> for each of the 40 portfolios.
>>
>
>

Gentlemen,

I'd like to thank each of you for your ideas and suggestions.

The method I chose was filtering the Deal# within each Portfolio and
doing a SUMIF on the filtered deals. I incorporated this into my macro
and it works perfectly.

Thanks again for all of your help.

Jack C