The Excel Questions Thread

[CunningSmeagol]

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.

 

[MetroStyles]

Please create a stochastic interest-rate economic pricing model for your average single-premium deferred annuity.

 

[gnatty8]

Originally Posted by MetroStyles

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..

 

[MetroStyles]

Originally Posted by gnatty8

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.

 

[CunningSmeagol]

Originally Posted by MetroStyles

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.

 

[CunningSmeagol]

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

?"

 

[MetroStyles]

Originally Posted by CunningSmeagol

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.

 

[dcg]

Originally Posted by MetroStyles

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.

 

[MetroStyles]

Originally Posted by dcg

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

Duh.

 

[gdl203]

Originally Posted by dcg

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

 

[dcg]

Originally Posted by gdl203

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.

 

[MetroStyles]

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.

 

[CunningSmeagol]

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.

 

[dcg]

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?

 

[CunningSmeagol]

Originally Posted by dcg

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.