Delete duplicate values in a column

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
Hi,

I have a column of data where duplicate records appear, can I get a macro to review the column and delete the duplicate record, I need the line item number to still show although the serial number has been deleted, please see example below-

line_number Serial_number
1 4FM12A1QWINC-1A
1 4FM12A1QWINC-1B
1 4FM12A1QWINC-1C
1 4FM12A1QWINC-1D
1 4FM12A1QWINC-1E
1 4FM12A1QWINC-1F
1 4FM12A1QWINC-1G
1 4FM12A1QWINC-1H
1 4FM12A1QWINC-1I
1 4FM12A1QWINC-1J
1 4FM12A1QWINC-1J delete serial number in column B, keep line number 1 in column A
2 4FM12A1QWINC-2A
2 4FM12A1QWINC-2B
2 4FM12A1QWINC-2C
2 4FM12A1QWINC-2D
2 4FM12A1QWINC-2E
2 4FM12A1QWINC-2E delete serial number in column B, keep line number 2 in column A
3 4FM12A1QWINC-3A
3 4FM12A1QWINC-3A delete serial number in column B, keep line number 3 in column A
4 4FM12A1QWINC-4A
4 4FM12A1QWINC-4A delete serial number in column B, keep line number 4 in column A

Any help would be greatly appreciated,
thanks
Steve
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
so I take it the data you provide isn't complete? You have other data like your explanation says in column B. So if the data in column A is found in column B, it would be on the same line? And if so you want to clear the data in column B where it matches?
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
Hi,
Thanks for the replies,

The file actually consists of 20+ columns of data, each row of data has a line item number in column A which will appear on more than one line, so eg. the first 10 rows could have line item 1 against them in col A, a further 5 rows of data will have line item number 2 in col A, etc.

Yesterday, Norie provided some code which then adds a header record at each change in line number in col A, the header record is an exact copy of the line above, so from 10 rows of data with line item number 1, we now get one more, ie 11 rows, for item number 2, we started with 5 rows then get a 6th added.

The header record needs to have the serial number deleted (actually in column Q), all other data on the new row remains untouched.

I can't work out how to adapt Norie's code to delete the serial number so wanted to add furhter code which then looks down the serial number, deletes the first duplicate where the item number is 1, then deletes the first duplicate serial number where th eitem number is 2 etc etc.

I nned the header record to upload the data into an accounting system.

Hope this helps.

thanks
Steve
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281

ADVERTISEMENT

Hi Adam,

Thanks for the link, there's a lot of interesting stuff.
I had a look and found a piece of code that nearly fits, but not quite.

The code I found deletes duplicate rows whereas I need to keep the duplicated row, but only delete one cell from the row.

Any ideas ?

thanks
Steve
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281

ADVERTISEMENT

Hi, tried the delete cells macro and it wouldnt work, fell over on
NumRows = rngSrc.Rows.Count

This is the code that I got given yesterday, it works great in that it creates a duplcate row everytime the line number changes, but I couldnt work out how to get it to delete the serial number in column Q - I thought the workaround would be to add the code you suggested but as I said, it didnt work.

Dim Rng As Range

Set Rng = Range("a2")

While Rng.Value <> ""
If Rng.Value <> Rng.Offset(1).Value Then
Rng.Offset(1).EntireRow.Insert
Rng.EntireRow.Copy Rng.Offset(1)


Set Rng = Rng.Offset(1)

End If
Set Rng = Rng.Offset(1)
Wend



End Sub
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
Hi,
I got the delete duplicate cells code to work but unfortunately, it clears the duplicates but then moves the rows up so that the list then becomes out of sync, the macro needs to leave an empty cell as opposed to deleting the cell.

Hope you understand what I mean

thanks
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
Just had a look at the code and I deleted the "delete" piece so it now works ok.
If you have time to try to improve the original code then gtreat, otherwise I should be ok with what i've got a l though it's not necessarily the most efficient way of doing it.

thanks for your help
 

Swaroon

Active Member
Joined
Nov 18, 2005
Messages
281
Just had a look at the code and I deleted the "delete" piece so it now works ok.
If you have time to try to improve the original code then gtreat, otherwise I should be ok with what i've got a l though it's not necessarily the most efficient way of doing it.

thanks for your help
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,527
Members
425,480
Latest member
br400821

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