From A1:A100(discontiguous data) to B1:B73(full)

[Mauricio Samy Silva]

Hi,
Range A1:A100 is filled with data and will count aleatorias empty cells.
Say, there are 27 aleatorias cells empty and 73 with data, in that range.
How to make to carry the data from A1:A100 to the B1:B73 (a full range of data).
I used =OFFSET formula, with a =LIN() reference in a dummie column,bla..bla..bla, but it's sounds me to be not a clean and good solution. Thanks for comments!

 

[Jim North]

You COPY then PASTE SPECIAL telling it to "skip blanks". To get a VBA version, just turn on the Macro recorder and walk through the steps.

 

[Mauricio Samy Silva]

OK JIM,
Thanks, but I'm looking for a formula in range B1:B73. I'll use in others ranges and arrays and I want also to work with formulas and not macros.
It is possible do make this by another way than that I'd already perform?

 

[Joel Horowitz]

One solution that I hope is simpler is the following: suppose your column is not in A1:A100 but in B1:B100.

1) In column A, you put something like
A1=1
A2=A1+(B2<>"")
A3=A2+(B3<>"")
and so forth until A100

2) After that, you can carry your B1:B100 column to C1:C73 by putting
C1=vlookup(row(),A$1:B$100,2)
C2=vlookup(row(),A$1:B$100,2)
etc...

Hope this helps
This message was edited by Joel Horowitz on 2002-08-24 16:25

 

[Mauricio Samy Silva]

Thanks Joel!
Apreciated your replay

 

[eliW]

Hi Mauricio,

Enter the folowing formula in B1 and copy down untill B100:

=IF(LEN(A1)>1,ROW(),"")

Now sort your range (A1:B100) on column B and you'll have all your data in the same order in A1:A73.

Eli
This message was edited by eliW on 2002-08-24 22:52

 

[Joel Horowitz]

Nice solution! Maybe it's better to put
LEN(A1)>=1.

As Excel geek and ex-Perl geek, I can't stop myself trying to make the formula even a couple of characters shorter: put B1 to
=A1="", copy down, and sort on column B.

On 2002-08-24 22:51, eliW wrote:
Hi Mauricio,

Enter the folowing formula in B1 and copy down untill B100:

=IF(LEN(A1)>1,ROW(),"")

Now sort your range (A1:B100) on column B and you'll have all your data in the same order in A1:A73.

Eli
This message was edited by eliW on 2002-08-24 22:52

This message was edited by Joel Horowitz on 2002-08-25 14:15

 

[Aladin Akyurek]

On 2002-08-25 13:40, Joel Horowitz wrote:
Nice solution! Maybe it's better to put
LEN(A1)>=1.

Just LEN(A1) will also suffice in

=IF(LEN(A1),ROW(),"")

or

=IF(LEN(A1),ROW(1:1),"")

As Excel geek and ex-Perl geek, I can't stop myself trying to make the formula even a couple of characters shorter: put B1 to
=A1="", copy down, and sort on column B.

This is nice too.

Advanced Filter might also be appropriate...

Just insert a row before current row 1 and put a label in bold A1 and use the following computed criteria:

=LEN(A2)

Then filter in place or to C1.

By the way, nice to see you back.

Aladin

 

[eliW]

On 2002-08-25 13:40, Joel Horowitz wrote:
Nice solution! Maybe it's better to put
LEN(A1)>=1.

Joel,

Thank you for the correction, I ment actually :
LEN(A1)>0

Eli
This message was edited by eliW on 2002-08-26 00:47