Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to cope with duplicates
Hi.
You could use this conditional formatting formula to highlight the duplicates on a column, in this case column A. Go to Format-Conditional Formatting-Condition1, choose Formula Is, and paste this formula: =IF(COUNTIF($A:$A,A1)=1,FALSE,NOT(COUNTIF($A$1:A1, A1)=1)) -- Alex *Remember to click "yes" if this post helped you. Thank you! "KayeNightingale" wrote: We have a list of Books; each Book has a Unique ISBN Number but, unfortunately the same Book can be listed twice so we are struggling to write a suitable VLOOKUP formula. The VLOOKUP correctly finds the first occurence of an entry with that ISBN number but how can we deal with finding the next occurence of the same ISBN number? There are 5000 rows of data and a lot of duplicates so this is causing a huge amount of manual editing each Quarter. Can anyone else please? KayeNightingale |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to cope with duplicates
Remember to set the format to any color you wish.
-- Alex *Remember to click "yes" if this post helped you. Thank you! "alexrs2k" wrote: Hi. You could use this conditional formatting formula to highlight the duplicates on a column, in this case column A. Go to Format-Conditional Formatting-Condition1, choose Formula Is, and paste this formula: =IF(COUNTIF($A:$A,A1)=1,FALSE,NOT(COUNTIF($A$1:A1, A1)=1)) -- Alex *Remember to click "yes" if this post helped you. Thank you! "KayeNightingale" wrote: We have a list of Books; each Book has a Unique ISBN Number but, unfortunately the same Book can be listed twice so we are struggling to write a suitable VLOOKUP formula. The VLOOKUP correctly finds the first occurence of an entry with that ISBN number but how can we deal with finding the next occurence of the same ISBN number? There are 5000 rows of data and a lot of duplicates so this is causing a huge amount of manual editing each Quarter. Can anyone else please? KayeNightingale |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to cope with duplicates | Excel Discussion (Misc queries) | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
Cope cells | Excel Discussion (Misc queries) | |||
Is there a way MATCH() can cope with duplicate values? | Excel Worksheet Functions |