CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
halma562
halma562

Reputation: 77

Finding duplicate cell values in the same column and returning text in another cell

I'm sure there's a simple explanation for the issue I've got, but I can't seem to find anything anywhere...

I've got a table of data that is currently 70+ rows (but is likely to expand in the future) and I'm trying to identify any duplicate values in one column and output text into a different column like below

Date Job No. Duplicate
13/05/2021 10542 Y
13/05/2021 10674 Y
13/05/2021 10676
13/05/2021 10434 Y
14/05/2021 10436
14/05/2021 10727
14/05/2021 10542 Y
14/05/2021 10729
14/05/2021 10332
15/05/2021 10334
15/05/2021 10335
15/05/2021 10434 Y
15/05/2021 10674 Y

The problem I'm coming across is finding a formula that looks at the Job No. column to see if there are any duplicate values and identifying them in the Duplicate column.

I've tried working through INDEX MATCH formulas, but can't seem to get anything working properly.

Upvotes: 0

Views: 511

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

Supposing that your sample data shown has the header "Date" in A1 and the header "Job No." in B1, delete everything (including the header) from Column C and place this in C1:

=ArrayFormula({"Duplicate";IF(A2:A="",,IF(COUNTIF(B2:B,B2:B)>1,"Y",))})

This will produce the header (which you can change inside the formula as you like) as well as all results.

Upvotes: 1

Related Questions