How to derive multiple corresponding entries for a string

[ThrottleWorks]

Hi,

Please check the attached file for problem details. Can anyone help me in this please.

 

[Nebu]

Hi:

Can't you use a pivot to achieve this? Anyways I have constructed a formula using pivot as helper range, if you are particular about the format in which you want the results.

Thanks

 

[Marc L]

Hi !

Pivot is often the Way …

Anyway, try this :​

Sub Demo1()
    Dim Rg As Range
    Application.ScreenUpdating = False
                            L& = 3
With Sheet1
             .Cells(4).CurrentRegion.Clear
    Set Rg = .Cells(1).CurrentRegion.Rows
        Rg("2:3").Copy .[D2]

    For R& = 4 To Rg.Count
        If Rg.Cells(R, 1).Value <> Rg.Cells(R - 1, 1).Value Then
            L = L + 1:    Rg(R).Copy .Cells(L, 4)
  
        ElseIf Rg.Cells(R, 2).Value <> Rg.Cells(R - 1, 2).Value Then
            C& = .Cells(L, 4).End(xlToRight).Column + 1
               Rg.Cells(R, 2).Copy .Cells(L, C)
              If .Cells(2, C).Value = "" Then .Cells(2, C - 1).Copy .Cells(2, C)
        End If
    Next
End With
    Set Rg = Nothing
End Sub

By code, many ways to achieve that
but it depends on the real source data, the number of lines, …
​

 

[ThrottleWorks]

Hi @Nebu , thanks a lot for the help. However I can not use pivot table. Have a nice day ahead.

 

[ThrottleWorks]

Hi @Marc L , thanks a lot for the help. It's working perfect ! Will revert in case of any doubts. Have a nice day ahead.