Create a list based on the value of a cell

[jc352]

I am trying to create a list in row E based on the information in row C. If C=Blue I want the it in the list. If something repeats I want it included in the new list.

The new list in in row E would be 1234, 1234 and 8518

A	B	C
1234	Jones, Sam	Blue
1234	Jones, Sam	Blue
8341	Brown, Adam	Green
8518	Smith, John	Blue
6541	Mills, Dan	Orange

<tbody>
</tbody>

 

[jc352]

Would it be possible to adjust the formula to include anthing with word 'blue' in it from column C? Still listing the following values from Column A (1234, 1234, 8518) in Column E.

A	B	C
1234		Blue
1234		Blue-LT
8341		Green
8518		Blue-DK
6541		Orange

<TBODY>
</TBODY>

 

[Aladin Akyurek]

Would it be possible to adjust the formula to include anthing with word 'blue' in it from column C? Still listing the following values from Column A (1234, 1234, 8518) in Column E.

A	B	C
1234		Blue
1234		Blue-LT
8341		Green
8518		Blue-DK
6541		Orange

<tbody>
</tbody>

E1,just enter:

=COUNTIF($C$2:$C$6,"*Blue*")

E2, control+shift+enter, not just enter:

=IF(ROWS(E$2:E2)<=E$1,INDEX($A$2:$A$6,
  SMALL(IF(ISNUMBER(SEARCH("Blue",$C$2:$C$6)),
  ROW($A$2:$A$6)-ROW($A$2)+1),ROWS(E$2:E2))),"")

 

[jc352]

Thank you for your help with this. I have one more request. Is it possible to adjust the formula to have the return in Column E be based on how the items in Column C fall alphabetically? So Column E would read 1234, 8518 and 1234 based on Blue, Blue-DK and Blue-LT.

 

[jc352]

Bump

 

[Aladin Akyurek]

Thank you for your help with this. I have one more request. Is it possible to adjust the formula to have the return in Column E be based on how the items in Column C fall alphabetically? So Column E would read 1234, 8518 and 1234 based on Blue, Blue-DK and Blue-LT.

Well, why not indeed?..

X		Y		Blue		#Sorted#
1234		Blue		3		Blue
1234		Blue-LT		Result List	Idx	Blue-DK
8341		Green		1234	1	Blue-LT
8518		Blue-DK		8518	2	Green
6541		Orange		1234	3	Orange

<colgroup><col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2844;" width="80"> <col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3185;" width="90"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;" width="94"> <col style="width: 48pt;" width="64"> <col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3697;" span="2" width="104"> <col style="width: 75pt; mso-width-source: userset; mso-width-alt: 3555;" width="100"> <tbody>
</tbody>

E1: Blue (a search string of interest)

E2, just enter:

=COUNTIF($C$2:$C$6,"*"&E$1&"*")

E4, just enter and copy down:

=IF($F4="","",INDEX($A$2:$A$6,MATCH(INDEX($G$2:$G$6,$F4),$C$2:$C$6,0)))

F4, control+shift+enter, not just enter, and copy down:

=IF(ROWS(F$4:F4)<=E$2,SMALL(IF(ISNUMBER(SEARCH(E$1,$G$2:$G$6)),
  ROW($G$2:$G$6)-ROW($G$2)+1),ROWS(E$4:E4)),"")

G1: #Sorted#

G2, control+shift+enter, not just enter, and copy down:

=IF(ROWS(G$2:G2)<=ROWS($C$2:$C$6),INDEX($C$2:$C$6,
  MATCH(0,COUNTIF($C$2:$C$6,"<"&$C$2:$C$6)-(SUM(IF($C$2:$C$6="",1))>0)-
  SUM(COUNTIF($C$2:$C$6,"="&G$1:G1)),0)),"")

Note 1. I hope the latter formula is accepted by a pre-2007 system like yours.

Note 2. This fomula modifies a sorting formula in an important way for empty/blank cells.

 

[Smergen]

Just wanted to bump this thread and say thanks. This is a really neat solution to a problem that I was trying to solve. It's been a while since being on the forum but Mr Excel does it again...