Delete duplicate values in a column

[Swaroon]

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

 

[ADAMC]

Hi

See http://www.cpearson.com/excel/Duplicates.aspx for details on duplicates any problems post back.

 

[texasalynn]

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]

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]

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

 

[ADAMC]

Try:

http://exceltips.vitalnews.com/Pages/T0189_Removing_Duplicate_Cells.html

Or post the code you have and we can see about adapting it.

 

[Swaroon]

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]

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]

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]

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