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

A VBA Solution to using the MCONCAT Function from the Morefunc Add-in

Good Afternoon

Having got a working solution, to a previous question, which is only hampered by the IT Policy, I am looking for a VBA work around. The original problem:

Please see the Excel workbook for further details - warning contains a formula which requires the Morefunc add-in

I am trying to match a list of items(Column C) to a given location(Column A), to help with future amendments to both the location and list of items I would like to refference a refference type table. Its this arrangement between the data and the refference table that makes me think of a VLookup?

A full example can be seen in the workbook, where column E refferes to what I would like to be left with. Each item has its own unique code and is in the following format:

Item;#Number;#

The solution, which works is the use of the MCONCAT function from the Morefunc add-in, with the following:

=MCONCAT(IF(ISNUMBER(1/SEARCH(Information!B$2:B$15,C2)/(A2=Information!A$2:A$15)),Information!B$2:B$15,""))

I have had a Google search of the VBA functionality for the AConcat as suggested, but VBA is outside my comfort zone.

Any ideas

Many thanks
Example.xls
0
Washcare
Asked:
Washcare
1 Solution
 
Rory ArchibaldCommented:
Here's a basic version - demo in the attached file.

Public Function Concat(Target As Variant, Optional strSeparator As String = ", ") As String
    Dim varItem, lngIndex As Long
    If UBound(Target, 1) >= 0 Then
        For lngIndex = LBound(Target, 1) To UBound(Target, 1)
            If Len(Target(lngIndex, 1)) > 0 Then Concat = Concat & strSeparator & Target(lngIndex, 1)
        Next lngIndex
      If Len(Concat) > 0 Then Concat = Mid$(Concat, Len(strSeparator) + 1)
   End If
End Function

Open in new window

Example--1-.xls
0
 
WashcareAuthor Commented:
Many thanks
0
 
Dave BrettVice President - Business EvaluationCommented:
It would be best to group the shorter strings together to savoid adding the longer string twice

Concat = Concat & (strSeparator & Target(lngIndex, 1))

Cheers

Dave
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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