Searching a column of cells and reporting results in a list

**utahmoose** · 06-14-2013, 05:04 PM

I am trying to figure out how to search a column for matching text and then report another value depending on whether it matches. For example (if "=" just means its the next cell on the row):

COLORS = LETTERS
blue = A
red = B
green = A
yellow = D
blue = E
purple = C
blue = A

For every cell, I'll be looking for blue. Blue can equal "A" or "E" or however many more letters. So I want to search the entire column of colors and wherever it is "blue", I want it to report, in a list, the corresponding letter on that row, such "A, E".
This might be looking too far ahead, but I would also like to figure out a way to not duplicate letters, so if two rows were blue and both corresponded with "a", it wouldn't list that as "A, E, A" and rather just "A, E".

Thanks!

**Craig K.** · 06-14-2013, 05:30 PM

From what you're describing, you're better off just using a pivot table. Highlight your data --> create pivot table --> make "COLORS" your first Row Label/Field and "LETTERS" your second Row Label/Field.

**Harribone** · 06-14-2013, 05:31 PM

Try the attached file, explainations given for each part are on the sheets.
This should give the the basic starting point for your work. Or you can use a Pivot Table (select your data and choose insert Pivot Table, use false data and give it a go if you've never used one before).

If you need more help though let me know.

**utahmoose** · 06-14-2013, 07:14 PM

Whew, pivot table is waaaaay easier. Might have to do some finagling to make it work the way I need it to elsewhere, but that's definitely a good start. Thanks!!!!