Comparing cell values and replacing data

[tudor30]

Comparing cell values and replacing data

I have a set of part numbers that have a version state listed on the same row in the worksheet. Example:

ColumnA ColumnB ColumnC
R155200 __________ D
R155211 __________ B
R155211 __________ C
R168414 __________ A
R168555 __________ C
R168555 __________ D

I need to read the part number in column A and compare it to A - 1(the previous cell in A)

If they are different conitnue to the next cell down in column A.

Else if they are the same I need to compare the value in row C to the value of C - 1. Whichever value in C and C - 1 is lessor determines which A or A - 1 is replaced with " "

After which I will sort the rows to combine all the empty values and then delete those rows.

Please review the code below and provide suggestions where I'm missing a the boat or a better solution if you have one.


Sub RemoveDuplicatePartNumber()

Dim f As Worksheet, y As Long, z As Long
Application.ScreenUpdating = False

y = 2
z = 2

'begin process

'get data
While Not IsEmpty(f.Range("A" & y))
If f.Range(&quot;A&quot; & y) <> (&quot;A&quot; & y - 1) Then
y = y + 1
z = z + 1

ElseIf f.Range(&quot;A&quot; & y) = (&quot;A&quot; & y - 1) Then
If f.Range(&quot;C&quot; & z) > (&quot;C&quot; & z - 1) Then
f.Range(&quot;A&quot; & y - 1) = (&quot; &quot
y = y + 1
z = z + 1

ElseIf f.Range(&quot;C&quot; & z) < (&quot;C&quot; & z - 1) Then
f.Range(&quot;A&quot; & y) = (&quot; &quot
y = y + 1
z = z + 1

Else: IsEmpty (f.Range(&quot;A&quot; & y))

End If
Wend
Application.CutCopyMode = False

End Sub


The final results of the info provided should be:

ColumnA ColumnB ColumnC
R155200 __________ D
_______ __________ B
R155211 __________ C
R168414 __________ A
_______ __________ C
R168555 __________ D


Thanks for the help,
John

 

[BuGlen]

Hi John,

If I read your post correctly, you're trying to select DISTINCT on column 'A' and MAX on column 'C'. If this is the case, I would recommend using Access instead of Excel for this type of function. You can link to the Excel file as a linked table in Access and create an aggregate query that will easily accomplish what you want. You can then export the results to another Excel file if you like.

Hope this helps.

- Glen

Know thy data.