Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread

post #1 of 491
5/4/09 at 2:19pm
Thread Starter 
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.
Reply
Reply
post #2 of 491
5/4/09 at 2:20pm
Please create a stochastic interest-rate economic pricing model for your average single-premium deferred annuity.
Reply
Reply
post #3 of 491
5/4/09 at 2:23pm
Quote:
Originally Posted by MetroStyles View Post
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..
Reply
Reply
post #4 of 491
5/4/09 at 2:23pm
Quote:
Originally Posted by gnatty8 View Post
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.
Reply
Reply
post #5 of 491
5/4/09 at 2:26pm
Thread Starter 
Quote:
Originally Posted by MetroStyles View Post
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.
Reply
Reply
post #6 of 491
5/4/09 at 2:33pm
Thread Starter 
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

?"
Reply
Reply
post #7 of 491
5/4/09 at 2:35pm
Quote:
Originally Posted by CunningSmeagol View Post
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.
Reply
Reply
post #8 of 491
5/4/09 at 2:41pm
Quote:
Originally Posted by MetroStyles View Post
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.
Reply
Reply
post #9 of 491
5/4/09 at 2:42pm
Quote:
Originally Posted by dcg View Post
Don't overthink it. Highlight the column, Data -> Sort -> Ascending.

Duh.
Reply
Reply
post #10 of 491
5/4/09 at 2:43pm
Quote:
Originally Posted by dcg View Post
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
Reply
Reply
post #11 of 491
5/4/09 at 2:51pm
Quote:
Originally Posted by gdl203 View Post
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.
Reply
Reply
post #12 of 491
5/4/09 at 2:52pm
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.
Reply
Reply
post #13 of 491
5/4/09 at 2:59pm
Thread Starter 
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.
Reply
Reply
post #14 of 491
5/4/09 at 3:05pm
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?
Reply
Reply
post #15 of 491
5/4/09 at 3:14pm
Thread Starter 
Quote:
Originally Posted by dcg View Post
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.
Reply
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread