Removing Duplicates

Hi Freinds,

I required to remove duplicates in work item code based on Party name in sheet 1 using frequency .

Punnam

Hi Nisha Dhawan ,

My requirement is in sheet3 .Please check it once again

Punnam

Hi

Bump. Need help

Punnam

Sheet3 C3 cell

=IF(COLUMNS($C$3:C3)<=COUNT(1/FREQUENCY(IF(Sheet1!$A$8:$A$50=$B3,IF(Sheet1!$B$8:$B$50<>"",MATCH(Sheet1!$B$8:$B$50,Sheet1!$B$8:$B$50,0))),ROW(Sheet1!$B$8:$B$50)-ROW(Sheet1!$B$8)+1)),INDEX(Sheet1!$B$8:$B$50,SMALL(IF(FREQUENCY(IF(Sheet1!$A$8:$A$50=$B3,MATCH(Sheet1!$B$8:$B$50,Sheet1!$B$8:$B$50,0)),ROW(Sheet1!$B$8:$B$50)-ROW(Sheet1!$B$8)+1),ROW(Sheet1!$B$8:$B$50)-ROW(Sheet1!$B$8)+1),COLUMNS($C$3:C3))),"")

Entered with Ctrl+Shift+Enter then drag right then pull down.

Note:If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself.

hi Ali Kırksekiz ,

Thank you for the solution it worked fine, can you expalin me in detail the frequency part in more details
1)

Formula:

=IF(COLUMNS($C$3:C4)=COUNT(1/FREQUENCY(IF(Sheet1!$A$8:$A$50=$B4,IF(Sheet1!$B$8:$B$50<>"",MATCH(Sheet1!$B$8:$B$50,Sheet1!$B$8:$B$50,0))),ROW(Sheet1!$B$8:$B$50)-ROW(Sheet1!$B$8)+1))

,
Why this part for formula is required ? Please explain
Punnam

First section counts unique values. According your example for Veda Prakesh, There are three different value. If ones of them are same value then frequency formula doesn’t counts same value. The formula skip same value and counts unique .

For example Veda Prakesh’s value 11;13 ;15;16
Occurrence counts are Veda Prakesh to IF generate a bunch FREQUENCY(1\1\1\1 …{1;1;1;;1;FALSE;FALSE;FALSE} COUNT formula gives 4,
Veda Prakesh’s value 11;13 ;13;16 FREQUENCY(1\1\0\1 {1;1;0;1;FALSE;FALSE;FALSE} COUNT formula gives 3

If you want to learn how to evaluate formula then On the Formulas tab, in the Formula Auditing group, click Evaluate Formula. Click until each part of the formula has been evaluated.