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

#### Mauricio Samy Silva

##### New Member
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!

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.

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?

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

Thanks Joel!

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

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

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.

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

Replies
5
Views
220
Replies
2
Views
261
Replies
10
Views
325
Replies
7
Views
395
Replies
2
Views
1K

1,221,185
Messages
6,158,411
Members
451,490
Latest member
desktopace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back