CODEWITHSUNDEEP
excelexcel-formula
M. Mill
M. Mill

Reputation: 57

Removal of Duplicate Values - Leading Date

I'm trying to remove duplicate values from my workbook, but the issue is that the same entries, the one I am attempting to keep has the year leading as seen below.

enter image description here

I've tried using a VLOOKUP (below) but since it's not a exact match, I used TRUE, which doesn't return the value I really want and it would take another step to remove the value without the leading year.

=VLOOKUP(F2,F2:F657,1,TRUE)

Any and all help would be appreciated!

Upvotes: 0

Views: 55

Answers (1)

Ralph
Ralph

Reputation: 9444

How about something like this:

{=IFERROR(VLOOKUP(F2,F2:F657,1,FALSE),INDEX(F2:F657,MATCH(F2,SUBSTITUTE(F2:F657,LEFT(F2:F657,7),""),0)))}

This works as follows: (1) first it checks if the vlookup finds a match. If that's not the case then (2) it will try to find a match in the list by removing the first 7 characters (year + space + hyphen + space). So, the above solution assumes that the first 7 characters must be always remove (this solution is not flexible to the length of the characters that need to be removed).

Furthermore, the above solution assumes that you have an Excel version which knows the formula IfError (requires Excel 2007+). Otherwise, you can substitute that with a complete If formula.

Note, that the above formula needs to be entered as an array formula. So, you need to press Ctrl + Shift + Enter to enter the formula. For more information on array formulas have a look at Microsoft's website here: https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

enter image description here

Upvotes: 1

Related Questions