Excel- Find multiple matching entries to a unique value

Hi,

I have a file which has two sheets. One sheet contains Names, and the other Names, and their locations. If I sort the Names sheet, I get all unique names. Now I want to use VLookup from Names to Locations sheet, and display all related Locations with each Name in the Names sheet. File attached.

-SanFind-multiple-entries.xlsx
Sanjay GandhiFounder, KenhalAsked:
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.

aikimarkCommented:
Use your first sheet of names as a criteria range for your names and locations table.  You should sort the names and locations table in name order first.  I updated your workbook (see attached)
Find-multiple-entries.xlsx
0
ProfessorJimJamCommented:
please check the attached file with formula solution. is this what you were looking for?
in sheet "Name"  check the formula in column E basically sorts the data by formula dynamically and column F formula returns the location for that given name.
Find-multiple-entries.xlsx
0
Sanjay GandhiFounder, KenhalAuthor Commented:
aikimark - Sorry, incidentally the file contains no formula

ProfessorJimJam - This file I am just getting the list with VLookup returning the first occurrence only. In 'Names' sheet, against names in Col E, you are giving me related location in Col F, but this is first location. I want each location for each name. Formula in Col E is good, but in Col F, I want all occurrences of names appearing in Col E.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

ProfessorJimJamCommented:
Ok then instead of lookup use countif

I can provide you the formula in the workbook later today
0
Sanjay GandhiFounder, KenhalAuthor Commented:
Yes, CountIF will give me how many same name appearing number of times. Finally I should be getting each different location for each repeated name. I'll wait for the solution.
0
aikimarkCommented:
aikimark - Sorry, incidentally the file contains no formula
I do not understand your comment.
0
Sanjay GandhiFounder, KenhalAuthor Commented:
aikimark - I think I've not been able to make the requirement clear. You have sorted the data and sent across - I thought it would contain a formula to arrive at a solution.

The problem once again. There is a list in 'Locations' sheet which contains several names and locations in front of names. In the 'Names' sheet, I have a few names. If I use a VLOOKUP in front of the name, I find multiple locations against each name. Therefore if I use a VLOOKUP in 'Names' sheet to find relevant Location for that Name, then there are several locations appearing against every name, but VLOOKUP would show me only first matching Location.

So I want a solution where my one Name that has a VLOOKUP in 'Names' sheet, should show me all related Locations from 'Locations' sheet in the 'Names' sheet. No problem if I have to repeat these names number of times the Names appear in Locations sheet.

Need more clarity, please write back.

-San
0
aikimarkCommented:
In the posted workbook, I filtered the list in place.  I could just as easily have placed the filtered copy somewhere else as I did with this version of the workbook. (uploaded)

It would be possible to change the sort order, but a new column would need to be created for the names table.

At this stage of the problem, there is no need for code.  Sorting and applying an advancedfilter are most simply done manually now and can be automated later.
Find-multiple-entries.xlsx
0
ProfessorJimJamCommented:
Sanjay

here is the solution attached.

some explanation. i have converted the Location sheet data to a Table, so that the range becomes dynamic in case if you add more data to it.

so your desired solution is in column B and C.  i think you would not need the F & G column, but i did not delete it and left there anyway.

let me know, how it goes.
EE.xlsx
0
Sanjay GandhiFounder, KenhalAuthor Commented:
Hi,

I am still sorry to say that this is not the result I need.

Aikimark - Sorry, but its going nowhere close to the solution I need. Sorting and all, I can also do. In case you've created the result which you show in Col H and Col I thru a formula, then I will need that.

ProfessorJimJam - The formulae you've put are fine, but if you notice cells F5 to F8 have same name Cathy which is also fine. But the result in front of these names should be Manchester, Delhi, Manchester, Manchester, and not Manchester four times. Likewise for Harry (this name should appear 4 times), we should be getting Florida, Manchester, Florida, Florida.

The result is not as desired, as yet.
0
ProfessorJimJamCommented:
Ok,   Please see attached file with formula solution in Column F
EE.xlsx
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
Sanjay GandhiFounder, KenhalAuthor Commented:
Thanks Professor JimJam.

This is the answer that was needed :)

- San
0
ProfessorJimJamCommented:
You are welcome.

Glad to help
0
aikimarkCommented:
In case you're interested in my approach to your problem, here is the code that automates the advancedfilter and (post-filtered) sorting:
    Dim wks As Worksheet
    Set wks = Worksheets("Names")
    Application.ScreenUpdating = False
    Worksheets("Locations").Range("B3:C27").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=wks.Range("B4:B8"), CopyToRange:=wks.Range("H4"), Unique:=False
    wks.Range(wks.Range("j5"), wks.Range("h4").End(xlDown).Offset(0, 2)).FormulaR1C1 = "=MATCH(rc[-2],r4c2:r8c2,0)"
    wks.Range(wks.Range("h4"), wks.Range("h4").End(xlDown).Offset(0, 2)).Sort key1:=wks.Range("J4"), Header:=xlYes
    'wks.Range("J:J").Delete
    Application.ScreenUpdating = True

Open in new window

0
Sanjay GandhiFounder, KenhalAuthor Commented:
Thanks, but don't want to work with macro. I know that part already. I wanted to know the function in Excel directly.

- San
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.