| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display a 1 for the first of duplicates
I have a list of records that have many duplicates. I need a formula that I
can use in the adjacent column to show a 1 for the first instance of a duplicate and a 0 for any others. The list is sorted so all dupes are together. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display a 1 for the first of duplicates
Try this formula:
=--(MATCH(A2,A:A,0)=ROW(A2)) and drag it down as required! Regards, Stefi €˛noelle€¯ ezt Ć*rta: I have a list of records that have many duplicates. I need a formula that I can use in the adjacent column to show a 1 for the first instance of a duplicate and a 0 for any others. The list is sorted so all dupes are together. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display a 1 for the first of duplicates
Use this formula in cell B3:
=IF(COUNTIF(C$3:C3, C3)=2, 1, 0) Let me know if it works. "noelle" wrote: I have a list of records that have many duplicates. I need a formula that I can use in the adjacent column to show a 1 for the first instance of a duplicate and a 0 for any others. The list is sorted so all dupes are together. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display a 1 for the first of duplicates
Note that the first solution above will flag the first occurence of each
value (duplicate or otherwise); the second solution will flag the second occurence (=the first duplicate, literally). Choose the one that actually matches your need. -- Adam Vero MCP, MOS Master, MLSS, CWNA http://veroblog.wordpress.com http://www.meteorit.co.uk "Excel_Learner" wrote: Use this formula in cell B3: =IF(COUNTIF(C$3:C3, C3)=2, 1, 0) Let me know if it works. "noelle" wrote: I have a list of records that have many duplicates. I need a formula that I can use in the adjacent column to show a 1 for the first instance of a duplicate and a 0 for any others. The list is sorted so all dupes are together. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display a 1 for the first of duplicates
Great, thanks. That worked!
Would you mind explaining the components so I understand how it works? It's really been bugging me that I can't figure this out! Thanks again. "Stefi" wrote: Try this formula: =--(MATCH(A2,A:A,0)=ROW(A2)) and drag it down as required! Regards, Stefi €˛noelle€¯ ezt Ć*rta: I have a list of records that have many duplicates. I need a formula that I can use in the adjacent column to show a 1 for the first instance of a duplicate and a 0 for any others. The list is sorted so all dupes are together. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to display remaining txt file which overflowed MsgBox display? | Excel Discussion (Misc queries) | |||
| Display every 3rd category name but still display latest month | Charts and Charting in Excel | |||
| Can I display an Excel chart as my screensaver display? | Charts and Charting in Excel | |||
| Duplicates | Excel Discussion (Misc queries) | |||
| Numbers display as decimal, i.e. enter 123 display 1.23 | Setting up and Configuration of Excel | |||
Linear Mode
