Multiple lookup values returned into one cell

redmarko · Oct 21st 2013

Hi

I require assistance in developing some VBA code to return multiple lookup values into one cell.
So for example, I have part numbers that can be at different stock locations. ie

Stock Locations (worksheet)
Part Location
X A10-Front
Y A11-Front
Z A12-Front
X A13-Front
Y A14-Front
Z A15-Front
X A16-Front
Y A17-Front
Z A15-Front

I want to summarise the locations for each part number as below.
Locations (worksheet)
Part Locations
X A10, A13, A16
Y A11, A14, A17
Z A12, A15

Note all the locations are in one cell and the returned text has been trimmed to remove the text "-Front". Also I dont want duplicates to appear as in Z A15-Front which appears twice on the list.
I require a macro that will run from another worksheet as the stock location spreadsheet will be a fresh download weekly.

My VBA is improving slowly but this is way beyond me. Any help is greatly appreciated.

Herbds7 · Oct 22nd 2013

Re: Multiple lookup values returned into one cell

Excel 2010, Tables
Consolidate, merge values into one cell.
With macro and hidden PivotTable.
No trimming or duplicate removal.
For more help, upload complete file.
http://42aa60af62977a54a0b2-9c…rackcdn.com/04_18_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

redmarko · Oct 22nd 2013

Re: Multiple lookup values returned into one cell

Hi

I have managed to find a function which pretty much does what I want. However the function only runs within the "multi stock location" spreadsheet in conjunction with the manual input of the getmacs formula.
I need to have this function embedded in a sub routine macro in another spreadsheet.

apo · Oct 22nd 2013

Re: Multiple lookup values returned into one cell

Hi..

I modified your Function so it removes the hyphen and the characters to the right of the hyphen..

Am trying now to turn it into a Sub.. hitting a bit of a brain block on that one though atm..

Code

Function GetMacs(LURng As Range, rng As Range, ref As Long, Optional txt As String = ",") As String
Dim i As Long
Dim pos As Integer
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For i = 1 To rng.Rows.Count
          If LURng.Value = rng.Cells(i, 1).Value Then
               If Not .exists(rng.Cells(i, ref).Value) Then
                    .Add rng.Cells(i, ref).Value, Nothing
                    pos = InStr(rng.Cells(i, ref).Value, "-")
                    GetMacs = GetMacs & txt & Left(rng.Cells(i, ref).Value, pos - 1)
               End If
          End If
     Next
End With
If Len(GetMacs) Then GetMacs = Mid$(GetMacs, Len(txt) + 1)
End Function

Display More

redmarko · Oct 22nd 2013

Re: Multiple lookup values returned into one cell

Thanks apo, every little helps

apo · Oct 22nd 2013

Re: Multiple lookup values returned into one cell

Hi..

This seems to work.. Click the Button in the attached Workbook..

Remove (or comment out) your Getmacs Function or it will try to call it when this sub is run...

I probably should have just changed the Getmacs variable to another name.. in hindsight..

Code

Private Sub CommandButton1_Click()
Dim i As Long
Dim pos As Integer
Dim txt As String, Getmacs As String
Dim rng As Range


Set rng = Sheets("Stock Location").Range("A2:A" & Sheets("Stock Location").Range("A" & Rows.Count).End(xlUp).Row)
txt = ","
For Each e In Sheets("Locations").Range("A2:A" & Sheets("Locations").Range("A" & Rows.Count).End(xlUp).Row)
With CreateObject("Scripting.Dictionary")
     .CompareMode = vbTextCompare
     For i = 1 To rng.Rows.Count
          If e.Value = rng.Cells(i, 1).Value Then
               If Not .exists(rng.Cells(i, 2).Value) Then
                    .Add rng.Cells(i, 2).Value, Nothing
                    pos = InStr(rng.Cells(i, 2).Value, "-")
                    Getmacs = Getmacs & txt & Left(rng.Cells(i, 2).Value, pos - 1)
               End If
          End If
     Next
End With
If Len(Getmacs) Then Getmacs = Mid$(Getmacs, Len(txt) + 1)
e.Offset(, 1).Value = Getmacs
Getmacs = ""
Next e
End Sub

Display More

redmarko · Oct 23rd 2013

Re: Multiple lookup values returned into one cell

Excellent. Works great

Multiple lookup values returned into one cell

Participate now!

Share