Removing Duplicate Dates Keeping Column With Name

thierry14

New Member
Joined
Dec 6, 2014
Messages
33
Hi All

I have a large sheet with thousands of duplicate dates and names

column A is the date

Column b is the name

01-02-16 Mike
01-02-16 Mike
01-02-16 Mike
01-03-16 Mike
01-03-16 Mike
01-02-16 John
01-02-16 John
01-03-16 John
01-03-16 John

How can I remove the duplicate dates in column A Eg 01-02-16 while keeping the names in column b Eg Mike

Answer should look like below

Duplicate removed date removed corresponding to name in column b

Any vba or formula I can use

01-02-16 Mike
01-03-16 Mike
01-02-16 John
01-03-16 John
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
A pivot table will do the job.
Select the two columns, chose to insert a pivot table, put the date and name columns in the left hand side and some basic count of name or date in the values section. Run the pivot and you have all the unique names and date combinations shown. I would remove the totals off the date field and chose "traditional pivot table" layout to exactly match the target format. To make the data more accessable and to eliminate the "values" bit you can use =d1 type formula to pull the data across into new cells.
Hope this helps
Miles
 
Upvote 0
I have a formula solution however it is not the best in term of performance as this formula is known to slow the processing speed on your workbook:


Excel 2010
BCDEF
2DatenameUnique mergeDateName
301-02-16Mike01-02-16 Mike01-02-16Mike
401-02-16Mike01-03-16 Mike01-03-16Mike
501-02-16Mike01-02-16 John01-02-16John
601-03-16Mike01-03-16 John01-03-16John
701-03-16Mike#N/A
801-02-16John#N/A
901-02-16John#N/A
1001-03-16John#N/A
1101-03-16John#N/A
Sheet1



this is the table setup above you have a column for the date and another for the associated person as you have mentioned.

* the 3rd column will produce unique concatenated results between column 1 and column 2
* the 4th and 5th columns are just to split the 3rd column into dates and names

the formulas are below:


Cell Formulas
RangeFormula
D3{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D2,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D4{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D3,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D5{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D4,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D6{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D5,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D7{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D6,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D8{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D7,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D9{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D8,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D10{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D9,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
D11{=INDEX(CONCATENATE($B$3:$B$11," ",$C$3:$C$11),MATCH(0,COUNTIF($D$2:D10,CONCATENATE($B$3:$B$11," ",$C$3:$C$11)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.




Excel 2010
EF
301-02-16Mike
Sheet1
Cell Formulas
RangeFormula
E3=IFERROR(LEFT($D3,FIND(" ",$D3)),"")
F3=IFERROR(RIGHT($D3,LEN($D3)-FIND(" ",$D3)),"")
 
Upvote 0
the VBA solution would be:

Code:
Option Explicit

Sub removedubs()


Dim Rng As Range: Set Rng = Sheet2.Range("b3:c12")


Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes


End Sub

where Rng = the range where the data is located and Array(1,2) reflect that the range is a multi dimensional array (2 columns)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top