| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
An Array To Sort Blank Cell
I'd like to create an array to place values in column A into Column B and remove blank cell from column A. Example:data in column "A" has blank cell at A2, I'd like to have column "B" show the values and elimate the blank cell. A............B 4............4 blank.......5 5............6 6 Your assistance is appreciated. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=518077 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
An Array To Sort Blank Cell
Hi!
Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER in B1 and copy down until you get blanks: =IF(ROWS($1:1)<=COUNTA(A$1:A$4),INDEX(A$1:A$4,SMAL L(IF(A$1:A$4<"",ROW(A$1:A$4)-ROW(A$1)+1),ROWS($1:1))),"") Biff "travelersway" wrote in message news:travelersway.240pmo_1141261810.6701@excelforu m-nospam.com... I'd like to create an array to place values in column A into Column B and remove blank cell from column A. Example:data in column "A" has blank cell at A2, I'd like to have column "B" show the values and elimate the blank cell. A............B 4............4 blank.......5 5............6 6 Your assistance is appreciated. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=518077 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
An Array To Sort Blank Cell
Biff, Thanks. the data moves to the column that I want, but all the empty cellsare not removed.my actual perameters are C63:C270 column for holding data K168:K374 column for sorted data Does this make a difference? The data in this sort are numbers. I would like to sort two additional columns as well in seperate sorts for: DATES: 1/12 TEXT: name Any help on those? Thank you again. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=518077 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
An Array To Sort Blank Cell
Define an empty cell? Is it totally empty or are there spaces in them?
Did you mean to remove empty cells from the A column itself? No formula can do that! Biff's formula extract and sort only non empty cells but it has no affect on column A itself. You can do that manually by select column A, press F5, click special, select blanks then delete the cells -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "travelersway" wrote in message ... Biff, Thanks. the data moves to the column that I want, but all the empty cellsare not removed.my actual perameters are C63:C270 column for holding data K168:K374 column for sorted data Does this make a difference? The data in this sort are numbers. I would like to sort two additional columns as well in seperate sorts for: DATES: 1/12 TEXT: name Any help on those? Thank you again. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=518077 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
An Array To Sort Blank Cell
See Peo's reply. He summed it up well.
Biff "travelersway" wrote in message ... Biff, Thanks. the data moves to the column that I want, but all the empty cellsare not removed.my actual perameters are C63:C270 column for holding data K168:K374 column for sorted data Does this make a difference? The data in this sort are numbers. I would like to sort two additional columns as well in seperate sorts for: DATES: 1/12 TEXT: name Any help on those? Thank you again. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=518077 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
An Array To Sort Blank Cell
Thank you for your help. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=518077 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) | |||
| Setting Purely BLANK Cell | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
| conditional formatting blank cell | Excel Discussion (Misc queries) | |||
Linear Mode
