remove same number on same column on the PO

kewlbleu22 · June 15th, 2010, 03:16 PM

Hello friends,
I have an excel spreadsheet with multiple Purchase order numbers. Each PO has multi of the same numbers on the same column ( I ) . How do I remove the same number out and leave only one number per PO with deleting the rows. I just need to remove the same number from the PO. ex. each PO has five of the same number on column I , I need to find a way to remove four of the five of those numbers in Ms Excel 2007. A macro would be great. Thanks in advance -K

gbianchi · June 15th, 2010, 03:53 PM

Hello..

So you start with any column, read the PO, then read the I column. Go to next row, if the Po is the same delete value on cell on I column. You repeat this process until the PO change. When it change you start the process on I column again. Any problem coding this?

kewlbleu22 · June 15th, 2010, 04:03 PM

can you help me with the VB code? as PO# is on Column A and the same numbers on column I

Thanks in advance

gbianchi · June 15th, 2010, 04:05 PM

I can't make it for, I can help you if you have any problem with it.

Shasur · June 15th, 2010, 10:17 PM

Hi

Can you check if the followng is of any help:

http://vbadud.blogspot.com/2008/07/r...eet-using.html

http://vbadud.blogspot.com/2007/08/d...excel-vba.html

Cheers
Shasur

kewlbleu22 · June 15th, 2010, 11:39 PM

Thanks Shasur. What I need is the code to go into each PO and delete the multiple of the same number on the same PO. Please see below. So it needs to delete the phone# column for the duplicate one. Hope you can help thanks all.

PO phone# to be like this >> PO phone#
---- -------- --- -------
222 555-1237 222 555-1237
222 555-1233 222 555-1233
222 555-1233 222

223 555-1233 223 555-1233
223 555-1234 223 555-1234
223 555-1233 223

Shasur · June 17th, 2010, 03:31 AM

Hi

Try the following code:

Code:

Sub Remove_Dupe_Row_Data()
Dim iMax As Long            ' Long M
Dim oWS As Worksheet            ' Sheet
Dim oFnd As Range
Dim sText As String
Dim i1 As Long

Set oWS = ActiveSheet
iMax = oWS.Cells.SpecialCells(xlCellTypeLastCell).Row
For i1 = 2 To iMax
    sText = oWS.Cells(i1, 4).Value
    Set oFnd = oWS.Range(oWS.Cells(1, 4), oWS.Cells(i1 - 1, 4)).Find(sText)
    If Not oFnd Is Nothing Then
        oWS.Cells(i1, 4).Value = "" ' Set the Fourth Column Value as Blank
    End If
Next i1
    

End Sub

It works for Fourth column . It makes the duplicate cells as blank.

Cheers
Shasur