Order table with macro

Greetings to all;

First of all many thanks for the time to read my question, I've been reviewed in the forum but I have not had capable to find something similar, I have the following table:

and I need to obtain this result

I've used Vlookup in VBA but this function only gives me the 1st result founded.

The reason I need to do this with macros is because this re-arrangement will be part of a loop process with information from different sources, but requiring the same treatment.

I'll be very grateful for any idea / comment or suggestion.

Best regards
Table.xlsx

Please try this.....
In the attached, click the button Transform Data to get the desired output in a separate sheet.
if you have an issue in downloading and opening the attached file due to a temporary bug in the forum, first download and save it on your system and then open it.

Sub TransformData()
Dim sws As Worksheet, dws As Worksheet
Dim dict As Object
Dim x, y
Dim i As Long, c As Long

Application.ScreenUpdating = False
Set sws = Sheets("Sheet1")
On Error Resume Next
Set dws = Sheets("Final Table")
dws.Cells.Clear
On Error GoTo 0

If dws Is Nothing Then
   Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Final Table"
   Set dws = ActiveSheet
End If

dws.Range("A1:B1").Value = Array("Còdigo", "Lote1")

Set dict = CreateObject("Scripting.Dictionary")
x = sws.Range("A1").CurrentRegion.Value

For i = 2 To UBound(x, 1)
   If Not dict.exists(x(i, 1)) Then
      dict.Item(x(i, 1)) = x(i, 2)
   Else
      dict.Item(x(i, 1)) = dict.Item(x(i, 1)) & ";" & x(i, 2)
   End If
Next i
dws.Range("A2").Resize(dict.Count).Value = Application.Transpose(dict.keys)
For Each it In dict.keys
   y = Split(dict.Item(it), ";")
   dws.Range("B" & Rows.Count).End(3)(2).Resize(1, UBound(y) + 1).Value = y
Next it
c = dws.Range("A1").CurrentRegion.Columns.Count
dws.Range("B1").AutoFill dws.Range(dws.Cells(1, 2), dws.Cells(1, c)), xlFillDefault
dws.Range("A1").CurrentRegion.CurrentRegion.Borders.Color = vbBlack
dws.Activate
Application.ScreenUpdating = True
End Sub

Open in new window

Table.xlsm

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 43

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented: 2016-08-22

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Leonel GarciaAuthor Commented: 2016-08-23

Wow!!!!, works like a charm!!!, I'v realized there are a lot of things I need to learn, I've never used a dictionary but the solution is great.

I was wondering if is possible (my apologies for any inconvenience this may cause) you can help me adding some comments to the dictionary part in the macro, those will be very helpful for me to understand how it works.

Once again many thanks for your help.

Best regards

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented: 2016-08-23

You're welcome Leonel! Glad to help and thanks for the feedback.

Adding comments for the dictionary part won't help you much unless you read about it. It's just a pretty simple IF block checking if a dictionary key exist or doesn't exist and performs different actions for both the conditions.

Order table with macro

Who is Participating?