Pull Values if any duplicate find then pull all values

[Abhijeet]

Hi

I tried to do this in Formula based i use this formula please tell me in VBA how to do this

=IFERROR(INDEX('Main Data'!$B$3:$B$17,SMALL(IF('Main Data'!$A$3:$A$17='Expecetd result'!$A1,ROW('Main Data'!$B$3:$B$17)-ROW('Main Data'!$B$3)+1),COLUMNS($B1:B1))),"")

 

[Marc L]

Hi !

See Evaluate method or Formula property …
​

 

[jindon]

For the data provided.

Sub test()
    Dim a, i As Long, w
    a = Sheets("main data").[a3].CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 1 To UBound(a, 1)
            If Not .exists(a(i, 1)) Then
                .Item(a(i, 1)) = Array(.Count + 1, 1)
                a(.Count, 1) = a(i, 1)
            End If
            w = .Item(a(i, 1))
            w(1) = w(1) + 1: .Item(a(i, 1)) = w
            If UBound(a, 2) < w(1) Then ReDim Preserve a(1 To UBound(a, 1), 1 To w(1))
            a(w(0), w(1)) = a(i, 2)
        Next
        Sheets("Expecetd result").Cells(1).Resize(.Count, UBound(a, 2)).Value = a
    End With
End Sub

 

[Abhijeet]

Hi

Can u please tell me for dynamic range bcoz data is more then this is not work

 

[jindon]

What do you mean by "dynamic"?

It is absolutely dynamic.

 

[Abhijeet]

if i add more name in expected result sheet then its not work

 

[jindon]

upload a wb.

 

[Abhijeet]

Sorry its work i manually add same name in expected result sheet

 

[jindon]

If you write vba for this, manually enter name(s) in result is meaningless.
It should be fully automated.

 

[Abhijeet]

Thanks Jindon for your help macro is work perfect