Sharing a neat trick
 

I just stumbled over a neat trick that I thought I should share.
To expand data in a column so that there is a blank cell
between each row.

Put some data into A1 to A10
Put 1 into B1
Highlight B1 and B2
Grab the fill handle and drag down to B19
That should leave 1 blank 2 blank...etc in col B
Put =INDIRECT("A"&B1) in C1
Grab the fill handle and drag down to C19
That should leave your column A data in column C
separated by the #REF error.

Whilst column C is still selected
Tap F5SpecialFormulas
Uncheck Numbers, Text and Logicals and leave
Errors checked.
OK out
Then tap delete

Do a CopyPaste Values on column C
and delete columns A and B

Not groundbreaking stuff but I'm sure it will come in very
handy in the right situation.

Regards
Martin

Sharing a neat trick
 

Hi Martin,

I think I have seen something like this before, don't remember if the same
or similar.

The problem with tricks like this is remembering them. Personally, by the
time I had looked for and found the note or whatever showing me how to do
it, I could have written the code 20 times <G.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"MartinW" wrote in message
...
I just stumbled over a neat trick that I thought I should share.
To expand data in a column so that there is a blank cell
between each row.

Put some data into A1 to A10
Put 1 into B1
Highlight B1 and B2
Grab the fill handle and drag down to B19
That should leave 1 blank 2 blank...etc in col B
Put =INDIRECT("A"&B1) in C1
Grab the fill handle and drag down to C19
That should leave your column A data in column C
separated by the #REF error.

Whilst column C is still selected
Tap F5SpecialFormulas
Uncheck Numbers, Text and Logicals and leave
Errors checked.
OK out
Then tap delete

Do a CopyPaste Values on column C
and delete columns A and B

Not groundbreaking stuff but I'm sure it will come in very
handy in the right situation.

Regards
Martin

Sharing a neat trick
 

Yeah, I know what you mean by remembering Bob, by the
time I need to use this again it will probably dawn on me as
a new revelation all over again.

Regards
Martin


"Bob Phillips" wrote in message
...
Hi Martin,

I think I have seen something like this before, don't remember if the same
or similar.

The problem with tricks like this is remembering them. Personally, by the
time I had looked for and found the note or whatever showing me how to do
it, I could have written the code 20 times <G.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"MartinW" wrote in message
...
I just stumbled over a neat trick that I thought I should share.
To expand data in a column so that there is a blank cell
between each row.

Put some data into A1 to A10
Put 1 into B1
Highlight B1 and B2
Grab the fill handle and drag down to B19
That should leave 1 blank 2 blank...etc in col B
Put =INDIRECT("A"&B1) in C1
Grab the fill handle and drag down to C19
That should leave your column A data in column C
separated by the #REF error.

Whilst column C is still selected
Tap F5SpecialFormulas
Uncheck Numbers, Text and Logicals and leave
Errors checked.
OK out
Then tap delete

Do a CopyPaste Values on column C
and delete columns A and B

Not groundbreaking stuff but I'm sure it will come in very
handy in the right situation.

Regards
Martin

Sharing a neat trick
 

Yeah, but think of the good feeling you will get as you 'discover' it
(again) <bg

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"MartinW" wrote in message
...
Yeah, I know what you mean by remembering Bob, by the
time I need to use this again it will probably dawn on me as
a new revelation all over again.

Regards
Martin


"Bob Phillips" wrote in message
...
Hi Martin,

I think I have seen something like this before, don't remember if the
same or similar.

The problem with tricks like this is remembering them. Personally, by the
time I had looked for and found the note or whatever showing me how to do
it, I could have written the code 20 times <G.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"MartinW" wrote in message
...
I just stumbled over a neat trick that I thought I should share.
To expand data in a column so that there is a blank cell
between each row.

Put some data into A1 to A10
Put 1 into B1
Highlight B1 and B2
Grab the fill handle and drag down to B19
That should leave 1 blank 2 blank...etc in col B
Put =INDIRECT("A"&B1) in C1
Grab the fill handle and drag down to C19
That should leave your column A data in column C
separated by the #REF error.

Whilst column C is still selected
Tap F5SpecialFormulas
Uncheck Numbers, Text and Logicals and leave
Errors checked.
OK out
Then tap delete

