I am trying to write a formula to detect all duplicates on the list of Column A, so that if an item appears on the list for the first time in Column A, column B shows "N/A" but if it appears on the list of Column A a SECOND time, then column B will list the item as shown in column A.
Hi jrc and welcome to the forum,
It sounds like you need a helper column that counts the number of times something appears in Column A. See the attached with formulas to see if this solves your problem.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
see if this works for you
=IF( COUNTIF(A:A, A1) =1, "N/A", IF(COUNTIF(A:A, A1)>1, A1, ""))
OR if you only want the 2nd time to show
=IF( COUNTIF(A:A, A1) =1, "N/A", IF(COUNTIF(A:A, A1)=2, A1, ""))
and not the 3rd or more times
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
A different approach from above, this will return the last instance that the value appeared (if at all) within Column A.
Input into B1, next to the start of the data in A1:
Please Login or Register to view this content.
Spread the love, add to the Rep
"None of us are as smart as all of us."
ORRR, if you only want duplicates beyond the first to show, in B2 copied down
=IF(COUNTIF($A$2:$A2,A2)>1, A2, "N/A")
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks