Link to home
Start Free TrialLog in
Avatar of Alex Krakov
Alex Krakov

asked on

Start the loop again when finding a duplicate in the same cell

I have a Excel file, its look like:
User generated imageAfter the macros, this will be the end results:
User generated image
I want macro that read column "B", and paste first country from column "C" in the same cells of column "A", after he find duplicate cell in "B", he copy next country from column "C", and does the same as I wrote with first country and etc.

I have a code:
Sub x()

Dim r1 As Long, r2 As Long

r1 = 2: r2 = 2

Do While Cells(r1, 2) <> vbNullString
    If IsNumeric(Application.Match(Cells(r1, 2), Range(Cells(1, 2), Cells(r1 - 1, 2)), 0)) Then
        r2 = r2 + 1
    End If
    Cells(r1, 1).Value = Cells(r2, 3).Value
    r1 = r1 + 1
Loop

End Sub

Open in new window


But it does not work as it should:
User generated image
I want when macro find first duplicate in column "B" (in my case B7 "A Bad Moms Christmas") he starting find duplicate from this cell and not paying attention to past cells, and paste same country from "C" (United Arab Emirates), before he find another duplicate and etc.
Can someone help? Thanks.
Avatar of Alan
Alan
Flag of New Zealand image

Hi Alex,

Can you post a sample file rather than us having to re-create it from scratch.

Also, does it have to be VBA?  A formula might be easier, and has the advantage of being self-documenting, and not requiring an XLSM file.

Alan.
Avatar of Alex Krakov
Alex Krakov

ASKER

Hi Alan.
I attach file. Yes it must be VBA.
data.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Alan
Alan
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Alan! it's work perfect.
You're most welcome - glad to help.

Alan.