Return a list of text values without blank cells from a column

MDSad

New Member
Joined
Nov 17, 2016
Messages
2
[FONT=&quot]I have 6 columns:[/FONT]
[FONT=&quot]A=code,[/FONT]
[FONT=&quot]B=color (drop down list),[/FONT]
[FONT=&quot]C=checked(1) - only "+",[/FONT]
[FONT=&quot]D=checked(2) - only "+", [/FONT]
[FONT=&quot]E=A and B,[/FONT]
[FONT=&quot]F=show E, which checked(1) only.[/FONT]
[FONT=&quot]It looks like this:[/FONT]
[FONT=&quot] A B C D E F[/FONT]
[FONT=&quot]123 black + + 123 black [/FONT]
[FONT=&quot]456 yellow + 456 yellow 456 yellow[/FONT]
[FONT=&quot]123 green + + 123 green[/FONT]
[FONT=&quot]789 green + + 789 green[/FONT]
[FONT=&quot]789 yellow + 789 yellow 789 yellow [/FONT]
[FONT=&quot]Formula for E is =CONCATENATE(A2;" ";B2)[/FONT]
[FONT=&quot]Formula for F is =IF(AND(C2="+";NOT(D2="+"));E2;"")[/FONT]
[FONT=&quot]I need column G, in this column will be all F values without blank cells. [/FONT]
[FONT=&quot]It must be look like this:[/FONT]
[FONT=&quot] A B C D E F G[/FONT]
[FONT=&quot]123 black + + 123 black 456 yellow[/FONT]
[FONT=&quot]456 yellow + 456 yellow 456 yellow 789 yellow[/FONT]
[FONT=&quot]123 green + + 123 green[/FONT]
[FONT=&quot]789 green + + 789 green[/FONT]
[FONT=&quot]789 yellow + 789 yellow 789 yellow [/FONT]
[FONT=&quot]I tried this formula: [/FONT]
[FONT=&quot]=IFERROR(SMALL(IF(ISBLANK($F$1:$F$1000);"";$F$1:$F$1000);ROW(A1));"")[/FONT]
[FONT=&quot]but it is not working. I think that's because blank cells in F is not actually blank, but I don't know what to do.[/FONT]
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
In J1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($F$1:$F$1000,SMALL(IF(1-($F$1:$F$1000=""),ROW($F$1:$F$1000)-ROW($F$1)+1),ROWS($J$1:J1))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,232
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top