Moving cells and creating columns according to a criteria in another cell

[lucas_delarge]

Hi friends,

I have to create a macro (or a formula) to do the following:

I need to get the supplier name when it appears on the below cell and transport to the same cell of the part number, adding new columns for them.

Column A --> is a structure index (not really important here)
Column B --> when I have "Supplied By Supplier (A>Sup)" I need to collect the same line value on "C" and "D" and move it to a column "E" and "F", but one line above to keep it in the same line of the father.

This is the previous table:

A	B	C	D
3	Uses Articles	100000747	---
3	Uses Articles	100000748	---
3	Uses Articles	100120786	---
4	Supplied By Supplier	315708909,Loctite	Loctite 5699
3	Uses Articles	3EER300000-2688	---
4	Supplied By Supplier	516867611,Loctite	7063 Cleaner

<tbody>
</tbody>

---

Should be like this:

A	B	C	D	E	F
3	Uses Articles	100000747	---
3	Uses Articles	100000748	---
3	Uses Articles	100120786	---	315708909,Loctite	Loctite 5699
4	Supplied By Supplier
3	Uses Articles	3EER300000-2688	---	516867611,Loctite	7063 Cleaner
4	Supplied By Supplier

<tbody>
</tbody>

I've tried a many combinations but it didn't work. If anyone knows how to solve this.

Thanks a lot!!!

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub lucas_delarge()
   Dim Cl As Range
   
   With Range("B:B")
      .Replace "Supplied By Supplier", "#N/A", xlWhole, , False, , False, False
      For Each Cl In .SpecialCells(xlConstants, xlErrors).Areas
         Cl.Offset(, 1).Resize(, 2).Cut Cl.Offset(-1, 3)
      Next Cl
      .Replace "#N/A", "Supplied By Supplier", xlWhole, , False, , False, False
   End With
End Sub

 

[lucas_delarge]

Great support!!!

I've forgotten to thank you but I am back here! Thanks a lot!

 

[Fluff]

You're welcome & thanks for the feedback