Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

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:
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
Leonel Garcia
Leonel Garcia
  • 2
1 Solution
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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")
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)
      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
Application.ScreenUpdating = True
End Sub

Open in new window

Leonel GarciaAuthor 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 ExpertCommented:
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now