Excel 2013

I'm trying to find the first occurrence of a values in a column (Conflict Name) and then write the result to a new column. Example below:

Conflict Name
LA-Merge Suppliers*Invoice Apply Prepayments
LA-Merge Suppliers*Invoice Payment Schedules
LA-Merge Suppliers*Invoice Payments
LA-Merge Suppliers*Invoices
LA-Merge Suppliers*Payment Invoices
LA-Suppliers*Invoice Apply Prepayments
LA-Suppliers*Invoice Payment Schedules
LA-Suppliers*Invoice Payments
LA-Suppliers*Invoices
LA-Suppliers*Payment Invoices
LA-Merge Suppliers*Invoice Apply Prepayments
LA-Merge Suppliers*Invoice Payment Schedules
LA-Merge Suppliers*Invoice Payments
LA-Merge Suppliers*Invoices
LA-Merge Suppliers*Payment Invoices
LA-Suppliers*Invoice Apply Prepayments
LA-Suppliers*Invoice Payment Schedules
LA-Suppliers*Invoice Payments
LA-Suppliers*Invoices
LA-Suppliers*Payment Invoices
LA-Merge Suppliers*Invoice Apply Prepayments
LA-Merge Suppliers*Invoice Payment Schedules
LA-Merge Suppliers*Invoice Payments
LA-Suppliers*Invoice Apply Prepayments
LA-Suppliers*Invoice Payment Schedules
LA-Suppliers*Invoice Payments
LA-Merge Suppliers*Invoice Apply Prepayments
LA-Merge Suppliers*Invoice Payment Schedules
LA-Merge Suppliers*Invoice Payments
LA-Merge Suppliers*Invoices
LA-Merge Suppliers*Payment Invoices
LA-Suppliers*Invoice Apply Prepayments
LA-Suppliers*Invoice Payment Schedules
LA-Suppliers*Invoice Payments
LA-Suppliers*Invoices
LA-Suppliers*Payment Invoices
LA-Merge Suppliers*Purchase Orders
LA-Merge Suppliers*Releases
LA-Suppliers*Purchase Orders
LA-Suppliers*Releases
LA-Merge Suppliers*Purchase Orders
LA-Merge Suppliers*Releases
LA-Suppliers*Purchase Orders
LA-Suppliers*Releases
LA-Invoice Apply Prepayments*Payment Invoices
LA-Invoice Apply Prepayments*Payment Invoices
LA-Invoice Apply Prepayments*Invoice Payment Schedules
LA-Invoice Apply Prepayments*Invoice Payments
LA-Invoice Apply Prepayments*Invoice Payment Schedules
LA-Invoice Apply Prepayments*Invoice Payments
LA-Invoice Apply Prepayments*Invoice Payment Schedules
LA-Invoice Apply Prepayments*Invoice Payments
LA-Invoice Apply Prepayments*Invoice Payment Schedules
LA-Invoice Apply Prepayments*Invoice Payments
shieldscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

I am not sure if I have addressed your requirements, but I pasted the above list into column [A] of the worksheet within the attached workbook.

In cell [B2] I have placed this formula:
=IF(COUNTIF(A:A,A2)=1,"No Conflict",CHOOSE(COUNTIF(A$2:A2,A2),"First","Second","Third","Fourth","Fifth"))

I have then copied that down column [ B ] to cell [B55].

Finally, I added Conditional Formatting on column [ B ] to highlight the "First" occurrence of each value in column [A].

Please advise if this is anywhere near what you intended.

Thanks.
Q_28710127.xlsx
0
shieldscoAuthor Commented:
What happens if there are 1000 first occurrences?
0
[ fanpages ]IT Services ConsultantCommented:
The first occurrence of each unique value in column [A] will have "First" shown in the corresponding cell in column [ B ].

Second occurrences as "Second", Third as "Third", & I have added "Fourth" & "Fifth" (to match the data you provided).

i was guessing what your requirements may be here, so you may only wish to see "First" on the first occurrence.

"No Conflict" is shown if the value in column [ A ] is unique across the whole of the data range.

If you have 1000 rows of data in the range [A2:A1001], simply copy cell [B2] down column [ B ] until cell [B1001].
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shieldscoAuthor Commented:
How would I indicate True for the first occurrence and False for all other occurences?
0
shieldscoAuthor Commented:
Or maybe see only "First" on the first occurrence.
0
[ fanpages ]IT Services ConsultantCommented:
Which of these two options do you require?

"How would I indicate True for the first occurrence and False for all other occurences?"

In cell [B2], then copy down as appropriate:
=COUNTIF(A$2:A2,A2)=1

Or...
=IF(COUNTIF(A$2:A2,A2)=1,"True","False")

"Or maybe see only "First" on the first occurrence."

Use:
=IF(COUNTIF(A$2:A2,A2)=1,"First","")


Please see the updated attachment.

(It is 2:47am in the UK now, so I will review any further replies in a few hours time)
Q_28710127b.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Great.. Thank You
0
[ fanpages ]IT Services ConsultantCommented:
You're welcome.  Thanks for closing the question so promptly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.