Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Eliminating blank cells in a list on a ROW
I need help converting Pearson's array formula from a list in a column to a list in a row: http://www.cpearson.com/excel/noblanks.htm =IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)- COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))), ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) Thanks in advance! -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=481884 |
#2
|
|||
|
|||
Eliminating blank cells in a list on a ROW
How odd! The second question today about this formula
=IF(COLUMN()-COLUMN(NoBlanksRange)+1COLUMNS(BlanksRange)-COUNTBLANK(BlanksR ange),"", INDIRECT(ADDRESS(ROW(BlanksRange),SMALL((IF(Blanks Range<"",COLUMN(BlanksRan ge),COLUMN()+COLUMNS(BlanksRange))),COLUMN()-COLUMN(NoBlanksRange)+1),ROW(Bl anksRanges),4))) -- HTH RP (remove nothere from the email address if mailing direct) "grime" wrote in message ... I need help converting Pearson's array formula from a list in a column to a list in a row: http://www.cpearson.com/excel/noblanks.htm =IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)- COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))), ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) Thanks in advance! -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=481884 |
#3
|
|||
|
|||
Eliminating blank cells in a list on a ROW
Try...
B1, copied across: =IF(COLUMNS($B1:B1)<=ROWS($A$1:$A$7)-COUNTBLANK($A$1:$A$7),INDEX($A$1:$A$ 7,SMALL(IF($A$1:$A$7<"",ROW($A$1:$A$7)-ROW($A$1)+1),COLUMNS($B1:B1))),"" ) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , grime wrote: I need help converting Pearson's array formula from a list in a column to a list in a row: http://www.cpearson.com/excel/noblanks.htm =IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)- COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))), ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) Thanks in advance! |
#4
|
|||
|
|||
Eliminating blank cells in a list on a ROW
Bob and Dom, Thanks for the reply, but neither formula did the trick. I entered both in as array formulas, and Bob, I even changed your BlanksRanges to BlanksRange. To summarize again, I have data in a range A1:M1 that contains blanks and I want the list from row 1 into row 2 (A2:M2) without blanks. Thanks again. -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=481884 |
#5
|
|||
|
|||
Eliminating blank cells in a list on a ROW
In my test I had to use different names as I had already used the other two
in the previous question, and did correct them all in the posting, but that apart, I tested it and it did work. Did you commit it with Ctrl-Shift-Enter? -- HTH RP (remove nothere from the email address if mailing direct) "grime" wrote in message ... Bob and Dom, Thanks for the reply, but neither formula did the trick. I entered both in as array formulas, and Bob, I even changed your BlanksRanges to BlanksRange. To summarize again, I have data in a range A1:M1 that contains blanks and I want the list from row 1 into row 2 (A2:M2) without blanks. Thanks again. -- grime ------------------------------------------------------------------------ grime's Profile: http://www.excelforum.com/member.php...o&userid=19227 View this thread: http://www.excelforum.com/showthread...hreadid=481884 |
#6
|
|||
|
|||
Eliminating blank cells in a list on a ROW
Try the following instead...
A2, copied across: =IF(COLUMNS($A2:A2)<=COLUMNS($A$1:$M$1)-COUNTBLANK($A$1:$M$1),INDEX($A$1: $M$1,SMALL(IF($A$1:$M$1<"",COLUMN($A$1:$M$1)-COLUMN($A$1)+1),COLUMNS($A2 :A2))),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , grime wrote: Bob and Dom, Thanks for the reply, but neither formula did the trick. I entered both in as array formulas, and Bob, I even changed your BlanksRanges to BlanksRange. To summarize again, I have data in a range A1:M1 that contains blanks and I want the list from row 1 into row 2 (A2:M2) without blanks. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbered List with Blank Spaces, etc. | Excel Discussion (Misc queries) | |||
Blank cells represented in area charts | Charts and Charting in Excel | |||
removing blank cells from a column | Excel Discussion (Misc queries) | |||
Ignore Blanks in Data Validation | Excel Worksheet Functions | |||
How can I make the graph omit blank cells in the data set? | Charts and Charting in Excel |