Question description:
This user has given permission to use the problem statement for this
blog.
seeking help in itentifying if A1 = A10 the need values in C1 are B1 and B10
Solved by C. W. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/04/2018 - 06:06
hello! How are you?
User
09/04/2018 - 06:06
Hi I am good wbu
Excelchat Expert
09/04/2018 - 06:06
Could you please walk me through your problem? And would appreciate if you could provide a sample data we could work with
User
09/04/2018 - 06:07
sure..
Excelchat Expert
09/04/2018 - 06:07
oka, thanks
Excelchat Expert
09/04/2018 - 06:08
please let me know when you're done and ready to discuss
User
09/04/2018 - 06:09
so what i am looking for is if i match the word apple for column A then the result in Column C should be B2 and B11 ( anthony, Dusky)
User
09/04/2018 - 06:09
ready to discuss now
Excelchat Expert
09/04/2018 - 06:10
got it, are you okay if we use vba for this?
User
09/04/2018 - 06:10
I can if thats the last option
Excelchat Expert
09/04/2018 - 06:11
let me think of other ways to do it without resorting to coding
User
09/04/2018 - 06:11
sure
Excelchat Expert
09/04/2018 - 06:11
i'll get back to you in a while
User
09/04/2018 - 06:11
sure no worries.. would you mind to sharethe VBA coding for the meantime so i can play with it
Excelchat Expert
09/04/2018 - 06:12
sure. i'll download and modify the excel file then send it to you.
Excelchat Expert
09/04/2018 - 06:15
oh no, i can't send macro-enabled worksheets
Excelchat Expert
09/04/2018 - 06:15
can i give you the code instead?
User
09/04/2018 - 06:15
sure please
Excelchat Expert
09/04/2018 - 06:16
here
[Uploaded an Excel file]
Excelchat Expert
09/04/2018 - 06:17
Function MyLookup(LookFor, FindCol As Range, GetCol As Range) As String
For Each c In FindCol
If LookFor = c Then
If MyLookup = "" Then
MyLookup = Cells(c.Row, GetCol.Column)
Else
MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
End If
End If
Next c
End FunctionFunction MyLookup(LookFor, FindCol As Range, GetCol As Range) As String
For Each c In FindCol
If LookFor = c Then
If MyLookup = "" Then
MyLookup = Cells(c.Row, GetCol.Column)
Else
MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
End If
End If
Next c
End Function
User
09/04/2018 - 06:18
could you please guide me the steps to follw
Excelchat Expert
09/04/2018 - 06:18
sure.
Excelchat Expert
09/04/2018 - 06:18
kindly open your excel file
Excelchat Expert
09/04/2018 - 06:18
then press ALT+F11
Excelchat Expert
09/04/2018 - 06:18
a vba windows should appear
Excelchat Expert
09/04/2018 - 06:19
then Insert>New Module
Excelchat Expert
09/04/2018 - 06:19
can you follow so far?
User
09/04/2018 - 06:20
Unable to run
Excelchat Expert
09/04/2018 - 06:20
you don't have to run this one
Excelchat Expert
09/04/2018 - 06:21
are you done with copy and paste formula to the vba module screen?
User
09/04/2018 - 06:21
yes i yes i inserted but i comes in a odd manner
Excelchat Expert
09/04/2018 - 06:21
wait, let me provide to you the link
Excelchat Expert
09/04/2018 - 06:21
https://www.ozgrid.com/forum/forum/help-forums/excel-general/120959-vlookup-multiple-values-and-return-multiple-values-in-one-cell-with-comma-seperated
Excelchat Expert
09/04/2018 - 06:22
just look for the code on the comment dated April 7th, 2013, 00:14
Excelchat Expert
09/04/2018 - 06:22
copy and paste to your module screen
User
09/04/2018 - 06:23
i did
Excelchat Expert
09/04/2018 - 06:23
alright
User
09/04/2018 - 06:23
what to do next after copy and paste
Excelchat Expert
09/04/2018 - 06:24
okay, close the vba window
User
09/04/2018 - 06:24
closed
Excelchat Expert
09/04/2018 - 06:24
then use the mylookup() formula on column c
Excelchat Expert
09/04/2018 - 06:24
it works like vlookup
Excelchat Expert
09/04/2018 - 06:24
are you familiar with using vlookup?
User
09/04/2018 - 06:25
yes i am
User
09/04/2018 - 06:25
where would i find my look up option
Excelchat Expert
09/04/2018 - 06:26
just type =mylookup()
Excelchat Expert
09/04/2018 - 06:26
=MyLookup(LookFor, FindCol As Range, GetCol As Range)
Excelchat Expert
09/04/2018 - 06:26
this is the format of the formula
User
09/04/2018 - 06:29
okies
User
09/04/2018 - 06:29
it comes with an error
Excelchat Expert
09/04/2018 - 06:30
okay, let me check again
Excelchat Expert
09/04/2018 - 06:30
i'll be back in a minute or so, okay?
Excelchat Expert
09/04/2018 - 06:31
hi
Excelchat Expert
09/04/2018 - 06:31
i am back
User
09/04/2018 - 06:32
hi
Excelchat Expert
09/04/2018 - 06:32
kindly use this code instead
User
09/04/2018 - 06:32
sure
Excelchat Expert
09/04/2018 - 06:32
https://superuser.com/questions/893506/how-to-lookup-a-value-and-return-multiple-unique-values-remove-duplicates-to-a
User
09/04/2018 - 06:33
cool i have copied this by using alt F11.. whts next
Excelchat Expert
09/04/2018 - 06:34
remove the original code and replace with the code on the link i provided you with
User
09/04/2018 - 06:34
do we just delete the previous code or is there anything else we need to do
Excelchat Expert
09/04/2018 - 06:35
just delete it, yes
User
09/04/2018 - 06:35
done
User
09/04/2018 - 06:35
whats next
Excelchat Expert
09/04/2018 - 06:35
after you paste the code, use this syntax instead mylookup(range,value)
Excelchat Expert
09/04/2018 - 06:36
you can copy what i did in cell E3 on the document preview on the right
User
09/04/2018 - 06:37
sorry still coming up with an error
Excelchat Expert
09/04/2018 - 06:38
hmmm
Excelchat Expert
09/04/2018 - 06:38
what is the name of the error?
Excelchat Expert
09/04/2018 - 06:38
=mylookup(A2:B11,D3)
Excelchat Expert
09/04/2018 - 06:38
did you type this?
User
09/04/2018 - 06:38
Ambiguous name detected
Excelchat Expert
09/04/2018 - 06:39
can you provide me a screenshot please?
Excelchat Expert
09/04/2018 - 06:39
if it still doesn't work, i still have another code you can try
User
09/04/2018 - 06:40
here we go
Excelchat Expert
09/04/2018 - 06:40
[Uploaded an Excel file]
Excelchat Expert
09/04/2018 - 06:41
[Uploaded an Excel file]
Excelchat Expert
09/04/2018 - 06:41
the former two screenshot worked perfectly for me too
User
09/04/2018 - 06:42
unable to copy the formula
Excelchat Expert
09/04/2018 - 06:43
you mean on the one with the screenshot i provided?
User
09/04/2018 - 06:43
yes
Excelchat Expert
09/04/2018 - 06:43
you can copy it from the document preview on the right :)
User
09/04/2018 - 06:45
sorry same error
Excelchat Expert
09/04/2018 - 06:46
how i wish i could send to you the excel file
Excelchat Expert
09/04/2018 - 06:47
for a while, i'll find a way to send it to you
User
09/04/2018 - 06:48
hmm any other way rather than VBA?
Excelchat Expert
09/04/2018 - 06:48
https://drive.google.com/open?id=1-7fV_tkKr9m3H_PXbmZHy0X3-xF7630y
Excelchat Expert
09/04/2018 - 06:48
try to download the file from this link.
Excelchat Expert
09/04/2018 - 06:49
yes, i found one. but it will involve a lot of additional columns
User
09/04/2018 - 06:50
thats fine happy to do that
Excelchat Expert
09/04/2018 - 06:50
okay, please give me a minute
User
09/04/2018 - 06:52
sure
Excelchat Expert
09/04/2018 - 06:56
here you go
[Uploaded an Excel file]
User
09/04/2018 - 06:57
Thanks for sending the file but it appears that its picking up the wrong data.. apple should match with anthony and dusky
Excelchat Expert
09/04/2018 - 06:57
remember what i used in columns E to H is an array formula
Excelchat Expert
09/04/2018 - 06:58
oops, sorry
Excelchat Expert
09/04/2018 - 06:59
kindly highlight E2 to H2 and change the ROW($B$2:$B$11)-2 to ROW($B$2:$B$11)-1
Excelchat Expert
09/04/2018 - 07:00
then after you made the edit on your keyboard press CTRL+SHIFT+ENTER
User
09/04/2018 - 07:00
sorry didnt get that
User
09/04/2018 - 07:00
could you please resend me the file
User
09/04/2018 - 07:00
thanks
Excelchat Expert
09/04/2018 - 07:00
sure
Excelchat Expert
09/04/2018 - 07:00
here
[Uploaded an Excel file]
User
09/04/2018 - 07:02
Thanks for that now any chance we could do it without adding additional rows or colums
Excelchat Expert
09/04/2018 - 07:02
i'm sorry what i have is only the vba code which doesn't seem to work with you
User
09/04/2018 - 07:03
hmm well thanks heaps for your help in the matter
User
09/04/2018 - 07:03
Really appreciate your help
Excelchat Expert
09/04/2018 - 07:03
sure, you are most welcome!
Excelchat Expert
09/04/2018 - 07:03
if you wish to study the solution more you may refer to https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/
Excelchat Expert
09/04/2018 - 07:04
under Formula 2
Excelchat Expert
09/04/2018 - 07:04
is it okay if I mark this problem solved?
User
09/04/2018 - 07:04
Thanks for youyr help
User
09/04/2018 - 07:04
sure i will
Excelchat Expert
09/04/2018 - 07:05
by the way, if you don't need anything else, please feel free to end the session.
Excelchat Expert
09/04/2018 - 07:05
also, you rating and feedback is greatly appreciated :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
