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

Concatenating values that match the same primary value

Hi,

I have a small concatenating type issue.  

I have a list of SiteID's that use different frequencies at the site to transmit.  Some sites have one frequency, others have multiple.  I need to get an extract of data from:

SITE1     FREQ1
SITE1     FREQ2
SITE2     FREQ1
SITE3     FREQ4

To be similar to:
SITE1     FREQ1, FREQ2
SITE2    FREQ1
SITE3    FREQ4

I have attached the sheet with data in it to show you the format, and the required output

Any help or direction would be great.

Thanks
Site-FrequencyData.xlsx
0
Snappa2000
Asked:
Snappa2000
  • 4
  • 2
1 Solution
 
dlmilleCommented:
Solution - created a UDF function that is like a VLOOKUP - except it returns all matches with comma + space separation as you requested:

usage: =getallmatches(F4,$A$3:$B$259,2) <- F4 is the Site ID, A3:B259 is the range to lookup, 2 is the second column to get.  Exact match is presumed.

Here's the function:
Function getAllMatches(lookup_value As Variant, lookup_table As Range, lColumn As Long) As String

    For Each mycell In lookup_table
        If mycell = lookup_value Then
            If getAllMatches = "" Then
                getAllMatches = mycell.Offset(0, lColumn - 1).Value
            Else
                getAllMatches = mycell.Offset(0, lColumn - 1).Value & ", " & getAllMatches
            End If
        End If
    Next mycell
    
End Function

Open in new window


See attached demo workbook from your posting, with the function demonstrating it works!

Cheers,

Dave
sample-r1.xls
0
 
Snappa2000Author Commented:
Dave,

I think you attached the wrong workbook :)
0
 
dlmilleCommented:
Sorry, dude.

Here it is:


Site-FrequencyData-r1.xlsm
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dlmilleCommented:
Sorry - I was playing around with the file when I posted, with a formula (after you told me I didn't upload the right file).  This one is cleaned up.

Sorry for repetative post.

Dave
Site-FrequencyData-r1.xlsm
0
 
Snappa2000Author Commented:
Dave,

Awesome.. quick and precise!

Thanks will be putting this in the sample box!

Cheers
0
 
dlmilleCommented:
PS - you can do this with formulas, but its pretty complex, having to build a match for every possible occurance you might need - so if you know max is 2 matches, its doable - more on this:

=VLOOKUP(K3,$A$3:$B$503,2,0)  &IFERROR(", " & VLOOKUP(K3,INDIRECT(ADDRESS(MATCH(K3,$A$1:$A$503,0)+1,2)&":$A$503"),2,0),"")

it gets really unwieldy if you want 3 or more matches to be possible...

Dave
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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