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

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

On Mon, 4 Jul 2005 17:59:57 -0500, tx12345
<[email protected]> wrote:

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

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)
--
HTH,

RD

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


"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> 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 >>
> 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 >>
> 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!
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=384406
>

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> 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 >>
> 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 >>
> 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!
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=384406
>

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

"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> 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 >>
> 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 >>
> 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!
>
>
> --
> tx12345
> ------------------------------------------------------------------------
> tx12345's Profile:
> http://www.excelforum.com/member.php...o&userid=24776
> View this thread: http://www.excelforum.com/showthread...hreadid=384406
>

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!

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


"tx12345" <[email protected]> wrote in
message news:[email protected]...

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!


--
tx12345
------------------------------------------------------------------------
tx12345's Profile:
http://www.excelforum.com/member.php...o&userid=24776
View this thread: http://www.excelforum.com/showthread...hreadid=384406