Deduplication of data

garymu16

Board Regular
Joined
Sep 15, 2006
Messages
139
Can anyone suggest how I could quickly deduplicate a range of data where column A is a project code & column B is a value?

What I would like to do is remove all instances whereby the project code appears more than once (over 15k lines). The value in column B will remain constant thru' the project code.

Many thx in advance all!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows
garymu16

If a project code appears in column A several times, are they all together in a group of rows or scattered down column A?
And just confirm whether you want to keep one instance of that project code or delete them all?
 

garymu16

Board Regular
Joined
Sep 15, 2006
Messages
139
Hi Peter,

The project codes would be scattered down column A. There may be one instance there may be multiple instances.

Also, I would like to keep one instance visible.

Many thx

Gary
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows
Gary

See if this is any use.

1. In C2 (copied down): =COUNTIF(A$2:A2,A2)
2. Select column C by clicking its column heading.
3. Data|Filter|AutoFilter
4. Click the AutoFilter drop-down in C1 and choose (Custom)
5. In the top left box choose 'Is greater than' and in the top right box choose 1|OK
6. Select the remaining visible rows (the duplicates) and Edit|Delete Row|OK
7. Data|Filter|AutoFilter to remove the AutoFilter and display the unique list.
8. Delete column C

Here it is after step 1
Mr Excel.xls
ABCD
1Project CodeValueDup
2ABC441
3DEF71
4GHI91
5ABC442
6ABC443
7JKL21
8DEF72
9
Delete Duplicates
 

Forum statistics

Threads
1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw

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
Top