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:

[TABLE="width: 606"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Uses Articles[/TD]
[TD]100000747[/TD]
[TD]---[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Uses Articles[/TD]
[TD]100000748[/TD]
[TD]---[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Uses Articles[/TD]
[TD]100120786[/TD]
[TD]---[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Supplied By Supplier[/TD]
[TD]315708909,Loctite[/TD]
[TD]Loctite 5699[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Uses Articles[/TD]
[TD]3EER300000-2688[/TD]
[TD]---[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Supplied By Supplier[/TD]
[TD]516867611,Loctite[/TD]
[TD]7063 Cleaner[/TD]
[/TR]
</tbody>[/TABLE]

---

Should be like this:

[TABLE="width: 489"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Uses Articles[/TD]
[TD="align: center"]100000747[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Uses Articles[/TD]
[TD="align: center"]100000748[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Uses Articles[/TD]
[TD="align: center"]100120786[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]315708909,Loctite[/TD]
[TD="align: center"]Loctite 5699[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Supplied By Supplier[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Uses Articles[/TD]
[TD="align: center"]3EER300000-2688[/TD]
[TD="align: center"]---[/TD]
[TD="align: center"]516867611,Loctite[/TD]
[TD="align: center"]7063 Cleaner[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Supplied By Supplier[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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