Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
Is there a "formula" that I can put into RowB that will remove the blank
cells from RowA without sorting or manually copying and pasting? I posted this earlier but didn't get an anwer if there is a formula or not. Example: A B 1 8 8 2 6 6 3 2 4 2 5 5 5 6 6 6 4 7 8 9 4 Thank you for your help Luke |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
A simple non-array play ..
In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Luke" wrote: Is there a "formula" that I can put into RowB that will remove the blank cells from RowA without sorting or manually copying and pasting? I posted this earlier but didn't get an anwer if there is a formula or not. Example: A B 1 8 8 2 6 6 3 2 4 2 5 5 5 6 6 6 4 7 8 9 4 Thank you for your help Luke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
Very much Appreciated!!!! I can't tell you how happy I am right now :)
Thank you again Max, you are most excellent! Luke "Max" wrote: A simple non-array play .. In B1: =IF(A1="","",ROW()) In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) )) Copy B1:C1 down to cover the max expected extent of data in col A. Minimize/hide away col B. Col C will return the results that you seek, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Luke" wrote: Is there a "formula" that I can put into RowB that will remove the blank cells from RowA without sorting or manually copying and pasting? I posted this earlier but didn't get an anwer if there is a formula or not. Example: A B 1 8 8 2 6 6 3 2 4 2 5 5 5 6 6 6 4 7 8 9 4 Thank you for your help Luke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
Welcome, glad it worked out ok for you.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Luke" wrote in message ... Very much Appreciated!!!! I can't tell you how happy I am right now :) Thank you again Max, you are most excellent! Luke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
Max,
How would I include other sheets in these formula? I have another sheet that would essentially take the place of RowA. I Should have said that in the first place but I thought I could figure it out. Thanks Max Luke Example: Sheet1 A 1 8 2 6 3 4 2 5 5 6 6 7 8 9 4 Sheet2: B 1 8 2 6 3 2 4 5 5 6 6 4 7 8 9 "Max" wrote: Welcome, glad it worked out ok for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Luke" wrote in message ... Very much Appreciated!!!! I can't tell you how happy I am right now :) Thank you again Max, you are most excellent! Luke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
Max,
I also need to know how to keep from using "full row reference" in the formula as it slows me way down on calculating. Regards Luke "Max" wrote: Welcome, glad it worked out ok for you. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Luke" wrote in message ... Very much Appreciated!!!! I can't tell you how happy I am right now :) Thank you again Max, you are most excellent! Luke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
How would I include other sheets in these formula?
If your source data is in Sheet1, in A1 down Then in Sheet2, In A1: =IF(Sheet1!A1="","",ROW()) In B1: =IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW()))) Copy A1:B1 down to cover the max expected extent of data in Sheet1's col A, eg down to B200. Minimize/hide away col A. Col B will return the results that you seek, all neatly bunched at the top. I also need to know how to keep from using "full row reference" in the formula as it slows me way down on calculating. I don't think there's a material performance difference once you limit the copy down to the smallest range large enough to cover the max expected extent of source data (eg to B200 in the above). It's always neater to use entire col refs. Anyway, if you want to use fixed ranges ... If source data in Sheet1 is expected within A1:A200 only Then you could use instead in Sheet2's B1: =IF(ROW()COUNT(A$1:A$200),"",INDEX(Sheet1!A$1:A$2 00,SMALL(A$1:A$200,ROW()))) (no change to A1's formula) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
I love it! I do beleive I hear the cats meow when I plugged that in :)
Max, You Rock to the Max! Most people wouldn't buy the cow when the milk is free but right now I wish I could pay you. Your are one of the best in my book, Best regards to you, Luke "Max" wrote: How would I include other sheets in these formula? If your source data is in Sheet1, in A1 down Then in Sheet2, In A1: =IF(Sheet1!A1="","",ROW()) In B1: =IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW()))) Copy A1:B1 down to cover the max expected extent of data in Sheet1's col A, eg down to B200. Minimize/hide away col A. Col B will return the results that you seek, all neatly bunched at the top. I also need to know how to keep from using "full row reference" in the formula as it slows me way down on calculating. I don't think there's a material performance difference once you limit the copy down to the smallest range large enough to cover the max expected extent of source data (eg to B200 in the above). It's always neater to use entire col refs. Anyway, if you want to use fixed ranges ... If source data in Sheet1 is expected within A1:A200 only Then you could use instead in Sheet2's B1: =IF(ROW()COUNT(A$1:A$200),"",INDEX(Sheet1!A$1:A$2 00,SMALL(A$1:A$200,ROW()))) (no change to A1's formula) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rephrased - Remove blank cells - no sorting, copy or pasting
Welcome, Luke. Glad to hear the sounds of satisfaction!
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Luke" wrote in message ... I love it! I do believe I hear the cats meow when I plugged that in :) Max, You Rock to the Max! Most people wouldn't buy the cow when the milk is free but right now I wish I could pay you. Your are one of the best in my book, Best regards to you, Luke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove blank cells - no sorting | Excel Worksheet Functions | |||
putting a (.) period in blank cells when pasting data | Excel Worksheet Functions | |||
Copy and pasting ref cells is not working | Excel Discussion (Misc queries) | |||
Remove blank cells from a range | Excel Discussion (Misc queries) | |||
remove paddings in cells when pasting from excel to word? | Excel Discussion (Misc queries) |