Condense multiples into first occurrence only

[nguerra]

Is there a way to take the first occurrence in every value change in a column and re-write it with another value in the same row? For example:

Column 1	Column 2			Column 6	Column 7
apple	a			apple	a
apple	a			pear	a
apple	a			orange	b
pear	a			grape	a
pear	a			banana	b
orange	b			apple	b
grape	a			orange	a
grape	a
banana	b
apple	b
apple	b
orange	a

<colgroup><col style="width:54pt" span="6" width="72"> </colgroup><tbody>
</tbody>

 

[Marcelo Branco]

See if this does what you need

Sub FirstOnlyv2()
    Dim lastRow As Long, i As Long, v As Variant
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    With CreateObject("Scripting.Dictionary")
        .comparemode = vbTextCompare
        
        For i = 1 To lastRow
            If Not .exists(Cells(i, 1).Value) Then .Add Cells(i, 1).Value, Range(Cells(i, 2), Cells(i, 3)).Value
        Next i
        
        Range("F1").Resize(.Count).Value = Application.Transpose(.keys)
        
        i = 1
        For Each v In .keys
            Range("G" & i).Resize(, 2).Value = .Item(v)
            i = i + 1
        Next v
    End With
End Sub

M.

 

[nguerra]

That's it, thank you. I was trying to modify your original code and I had the Cells(i, 3) right but I didn't know about using the range. I greatly appreciate your help with this

 

[Marcelo Branco]

That's it, thank you. I was trying to modify your original code and I had the Cells(i, 3) right but I didn't know about using the range. I greatly appreciate your help with this

You are welcome. Glad for helping.

M.