Sum TWO columns and delete rows

[cdrobinson83]

Hi,

I'm trying to figure out how I can sum columns I and J while deleting the rows in the process that are deemed duplicates. Match criteria are columns A,C,E,F,H,K, and L. Example below of what I'm trying to do. Is anyone able to help? I can't for the life of me figure it out. I've looked at other posts and I just can't apply the logic.

Before:

After:

 

[alansidman]

Rather than have us try to recreate your data to test, please upload your sample data using XL2BB.

 

[cdrobinson83]

Sorry about that. I'm not able to download that extension. Does the below help?

Account

Account Name

Firm

LopId

Exchange code

Future code

Desc

CTYM

Ex Long Qty

As Short Qty

Strike Price

Put/Call Value

EDSP of undl.ppm

ITM/ATM/OTM

Class

Subclass

Firm LEI

Account LEI

Business Close Date

Expiration Date

Trade Time

MIC Code

Contract ISIN

Buy/Sell

111​

aaa

M

xxxxxx

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

15​

95.75​

P

95.23​

ITM

C

I

B

222​

bbb

M

yyyyyy

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

50​

95.75​

P

95.23​

ITM

C

I

B

333​

ccc

M

zzzzzz

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

70​

95.75​

P

95.23​

ITM

C

I

B

111​

aaa

M

xxxxxx

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

15​

95.75​

P

95.23​

ITM

C

I

B

222​

bbb

M

yyyyyy

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

50​

95.75​

P

95.23​

ITM

C

I

B

333​

ccc

M

zzzzzz

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

70​

95.75​

P

95.23​

ITM

C

I

B

111​

aaa

M

xxxxxx

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

15​

95.75​

P

95.23​

ITM

C

I

B

222​

bbb

M

yyyyyy

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

50​

95.75​

P

95.23​

ITM

C

I

B

333​

ccc

M

zzzzzz

9C

S0

PUT SEP 23 3M SOFR 1YMC 9575

202309​

0​

70​

95.75​

P

95.23​

ITM

C

I

B

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source, {"Account", "Firm", "Exchange code", "Future code", "CTYM", "Strike Price", "Put/Call Value"})
in
    #"Removed Duplicates"

 

[cdrobinson83]

"Expression.Error: We couldn't find an Excel table named 'Table1'."

Is there something I need to name?

 

[alansidman]

Yes, the data you have shown as your source data needs to be named Table1 or change the code to reflect the table name you have used. The following may help

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[cdrobinson83]

Thank you very much. I have it removing the dupes now but I need it to also sum columns "Ex Long Qty" and "As Short Qty" as well.

 

[alansidman]

Does that need to be summed by Account Name? In Total? In your example if by account name, then there is nothing to do unless your example is not representative of your actual data. Please expand your needs explanation.

 

[cdrobinson83]

Sorry about that. I thought I mentioned that in my first post. I need to delete any "duplicates" based on criteria "Account", "Firm", "Exchange code", "Future code", "CTYM", "Strike Price", "Put/Call Value" while taking the quantities in "Ex Long Qty" and "As Short Qty" and summing them. Before and after below.

 

[alansidman]

Apologies if I missed that requirement. Because I had to copy and Paste from your data that was not posted with XL2BB, I had to change the data type as shown in the second line. When I copied and pasted it to excel it came over as text. You may not need that line of Mcode.

Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ex Long Qty", type number}, {"As Short Qty", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account", "Firm", "Exchange code", "Future code", "CTYM", "Put/Call Value"}, {{"Sum Ex Long Qty", each List.Sum([Ex Long Qty]), type text}, {"Sum As Short Qty", each List.Sum([As Short Qty]), type text}})
in
    #"Grouped Rows"