You are here:

# Excel/Excel finding all first occurances

Question
Dear Tom,

I have a query that I have been searching the internet to find the answer to but to no avail. You have helped me once before so I hope you are able to help me again.

I want to create a formula which will look at column A to find the first occurance of each ID number and then examine column B.

I am using Excel 2003.

Below is an example table.

ID   Color
123   Red
132   Blue
444   Red
132   Red
122   Yellow
444   Blue
444   Green
111   Red
122   Blue

I would like a formula for the following:

If the first occurance of ID (in column A) equals red (column B) then "red", If the first occurance of ID equals yellow then "yellow", If first occurance of ID does not equal red or yellow then "other". Subsequent occurances of ID can be ignored.

I hope this makes sense. Please be gentle with me as I am not a very confident Excel user.

Thank you
Grace

Grace,

It is unclear to me where you want the results or how you want to search.  I will assume that
A1:  ID
B1:  Color
A2:  Red
B2:  Blue

so in C2 put in

=IF(COUNTIF(\$A\$2:\$A2,\$A2)=1,IF(B2="Red","Red",IF(B2="Yellow","Yellow","Other")),"")

now drag fill this formula down column C next to your data.

In my test worksheet with test data I generated, I got a result like this:

ID   Color
2222   Green   Other
3333   Yellow   Yellow
3333   Purple
3333   Red
1111   Red   Red
2222   Yellow
1111   Red
3333   White
2222   Green
2222   Blue
1111   Yellow

Hopefully that is what you want.

--
Regards,
Tom Ogilvy
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thank you Tom for your speedy answer to my unclear question. The answer you provided is exactly what I was looking for. This is the second question you have answered for me and both times I have accpreciated the clarity of your response. Many many thanks.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.