Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
remove blank cells - no sorting
Is there a way to have contents of row A placed in row B without the blank
cells and without sorting? 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
|
|||
|
|||
remove blank cells - no sorting
Copy data from Column A and Paste it to Column B hit F5 Special Blanks
OK out Edit Delete Shift cells up OK out "Luke" wrote: Is there a way to have contents of row A placed in row B without the blank cells and without sorting? 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
|
|||
|
|||
remove blank cells - no sorting
If I understand you correctly, you want to copy the Col_A values
and paste them over the Col_B values...but you don't want the blanks in Col_A to write over values in Col_B. If that's correct, try this: Using your example Copy the Col_A values (A1:A9) Select cell B1 From the Excel Main Menu: <edit<paste special ....Check: Values ....Check: Skip blanks Click [OK] Press the [ESC] key to end cut/copy mode. Does that help?...If no, <edit<undo Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Luke" wrote in message ... Is there a way to have contents of row A placed in row B without the blank cells and without sorting? 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
|
|||
|
|||
remove blank cells - no sorting
Yes that helps. However I was hoping for a formula to place in row B then
fill down because there are so much data and as the new data arives it gets cumbersome to manually copy.., then pasting also messes with fomulas in other cells that read row B. Does that make sense? "Ron Coderre" wrote: If I understand you correctly, you want to copy the Col_A values and paste them over the Col_B values...but you don't want the blanks in Col_A to write over values in Col_B. If that's correct, try this: Using your example Copy the Col_A values (A1:A9) Select cell B1 From the Excel Main Menu: <edit<paste special ...Check: Values ...Check: Skip blanks Click [OK] Press the [ESC] key to end cut/copy mode. Does that help?...If no, <edit<undo Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Luke" wrote in message ... Is there a way to have contents of row A placed in row B without the blank cells and without sorting? 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
remove blank cells - no sorting
Sorry, Luke...I misunderstood your request.
See if this helps: Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER, instead of just ENTER, in cell.... B1: =IF(COUNT($A$1:$A$10)=ROWS($1:1), INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10<"", ROW($A$1:$A$10)),ROWS($1:1))),"") Copy B1 and paste into B2 through B10 That formula lists each item from Col_A, in the order encountered and without blanks. Adjust range references to suit your actual situation. Note: If Col_A might contain text that you want to list, use COUNTA, instead of COUNT. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Luke" wrote in message ... Yes that helps. However I was hoping for a formula to place in row B then fill down because there are so much data and as the new data arives it gets cumbersome to manually copy.., then pasting also messes with fomulas in other cells that read row B. Does that make sense? "Ron Coderre" wrote: If I understand you correctly, you want to copy the Col_A values and paste them over the Col_B values...but you don't want the blanks in Col_A to write over values in Col_B. If that's correct, try this: Using your example Copy the Col_A values (A1:A9) Select cell B1 From the Excel Main Menu: <edit<paste special ...Check: Values ...Check: Skip blanks Click [OK] Press the [ESC] key to end cut/copy mode. Does that help?...If no, <edit<undo Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Luke" wrote in message ... Is there a way to have contents of row A placed in row B without the blank cells and without sorting? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Blank Cells | Excel Discussion (Misc queries) | |||
Sorting with blank cells | Excel Discussion (Misc queries) | |||
Sorting and Blank Cells | Excel Worksheet Functions | |||
how to remove blank cells in excel | Excel Discussion (Misc queries) | |||
Remove blank cells from a range | Excel Discussion (Misc queries) |