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
Snappa2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Snappa2000Author Commented:
Dave,

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

Here it is:


Site-FrequencyData-r1.xlsm
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.