Order table with macro

Leonel Garcia
Leonel Garcia used Ask the Experts™
on
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:
Initial table
and I need to obtain this result

Final table
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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
Leonel Garcia

Author

Commented:
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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial