Combining multiple rows with identical data into one single row

  • i currently have data spanning multiple rows and 9 columns, i want to combine the rows that have he same price. here is the data i am looking at:


    [TABLE="width: 626"]

    [tr]


    [td]

    Expiration Month

    [/td]


    [td]

    Expiration Day

    [/td]


    [td]

    Expiration Year

    [/td]


    [td]

    TYPE

    [/td]


    [td]

    Strike

    [/td]


    [td]

    Quantity

    [/td]


    [td]

    Price

    [/td]


    [td]

    Trader

    [/td]


    [td]

    Exchange

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    600

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    200

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [/TABLE]


    here is how i would like the data to look:


    [TABLE="width: 626"]

    [tr]


    [td]

    Expiration Month

    [/td]


    [td]

    Expiration Day

    [/td]


    [td]

    Expiration Year

    [/td]


    [td]

    TYPE

    [/td]


    [td]

    Strike

    [/td]


    [td]

    Quantity

    [/td]


    [td]

    Price

    [/td]


    [td]

    Trader

    [/td]


    [td]

    Exchange

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    1800

    [/td]


    [td]

    0.0945

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    1

    [/td]


    [td]

    2014

    [/td]


    [td]

    C

    [/td]


    [td]

    3.900

    [/td]


    [td]

    2000

    [/td]


    [td]

    0.1

    [/td]


    [td]

    chris

    [/td]


    [td]

    CME

    [/td]


    [/tr]


    [/TABLE]


    i have attached a spreadsheet as well. i really appreciate all your help. thanks.

  • Re: Combining multiple rows with identical data into one single row


    Hi..


    Here's one way..


    1. Loop through your 'Price" column.. adding the values to the Dictionary..


    2. Set up a loop and autofilter your usedrange by the Keys (unique Prices) of the Dictionary.


    3. Subtotal the Visible values in the "Quantity" column each time you filter.


    4. Either copy directly to sheet (one of the visible rows.. but overwrite the "Quantity" value with the Subtotal value).. or add build an array and write resulting array to sheet at end of Sub.


    Out of time right now (kids are waiting for me to watch a movie with them).. if you can't code what i am suggesting above.. post back.. and I will look at it tomorrow (or someone else will pick it up)..

  • Re: Combining multiple rows with identical data into one single row


    Try this..


  • Re: Combining multiple rows with identical data into one single row


    to all, thank you for your responses and your help. they proven both effective, informative and helpful.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!