Identify duplicate titles

[dininaus]

Hi All,

Could someone please assist with the below query as I couldn't figure out the way to do it.

I have two columns(A&B) with document numbers (same numbers exist in column A due to multiple versions- draft, version 1, final etc) and Titles. Some document numbers have same titles, which has to be identified (in a separate column) and also corrected by adding _a,_b and so on.. to the end of the title.

Document number	Existing Titles	Correct Title
N-1000-05-TS-1001	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY
N-1000-05-TS-1001	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY
N-1000-05-TS-1001	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY
N-1000-10-DH-003	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY_A
N-1000-10-DH-003	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY_A
N-5000-40-TP-0024_20	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY_B
N-5000-40-TP-0024_20	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY_B
N-5000-40-TP-0024_20	TC FOR STANDARD SURVEY	TC FOR STANDARD SURVEY_B

<tbody>
</tbody>

Thanks in advance!

 

[Yongle]

This only works if the same document numbers are adjacent to each other

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	Document Number	Title	Formula in B2 copied down
2​	N-1000-05-TS-1001	TC FOR STANDARDS SURVEY	=SUBSTITUTE("TC FOR STANDARDS SURVEY"&IFERROR("_"&CHAR(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2&""))+63),""),"_@","")
3​	N-1000-05-TS-1001	TC FOR STANDARDS SURVEY
4​	N-1000-05-TS-1001	TC FOR STANDARDS SURVEY
5​	N-1000-10-DH-003	TC FOR STANDARDS SURVEY_A
6​	N-1000-10-DH-003	TC FOR STANDARDS SURVEY_A
7​	N-5000-40-TP-0024_20	TC FOR STANDARDS SURVEY_B
8​	N-5000-40-TP-0024_20	TC FOR STANDARDS SURVEY_B
9​	N-5000-40-TP-0024_20	TC FOR STANDARDS SURVEY_B
10​
11​

Sheet: Sheet10

 

[Yongle]

another way to write the formula in B2

="TC FOR STANDARDS SURVEY"&SUBSTITUTE(IFERROR("_"&CHAR(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2&""))+63),""),"_@","")


Logic of both formulas

count unique values in column A from A2 to current row
SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2&"")

add 63 to that count

convert that value to alpha (note - this formula only works if there are fewer than 27 unique document numbers in column A)
CHAR(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2&""))+63)

CHAR(64) is @
CHAR(65) - CHAR(90) are alpha characters A - Z

Remove _@ to leave the first document number with title exluding alpha suffix
SUBSTITUTE(IFERROR("_"&CHAR(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2&""))+63),""),"_@","")

 

[dininaus]

Thanks Yongle, I think I didn't explain the requirement well. I have around 600000+ document numbers with different titles. Multiple document numbers exist in a column because of multiple versions of same document. Title's have to be unique for document numbers, if any title duplicate exists in column B we decided to add _a _b_c and so on to make the title unique for that number. Thank you. Please see the result after trying your formula

https://ibb.co/YRSCG9z

 

[Yongle]

In that case, your solution is likely to require array formula (not one of my strengths )