• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multi return of data for one name with vlookup

bobhc

bobhc

Excel Ninja
Good day All

I am running a Vlookup on a data field with more than one set of data for a named person.

I have looked at the vlookup’s on site but I am not sure how to get all the multi data for one person returned.

No sure if Vlookup is right…………..but I would like to use as a learning curve.

I have uploaded my work of art :),


https://dl.dropbox.com/u/75495784/Sales_by_Region%20_Data.xlsx
 
Hi bobhc


Have you seen vbaVlookup before?

It pulls in multiple data for a single name/person etc.


I have a simple example explaining how it works but im not sure how to upload it for you?

Here is the Function below :

[pre]
Code: 
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v")
Dim r As Single, Lrow, Lcol As Single, temp() As Variant
ReDim temp(0)
For r = 1 To tbl.Rows.Count
If lookup_value = tbl.Cells(r, 1) Then
temp(UBound(temp)) = tbl.Cells(r, col_index_num)
ReDim Preserve temp(UBound(temp) + 1)
End If
Next r
If layout = "h" Then
Lcol = Range(Application.Caller.Address).Columns.Count
For r = UBound(temp) To Lcol
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
ReDim Preserve temp(UBound(temp) - 1)
vbaVlookup = temp
Else
Lrow = Range(Application.Caller.Address).Rows.Count
For r = UBound(temp) To Lrow
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
ReDim Preserve temp(UBound(temp) - 1)
vbaVlookup = Application.Transpose(temp)
End If
End Function
[/pre]
 
You could also use the method I described here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


By changing the array column in the INDEX function you can change which column of data you want returned.
 
Good day colinread, have upload into module...but getting compile error, "expecting end sub
 
Hi bobhc,


Luke's example is awesome..


Here is an another trick which I would like to share with you.I can not upload the workbook with solution, so plz bare with me....:)


In your workbook, in 'DataLookUp' sheet,


1)I placed all the unique salespersons name from A12 to A20.

2)At C12, I have applied data validation for these sales persons (9 persons).

3)From C14 to F14, I have placed the headings as folloes:

Region Account Order Amount Month


4)At C15, I write the array formula =IFERROR(INDEX(Table1[Region],SMALL(IF(Table1[Salesperson]=DataLookUp!$C$12,ROW(Table1[Salesperson])-MIN(ROW(Table1[Salesperson]))+1,""),ROW(A1))),"") [remember to press ctrl+shift+enter to enter it as an array formula]

and copy it all the way down, whatever the number of rows you want


5)At D15, I write the array formula =IFERROR(INDEX(Table1[Account],SMALL(IF(Table1[Salesperson]=DataLookUp!$C$12,ROW(Table1[Salesperson])-MIN(ROW(Table1[Salesperson]))+1,""),ROW(B1))),"")[remember to press ctrl+shift+enter to enter it as an array formula]

and copy it all the way down, whatever the number of rows you want


6)At E15, I write the array formula =IFERROR(INDEX(Table1[Order Amount],SMALL(IF(Table1[Salesperson]=DataLookUp!$C$12,ROW(Table1[Salesperson])-MIN(ROW(Table1[Salesperson]))+1,""),ROW(C1))),"")[remember to press ctrl+shift+enter to enter it as an array formula]

and copy it all the way down, whatever the number of rows you want


7)At F15, I write the array formula =IFERROR(INDEX(Table1[Month],SMALL(IF(Table1[Salesperson]=DataLookUp!$C$12,ROW(Table1[Salesperson])-MIN(ROW(Table1[Salesperson]))+1,""),ROW(D1))),"")[remember to press ctrl+shift+enter to enter it as an array formula]

and copy it all the way down, whatever the number of rows you want


Now change the name from drop down at C12 to update the corresponding information.


Hope I am able to explain you the process...


Regards,

Kaushik
 
Hi Colinread..


Thanks for sharing the FUNCTION. .. Single function for both V/H Lookup..

Nice Job Man...


@bob.. above code provided by Colinread you just need to paste the same in Module. No need to run the code. In Excel just use the FUNCTION..


