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>

 

[Aladin Akyurek]

The best thing to add to your description is the set of the values that you expect to see in column E. Would you provide those values?

 

[jc352]

I would like to have the values in column a shown in column e based on the value of column c, allowing repeats.

 

[Aladin Akyurek]

I would like to have the values in column a shown in column e based on the value of column c, allowing repeats.

Which values are they? I'm sure it's obvious to you which they are, but not necessarily to the would-be helpers...

 

[jc352]

I see from my original post how this is confusing. Where I said row, I should have said column.

I am trying to create a list in column E using the information in column A based on the information in column C. If the value in C=Blue, I would like the corresponding value in column A in the list in column E. If something repeats I want it included in this list as well.

Based on the above, the list in column 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>

 

[Aladin Akyurek]

I see from my original post how this is confusing. Where I said row, I should have said column.

I am trying to create a list in column E using the information in column A based on the information in column C. If the value in C=Blue, I would like the corresponding value in column A in the list in column E. If something repeats I want it included in this list as well.

Based on the above, the list in column 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>

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

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

 

[jc352]

Thank you, the formula returns #NAME?

 

[Aladin Akyurek]

Thank you, the formula returns #NAME?

You are probably on a pre-2007 system, which does not have the IFERROR function.

E1, just enter:

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

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

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

 

[jc352]

Yes, working on 2003 for a little bit longer. That worked perfectly, thank you for your help!

 

[Aladin Akyurek]

Yes, working on 2003 for a little bit longer. That worked perfectly, thank you for your help!

You are welcome. Thanks for providing feedback.