Extracting Data in Cells in order -- (or) eliminating empty cell space in a column

  • Thread starter Thread starter tx12345
  • Start date Start date
T

tx12345

Hi

I have this problem that I bet is easy to solve, but i am lost. I am an
expert at the slow way to do things, but maybe there is a better way.
The only way I can describe the problem is by means of an example.


Lets say I have a column of numbers:
_A_|_B_| etc >>
Click to expand...
1_1_|___|
2_3_|___|
3_2_|___|
5_5_|___|
5_3_|___|
6_4_|___|
7_7_|___|
8_3_|___|
9_1_|___|

and then i write a little function in the adjoing cell, B1:

=if(a1=3,a2,"")

From there I fill down column B to B9.

OK, pretty simple so far, right? What I am looking for is instances
where I find a '3' in column A, and if I do, then I want the cell in
column B to show me what the next number was that followed it.

So then my spread sheet looks like this:
_A_|_B_| etc >>
Click to expand...
1_1_|___|
2_3_|_2_|
3_2_|___|
5_5_|___|
5_3_|_4_|
6_4_|___|
7_7_|___|
8_3_|_1_|
9_1_|___|

With me so far? My little if function found three instances where '3'
showed up in column 'A', and then showed me the three different numbers
that followed the number three at that point -- a 2,4, and a 1

My problem is this: see all that blank space between 2 and 4, and 4 and
1 in column B? How do I get rid of that, or set up another cell, or set
of cells ome where where all the blank space gets sucked out, so I see a
new column that looks like this:

_|_B_|
1|_2_|
2|_4_|
3|_1_|

Why am I doing this? Because I want to chart the progress of the
numbers in a simple line chart, and if i leave all the blank spaces it
makes the chart look weird, and distorts the findings.

I think I explained the problem as clearly as I could. Any help would
be fantastic.

Thanks!
 
Have you tried Auto-filtering on non-blanks to show just the 2,4,1 then select
that range to chart?


Gord Dibben Excel MVP
 
How about you *just* revise your formula to return a #N/A error, which you
will see, will not distort your graph?

Try it, you may like it!

=IF(A1=3,A2,#N/A)
 
One non-array way ..

Assume source data in A1 down

Put in B1: =IF(A1=3,ROW(),"")

Put in C1:
=IF(ISERROR(MATCH(SMALL(B:B,ROWS($A$1:A1)),B:B,0)),"",INDEX(A:A,MATCH(SMALL(
B:B,ROWS($A$1:A1)),B:B,0)+1))

Select B1:C1 and fill down

Col C will return the results you seek
 
What happens if there are consecutive 3's or the last value in the range is
a 3?

2
5
1
3
3
3

Biff
 
RAGDYER,

Sweeeeet!!

Thanks!

Thanks to all for your suggestions. I promise when i make the million
from these formulas, i'll give you a nice big tip
 
Appreciate the feed-back,

And you should realize that *YOU* precipitated the suggestion, simply
because you gave a full explanation of your actual goal.

How easy it would be if all OPs would do the same.<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


message
RAGDYER,

Sweeeeet!!

Thanks!

Thanks to all for your suggestions. I promise when i make the millions
from these formulas, i'll give you a nice big tip!
 
Back
Top