Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula to return all non-blank values
Hello,
I am trying to figure out an excel formula to help me return all non- blank values from one column into another. I'd like it to look like the below example. All values from column A listed sequentially in Column B with no blank spaces. A B 1 TEST1 TEST1 2 TEST2 3 TEST2 TEST 4 BTEST 5 NOSE 6 TEST 7 8 BTEST 9 10 NOSE Additional information: The information in column A is text and is referenced from another sheet and will change from time to time. The results in Column B should also update. I've tried multiple IF statements but that just gets messy very quickly. Plus, it still didn't do what I needed it to. I would appreciate any and all help you can give!!! Thank you!! Matt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula to return all non-blank values
In Cell B1 enter: (this is a single line formula) it must be entered
simultaneouly with the Control Key + Shift + Enter key. =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))) " wrote: Hello, I am trying to figure out an excel formula to help me return all non- blank values from one column into another. I'd like it to look like the below example. All values from column A listed sequentially in Column B with no blank spaces. A B 1 TEST1 TEST1 2 TEST2 3 TEST2 TEST 4 BTEST 5 NOSE 6 TEST 7 8 BTEST 9 10 NOSE Additional information: The information in column A is text and is referenced from another sheet and will change from time to time. The results in Column B should also update. I've tried multiple IF statements but that just gets messy very quickly. Plus, it still didn't do what I needed it to. I would appreciate any and all help you can give!!! Thank you!! Matt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula to return all non-blank values
Thank you very much! It worked great! I just had to make the first
part =IF(ROW()-ROW(BlanksRange)... instead of NoBlanksRange. Thanks again!! Matt On Feb 14, 4:01*pm, Jim May wrote: In Cell B1 enter: *(this is a single line formula) it must be entered simultaneouly with the Control Key + Shift + Enter key. =IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),""*,INDIRECT(ADDRESS(SMALL ((IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(B la*nksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4))) " wrote: Hello, I am trying to figure out an excel formula to help me return all non- blank values from one column into another. *I'd like it to look like the below example. *All values from column A listed sequentially in Column B with no blank spaces. * * * * A * * * * * * * * * * * * B 1 * *TEST1 * * * * * * * *TEST1 2 * * * * * * * * * * * * * * *TEST2 3 * TEST2 * * * * * * * * TEST 4 * * * * * * * * * * * * * * *BTEST 5 * * * * * * * * * * * * * * *NOSE 6 * TEST 7 8 * BTEST 9 10 *NOSE Additional information: The information in column A is text and is referenced from another sheet and will change from time to time. *The results in Column B should also update. I've tried multiple IF statements but that just gets messy very quickly. Plus, it still didn't do what I needed it to. I would appreciate any and all help you can give!!! *Thank you!! Matt- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return a true blank | Excel Worksheet Functions | |||
IF function to return values for cells with blank or - in them | Excel Worksheet Functions | |||
Formula to return blank | Excel Worksheet Functions | |||
Solution for charting IF statements that return blank values | Charts and Charting in Excel | |||
Return blank cell using a formula | New Users to Excel |