Need a macro. Separate values from a column and combined it in a different cell. Thanks

[Farisha]

Dear all,

I need your help. I have value in Column A, and Column B. In some cells the value in Column B comes with multiple value separated by comma. I need to separate the value in column B and put it in column G. The value should come in Column G in an order like, First Value from column V, second value from Column D, Third Value from E, 4th Value from Column F ( if there is any) and if it is blank, the 5 value should start from Column C second value and it should go on like this. I will appreciate any help . Thanks

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Match Column A and G
Apple	Apple, Orange, Banana	Apple	Orange	Banana		Apple
Orange	Mango	Mango				Orange
Banana	Peach	Peach				Banana
Mango	Avocado	Avocado				Mango
Peach						Peach
Avocado						Avocado

 

[Fluff]

Can you have duplicate values in col B? If so do you want to display that once in col G or multiple times?

 

[Farisha]

Can you have duplicate values in col B? If so do you want to display that once in col G or multiple times?

Yes. I will have duplicate value and It should be shown multiple times in the same order.

 

[Fluff]

Ok, how about

Sub Farisha()
   Dim Lst As Object
   Dim Sp As Variant
   Dim i As Long
   Dim Cl As Range
   
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      Sp = Split(Cl, ",")
      For i = 0 To UBound(Sp)
         Lst.Add Trim(Sp(i))
      Next i
   Next Cl
   Range("G2").Resize(Lst.Count).Value = Application.Transpose(Lst.toarray)
End Sub

 

[Farisha]

Ok, how about

Sub Farisha()
   Dim Lst As Object
   Dim Sp As Variant
   Dim i As Long
   Dim Cl As Range
  
   Set Lst = CreateObject("system.collections.arraylist")
   For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
      Sp = Split(Cl, ",")
      For i = 0 To UBound(Sp)
         Lst.Add Trim(Sp(i))
      Next i
   Next Cl
   Range("G2").Resize(Lst.Count).Value = Application.Transpose(Lst.toarray)
End Sub

Pefect. It worked very well. Thanks for making my life soooo easy.

 

[Fluff]

You're welcome & thanks for the feedback