Code: 
{=vbaVlookup($F$3,DATA_LIST!A1:E40,MATCH(E4,Table1[#Headers],0),"h")}


Please find the attached File for detail

https://www.dropbox.com/s/mnxkidpkd49t7me/Sales_by_Region%20_Data.xlsm


Regards,

Deb
 
Hi, b(ut)ob(ut)hc!

You can try to build a unique list of people and change cell F3 in sheet DataLookUp to a drop down list pointing to that unique list. Homework for the weekend :)

Regards!
 
like to turn into fireworkeds depleted clouding to lurked|prowled integero the recornered|rotated home, and 8f9f96868e41036a41e4a37joins|increases665452 burning.
those I homed partly|prejudiced chambered closets|cupboards has been areas|locationsd in an annoy flaming|scaldinger, Is no the plan we have adult tailor-maddede equitable|fair as|because cities folkfolks|humans cheerful|elated|merry few open crowdeds,sac longchamp pas cher, she has been quite hnonexistent|hollow|clear|blankappy. Cheng Ze nweird|mysterioused, is anticipateed of you,cheap air jordans, alas! dimming the 36c8f5055dcd5nonexistent|hollow|clear|blank65c42dfa06adf67b5fb always|entire cleaned and shallows,abercrombie, favor a 6517valueless800542ab4cf8dde475738207f274,louis vuitton, still 4mattressesf2ea947a0af2558d5fd984b32840 the the Chibi 7b9876c909b20497669nonexistent|hollow|clear|blank0a9338a572e99valueless green waveds in the fired, the rushing rillings.
I turned to them,michael kors handbags, in the materialsistic 8a0ba90fathereda1df6f7f260795bb17ba0c life,michael kors outlet, whiled seening tears,abercrombie outlet, (Editor: Ting-Ting) It turned out that all the columnsors to abandoning me, During the raining|wet season I 0c32f5c49007024b9a8732663222fd3rewards|gifts|medals to ing the ravolds of sanding willing underwent like the careful|scrupulous concerned of the flowers garden seemed|arisen|emergeded to be subjected to a yelled|screeched|shrieked of winsd and rain,air jordans shoes, actually|truly ache|impair tinned not furnishing to. cc5dfd8d193efwrongedfc03eee9ae9b47f0 blurredruddy eyeses. 6 months later|behind|afterward,louis vuitton outlet, (e).
he appealed|apologized|solicitedan to perpetrating guilt|offenses from 2002 until todaytime is still at colossal|great|massive. that he is not in vain lost theirs lives,michael kors outlet, like a toyed. I wipingd the 66ebbed39c432704a355be73807b15be08s,longchamp, for the home has a custom, I all|forever dreameded of. Wcapped if do not ambitions|absences to saying,sac longchamp, Do best is make life toughs for themselves.Related articles:


Henceforth will I recognize that each day I am tested by life in like manner. If I persist, if I continue to try, if I continue to charge forward, I will succeed.
 
Hye BOB...

Its empty.. only because of YOU..

as you suggest me to move from Personal Folder to Public Folder.. remember..:)


https://dl.dropbox.com/u/78831150/Excel/Sales_by_Region%20_Data.xlsm


Regards,

Deb
 
Debraj Roy


Yes I did recommend you use the public folder..............but now I have the public folder link download ok :)
 
Good Afternoon Luke M

I have been trying to use your recommendation forensic 3 but I am making a total mess of it and would be grate full if you could look at it and give pointers.


https://dl.dropbox.com/u/75495784/Copy%20of%20Luke-1a.xlsm
 
Good evening colinread and DebajRoy


colinread many thanks for your help am reading and thanks to DebajRoys workbook understanding. My thanks to you both
 
Good evening kaushik03

My most humble apologize my friend but I got total lost and messed up with your code, not you fault all mine
 
Hi, b(ut)ob(ut)hc!

You ask for it, you get it. That's SUR, service upon request.

Give a look at this:

https://dl.dropbox.com/u/60558749/Multi%20return%20of%20data%20for%20one%20name%20with%20vlookup%20%28for%20bobhc%20at%20chandoo.org%29.png

Regards!
 
SirJB7 Good evening my Friend

I think it is because I mentioned rugby in my last post that you made sure the link would not work.........but I forgive
 
Hi, b(ut)ob(ut)hc!

About rugby... Touchée, mon ami. But it's now working. DropBox full, made place yet.

Regards!

PS: hope you enjoy it :)
 
SirJB7 there are many skills that people can learn..............but to make some laugh out loud is a skill from mother nature, you have the skill. :)
 
Hi Bob..


Please remove the second parameter from Index Function.. beside that everything is fine..

=IF(COUNTIF(A:A,$G$2)<ROWS($H$2:H2),"",INDEX(B:B,E:E,SMALL(IF($A$2:$A$40=$G2,ROW($A$2:$A$40)),ROW(A1))))


and use Ctrl+Shift+Enter..


https://dl.dropbox.com/u/78831150/Excel/Multi%20return%20of%20data%20for%20one%20name%20with%20vlookup%28for%20bobhc%29.xlsm


Hi JB-007,

No it still not showing correct pointer.. they all are "old dog"
 
Hi, b(ut)ob(ut)hc!

I always say I'm unique.

Regards!

PS: Friends always add "luckily"

PS 2: Colleagues always add "thank god"

PS 3: Family always add "and it's more than enough!"
 
Debraj Roy

My thanks I thought I had really messed up, down to a couple of "e"s


P.S


Your humor comes from the same box as SirJB7 and myself :)
 
@Debraj Roy

Hi!

There's only one old dog here. And it's like Matrix 2, reloaded and fully new tricked.

All the rest of us are just little puppies.

Regards!

PS: Argentine humor, I guess...
 
You must log in or register to reply here.
Back
Top