try this

Sub getlongest()

StartRow = 1
Sh1Rowcount = 1
Sh2Rowcount = 1
With Sheets("Sheet1")
Do While .Cells(Sh1Rowcount, "A") < ""
If .Cells(Sh1Rowcount, "A") < _
.Cells(Sh1Rowcount + 1, "A") Then

ParentSKU = .Cells(Sh1Rowcount, "A")
Longstr = _
getlongeststr(StartRow, Sh1Rowcount)
With Sheets("Sheet2")
.Cells(Sh2Rowcount, "A") = _
ParentSKU
.Cells(Sh2Rowcount, "B") = _
Longstr
End With
StartRow = Sh1Rowcount + 1
Sh2Rowcount = Sh2Rowcount + 1
End If

Sh1Rowcount = Sh1Rowcount + 1
Loop

End With

End Sub
Function getlongeststr(StartRow, EndRow)

CharacterCount = 1
With Sheets("Sheet1")
Do While (True)

For RowCount = StartRow To (EndRow - 1)
a = Len(.Cells(RowCount, "B"))
b = Len(.Cells(RowCount + 1, "B"))
c = Mid(.Cells(RowCount, "B"), _
CharacterCount, 1)
d = Mid(.Cells(RowCount + 1, "B"), _
CharacterCount, 1)
If Len(.Cells(RowCount, "B")) < _
CharacterCount Then Exit Do

If Len(.Cells(RowCount + 1, "B")) < _
CharacterCount Then Exit Do

If Mid(.Cells(RowCount, "B"), _
CharacterCount, 1) < _
Mid(.Cells(RowCount + 1, "B"), _
CharacterCount, 1) Then

Exit Do
End If
Next RowCount

CharacterCount = CharacterCount + 1
Loop
If CharacterCount = 1 Then
getlongeststr = ""
Else
getlongeststr = Left( _
.Cells(RowCount, "B"), _
CharacterCount - 1)
End If
End With
End Function

"Nathan_Decker" wrote:

Trying to find closest match in column B for each unique entry in column A.

I have a list like this (very long):
ParentSKU ManufacturerSKU
10028 A054150
10028 A054150
1002 AVCRGYGWT
1002 AVCRGNGGD
1002 AVCRBNGBZ
10000 2075492
10000 2075493
10000 2075492
9997 2075488
9997 2075499

For each unique "ParentSKU" in column A, I need to find the longest common
text among all of the same ParentSKU's in column B.

To demonstrate what result is desired, it's easy to eye it up and find the
answer. For the above, the following results are desired:
ParentSKU Answer
10028 A054150
1002 AVCR
10000 207549
9997 20754

Is there a way to do this in excel to avoid having to manually go through
the entire list and eye up each ParentSKU answer?

Each unique "ParentSKU" has between 1 and 54 rows and there are 29,000 total
rows. Additionally, "ManufacturerSKU's" can be either text, numbers, or a
combination of text and numbers.