1. In 2018. by way of thanks for being a part of this community, we'll be choosing 16 active members of our community at random to receive a special gift and a gift certificate for one of our affiliate vendors, to represent each of our 16 years.

    Fok and the Styleforum Team

    Dismiss Notice
Dismiss Notice

STYLE. COMMUNITY. GREAT CLOTHING.

Bored of counting likes on social networks? At Styleforum, you’ll find rousing discussions that go beyond strings of emojis.

Click Here to join Styleforum's thousands of style enthusiasts today!

The Excel Questions Thread

Discussion in 'General Chat' started by CunningSmeagol, May 4, 2009.

  1. CunningSmeagol

    CunningSmeagol Distinguished Member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Let us free up the next 5 hours of your life. Need to stay sharp now that I'm in back in school.

    Edit: Changed the original title, since we're all in this together.
     


  2. MetroStyles

    MetroStyles Stylish Dinosaur

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Please create a stochastic interest-rate economic pricing model for your average single-premium deferred annuity.
     


  3. gnatty8

    gnatty8 Distinguished Member

    Messages:
    9,507
    Likes Received:
    1,511
    Joined:
    Nov 12, 2006
    Location:
    Not in Atlanta, GA
    Please create a stochastic interest-rate economic pricing model for your average single-premium deferred annuity.

    I have one of those for bond options, could probably be finagled to do what you want.. You will need a copy of Crystal Ball or be able to program a monte carlo engine in VBA for it to work..
     


  4. MetroStyles

    MetroStyles Stylish Dinosaur

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    I have one of those for bond options, could probably be finagled to do what you want.. You will need a copy of Crystal Ball or be able to program a monte carlo engine in VBA for it to work..

    Haha, no I already have the model. I was just being a douchebag.
     


  5. CunningSmeagol

    CunningSmeagol Distinguished Member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Haha, no I already have the model. I was just being a douchebag.

    Good because for a second I thought this was going to be too easy.
     


  6. CunningSmeagol

    CunningSmeagol Distinguished Member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    But seriously I was thinking more generic, along the lines of "how do I consolidate a vertical list of items that are at separated randomly by blank cells? i.e.

    from:

    a

    b
    c


    d

    e

    f

    to:

    a
    b
    c
    d
    e
    f

    ?"
     


  7. MetroStyles

    MetroStyles Stylish Dinosaur

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    But seriously I was thinking more generic, along the lines of "how do I consolidate a vertical list of items that are at separated randomly by blank cells? i.e.
    "


    Is it doable without a macro? I always had to write a macro to do that.
     


  8. dcg

    dcg Distinguished Member

    Messages:
    4,113
    Likes Received:
    501
    Joined:
    Nov 2, 2007
    Location:
    Philly
    Is it doable without a macro? I always had to write a macro to do that.

    Don't overthink it. Highlight the column, Data -> Sort -> Ascending.
     


  9. MetroStyles

    MetroStyles Stylish Dinosaur

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Don't overthink it. Highlight the column, Data -> Sort -> Ascending.

    Duh. [​IMG]
     


  10. gdl203

    gdl203 Affiliate Vendor Dubiously Honored Affiliate Vendor

    Messages:
    37,425
    Likes Received:
    19,178
    Joined:
    Jun 9, 2005
    Location:
    New York
    Don't overthink it. Highlight the column, Data -> Sort -> Ascending.

    I thnk you took the a, b, c, d part a little too literally. Just a guess
     


  11. dcg

    dcg Distinguished Member

    Messages:
    4,113
    Likes Received:
    501
    Joined:
    Nov 2, 2007
    Location:
    Philly
    I thnk you took the a, b, c, d part a little too literally. Just a guess

    Should work regardless of the data, no? All we're trying to do is get rid of the blanks.
     


  12. MetroStyles

    MetroStyles Stylish Dinosaur

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Even if you needed it ordered in a specific way, you could just add ordered numbering in an adjacent column and re-order to the old order after the sort.
     


  13. CunningSmeagol

    CunningSmeagol Distinguished Member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    I do it with an array formula (assume the data is in column A1 -> Ax), in cell B1:

    {=INDIRECT("A"&SMALL(IF($A$1:$A$x<>"",ROW($A$1:$A$x)),ROW(A1)))}

    Drag down.
     


  14. dcg

    dcg Distinguished Member

    Messages:
    4,113
    Likes Received:
    501
    Joined:
    Nov 2, 2007
    Location:
    Philly
    Here's a question for the OP (or anyone else that might feel like answering).

    Say you have a list of a few thousand cells that you need deduped. Ex:

    1
    1
    2
    5
    10
    13
    13
    13
    16
    22
    ....

    Assume your list starts in A2. When I've had to accomplish this I've entered

    =IF(A2=A1, 1, 0)

    into B2, then copied -> paste special -> values both columns, then sorted both columns by column B ascending, giving you a deduped list in column A for all rows where column B is 0.

    Is there an easier way to dedupe a list?
     


  15. CunningSmeagol

    CunningSmeagol Distinguished Member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Here's a question for the OP (or anyone else that might feel like answering).

    Say you have a list of a few thousand cells that you need deduped. Ex:

    1
    1
    2
    5
    10
    13
    13
    13
    16
    22
    ....

    Assume your list starts in A2. When I've had to accomplish this I've entered

    =IF(A2=A1, 1, 0)

    into B2, then copied -> paste special -> values both columns, then sorted both columns by column B ascending, giving you a deduped list in column A for all rows where column B is 0.

    Is there an easier way to dedupe a list?


    I would modify the first formula I posted:

    {=INDIRECT("A"&SMALL(IF($A$2:$A$15<>$A$1:$A$14,ROW($A$2:$A$15)),ROW(A1)))}

    Drag down.
     


Share This Page

Styleforum is proudly sponsored by