How do I check for duplicate rows and do calculate on that rows in excel file?

Question

I have one excel file which is having data like following

CUSIP   Quantity  Date           Price
AF0      500000   5/6/2013      1
AE4      400000   5/6/2013      1.0825
AE4      500      5/6/2013      1

I need to check for column CUSIP and Date If I'm having duplicate CUSIP for same date then I need to do following calculation.

1.Need to add Quantity for both of them instead of showing duplicate records need to show only one record( sum of Quantity).

2.Need to do calculation on Price as well like following

NewPrice = ((400000 * 1.0825) + (500 * 1.00))/(400000 + 500) = 1.08148

For example in using above data Need to show output like

CUSIP   Quantity  Date          Price
AF0     500000    5/6/2013      1
AE4     400500    5/6/2013      1.082397004

   

How do I achieve this in excel file using LOOKUP or else ?

Answer 1

Okay, after quite some research (interesting question by the way!), I came up with this:

=IF(COUNTIF($A$2:A2,A2)>1,"",SUMIF(A:A,A2,B:B))
=IF(COUNTIF($A$2:A2,A2)>1,"",SUMPRODUCT(--(A:A=A2),B:B,D:D)/SUMIF(A:A,A2,B:B))

Put these in cell D2 and E2 respectively (which is the next column after Price and in the row of CUSIP AF0).

And fill to the bottom of the worksheet to get the weighted average price or each CUSIP.

The first formula gives you the total quantity for the CUSIP and the second gives you the average price.

Copy and paste values for those two columns after calculation.

Put a filter and remove all the rows where the total quantity and average price is blank, and sort to make the worksheet neat.

Let me know if this works for you! I tried it on your sample data and it seems to be working. It's my first time using SUMPRODUCT ^^;

---

SUMPRODUCT(--(A:A=A2)

This bit returns the rows from column A where it equals to the row's CUSIP.

SUMPRODUCT(--(A:A=A2),B:B,D:D

This additional bit tells excel to multiply the values in column B and D of each returned row found above and SUMPRODUCT adds each result together.

EDIT: I actually forgot about the date. You could maybe add a helper column where you'll generate an identifier to separate the different dates. To make it, you'll have to make a concatenate.

Hence in cell F2, you put:

 =CONCATENATE(A2,C2)

In the formulae for cells D2 and E2, you will have to change them so they become:

=IF(COUNTIF($F$2:F2,F2)>1,"",SUMIF(A:A,A2,B:B))
=IF(COUNTIF($F$2:F2,F2)>1,"",SUMPRODUCT(--(A:A=A2),B:B,D:D)/SUMIF(A:A,A2,B:B))

reEDIT: Oops, put the wrong reference. Fixed now.