| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to trick a Dynamic Name to be Static when SOLVER is ON... | Excel Worksheet Functions | |||
| When Sharing | Excel Discussion (Misc queries) | |||
| Trick? for using INDIRECT references in Data Source of Chart? | Charts and Charting in Excel | |||
| CountBlank for all Names in a Column, and another trick | Excel Discussion (Misc queries) | |||
| What is the trick to make rows resize height automatically? | Excel Discussion (Misc queries) | |||
Linear Mode