Do a CopyPaste Values on column C
and delete columns A and B

Not groundbreaking stuff but I'm sure it will come in very
handy in the right situation.

Regards
Martin

Sharing a neat trick
 

LOL



"Bob Phillips" wrote in message
...
Yeah, but think of the good feeling you will get as you 'discover' it
(again) <bg

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"MartinW" wrote in message
...
Yeah, I know what you mean by remembering Bob, by the
time I need to use this again it will probably dawn on me as
a new revelation all over again.

Regards
Martin


"Bob Phillips" wrote in message
...
Hi Martin,

I think I have seen something like this before, don't remember if the
same or similar.

The problem with tricks like this is remembering them. Personally, by
the time I had looked for and found the note or whatever showing me how
to do it, I could have written the code 20 times <G.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"MartinW" wrote in message
...
I just stumbled over a neat trick that I thought I should share.
To expand data in a column so that there is a blank cell
between each row.

Put some data into A1 to A10
Put 1 into B1
Highlight B1 and B2
Grab the fill handle and drag down to B19
That should leave 1 blank 2 blank...etc in col B
Put =INDIRECT("A"&B1) in C1
Grab the fill handle and drag down to C19
That should leave your column A data in column C
separated by the #REF error.

Whilst column C is still selected
Tap F5SpecialFormulas
Uncheck Numbers, Text and Logicals and leave
Errors checked.
OK out
Then tap delete

Do a CopyPaste Values on column C
and delete columns A and B

Not groundbreaking stuff but I'm sure it will come in very
handy in the right situation.

Regards
Martin

Sharing a neat trick
 

This one even better

Let's say data A1:A10
in B1: enter 1, and B2 enter 2 highlight b1&b2 copy down to B10, select
b1:b10 copy to b11. Now you have two sets of 1:10
select both column A and B, sort on column B ascending order, then delete
column B

"MartinW" wrote:

I just stumbled over a neat trick that I thought I should share.
To expand data in a column so that there is a blank cell
between each row.

Put some data into A1 to A10
Put 1 into B1
Highlight B1 and B2
Grab the fill handle and drag down to B19
That should leave 1 blank 2 blank...etc in col B
Put =INDIRECT("A"&B1) in C1
Grab the fill handle and drag down to C19
That should leave your column A data in column C
separated by the #REF error.

Whilst column C is still selected
Tap F5SpecialFormulas
Uncheck Numbers, Text and Logicals and leave
Errors checked.
OK out
Then tap delete

Do a CopyPaste Values on column C
and delete columns A and B

Not groundbreaking stuff but I'm sure it will come in very
handy in the right situation.

Regards
Martin

Sharing a neat trick
 

Also don't forget the frustration you experience thinking, "Man I've
done this before! How the heck did I do it??"


On Jul 6, 9:43 am, "Bob Phillips" wrote:
Yeah, but think of the good feeling you will get as you 'discover' it
(again) <bg

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"MartinW" wrote in message

...



Yeah, I know what you mean by remembering Bob, by the
time I need to use this again it will probably dawn on me as
a new revelation all over again.

Regards
Martin

"Bob Phillips" wrote in message
...
Hi Martin,

I think I have seen something like this before, don't remember if the
same or similar.

The problem with tricks like this is remembering them. Personally, by the
time I had looked for and found the note or whatever showing me how to do
it, I could have written the code 20 times <G.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"MartinW" wrote in message
...
I just stumbled over a neat trick that I thought I should share.
To expand data in a column so that there is a blank cell
between each row.

Put some data into A1 to A10
Put 1 into B1
Highlight B1 and B2
Grab the fill handle and drag down to B19
That should leave 1 blank 2 blank...etc in col B
Put =INDIRECT("A"&B1) in C1
Grab the fill handle and drag down to C19
That should leave your column A data in column C
separated by the #REF error.

Whilst column C is still selected
Tap F5SpecialFormulas
Uncheck Numbers, Text and Logicals and leave
Errors checked.
OK out
Then tap delete

Do a CopyPaste Values on column C
and delete columns A and B

Not groundbreaking stuff but I'm sure it will come in very
handy in the right situation.

Regards
Martin- Hide quoted text -

- Show quoted text -