Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Help Writing Excel Macro

Posted on 2011-02-21
Medium Priority
233 Views
The Data worksheet is how my data is formatted.  There are an known number of rows, some could have 100, 3, or 1000.  The macro needs to loop until end of last row.

On the Result worksheet, it's stringing together all the similar part numbers.

Check the attached xls for an example. example.xls
0
Question by:michaelblitz
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
• 2

LVL 17

Expert Comment

ID: 34948938
This created for the  row 7 of the work sheet, apply same for the rest of the cells

=CONCATENATE(Data!A7,"|",Data!B7," ",Data!C7,"|",REPLACE(Data!D7,5,1,"|"))

Hope this helps
0

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 34949031
michaelblitz,

The following seems to be working for me.  It uses a "Dictionary of Dictionaries" approach as outlined in my article http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

``````Sub MakeLists()

Dim dicFront As Object
Dim dicRear As Object
Dim dicSecond As Object
Dim arr As Variant
Dim r As Long
Dim LastR As Long
Dim Make As String
Dim Model As String
Dim Years As Variant
Dim Front As String
Dim Rear As String
Dim Concat As String
Dim Entries As Variant

With ThisWorkbook.Worksheets("Data")
LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
arr = .Range(.[a2], .Cells(LastR, "g")).Value
End With

Set dicFront = CreateObject("Scripting.Dictionary")
Set dicRear = CreateObject("Scripting.Dictionary")

For r = 1 To UBound(arr, 1)
Make = arr(r, 1)
Model = Trim(arr(r, 2) & " " & arr(r, 3))
Years = Split(arr(r, 4) & "-", "-")
Concat = Join(Array(Make, Model, Years(0), Years(1)), "|")
Front = arr(r, 6)
Rear = arr(r, 7)
If Front <> "" Then
If dicFront.Exists(Front) Then
Set dicSecond = dicFront.Item(Front)
Else
Set dicSecond = CreateObject("Scripting.Dictionary")
End If
dicSecond.Item(Concat) = Concat
End If
If Rear <> "" Then
If dicRear.Exists(Rear) Then
Set dicSecond = dicRear.Item(Rear)
Else
Set dicSecond = CreateObject("Scripting.Dictionary")
End If
dicSecond.Item(Concat) = Concat
End If
Next

[a1] = "Front"
LastR = 1
Entries = dicFront.Keys
For r = 0 To UBound(Entries)
LastR = LastR + 1
Cells(LastR, 1) = Entries(r)
Cells(LastR, 2) = Join(dicFront.Item(Entries(r)).Keys, "###")
Next

LastR = LastR + 2
Cells(LastR, 1) = "Rear"
Entries = dicRear.Keys
For r = 0 To UBound(Entries)
LastR = LastR + 1
Cells(LastR, 1) = Entries(r)
Cells(LastR, 2) = Join(dicRear.Item(Entries(r)).Keys, "###")
Next

Set dicSecond = Nothing
Set dicFront = Nothing
Set dicRear = Nothing

MsgBox "Done"

End Sub
``````

Patrick
0

Author Comment

ID: 34949050
Hi matthewspatrick

Very nice but only one critic, right now you're using Front AND Rear to determine uniqueness.  Instead, it should be Front OR Rear.  For example,

A, B
A,
A, C
A, D

In your code you'd show 4 unique but in reality it's 1 unique front and 3 unique rears.  Did that make sense?  :)
0

Author Comment

ID: 34949202
Oh wait nevermind my mistake :)
0

LVL 93

Expert Comment

ID: 34950265
michaelblitz,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

Patrick
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month8 days, 22 hours left to enroll

#### 604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.