VLOOKUP and multiple rows ?

I have two worksheets:
#1 contains client numbers and data. 
client no.   data 
1             xyz
2             abc 
3             def
4             wed  

#2 contains client numbers and the employees attached to 
the client. 
client no     employee 
1              joe
1              tom
2              jim  
3              ann

**Here's my problem! Is there a way to do a lookup on 
spreadsheet # 1 to bring in the employee data from 
spreadsheet #2 (a normal VLOOKUP) and if there are mutiple 
employees to either concatenate the data->

client no  employee 
1           joe,tom


or put it into a new column -->
client no   employee   employee 
1           joe        tom 


I have tens of thousands of data items and this is 
becoming a major issue. 
Thanks for you help!

-Joe

..


0
7/18/2003 3:28:17 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
838 Views

Similar Articles

[PageSpeed] 34

try this: Adjust sheets & ranges to suit.

Sub listem2()
For Each cel In [sheet17!a21:a25] '1st list to add info to
 mystr = ""
  With Worksheets("sheet16").Range("b16:b22")'2nd sheet source
    Set c = .Find(cel, LookIn:=xlValues)
    If Not c Is Nothing Then
       firstaddress = c.Address
        Do
         mystr = mystr & c.Offset(, 1) & " "
        Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
'MsgBox mystr
 cel.Offset(, 2) = mystr
  End With
Next cel
End Sub

-- 
Don Guillett
SalesAid Software
Granite Shoals, TX
donaldb@281.com
"Joe" <jlazauskas@yahoo.com> wrote in message
news:010e01c34d41$36319d20$a101280a@phx.gbl...
> I have two worksheets:
> #1 contains client numbers and data.
> client no.   data
> 1             xyz
> 2             abc
> 3             def
> 4             wed
>
> #2 contains client numbers and the employees attached to
> the client.
> client no     employee
> 1              joe
> 1              tom
> 2              jim
> 3              ann
>
> **Here's my problem! Is there a way to do a lookup on
> spreadsheet # 1 to bring in the employee data from
> spreadsheet #2 (a normal VLOOKUP) and if there are mutiple
> employees to either concatenate the data->
>
> client no  employee
> 1           joe,tom
>
>
> or put it into a new column -->
> client no   employee   employee
> 1           joe        tom
>
>
> I have tens of thousands of data items and this is
> becoming a major issue.
> Thanks for you help!
>
> -Joe
>
> .
>
>


0
Don
7/18/2003 5:13:07 PM
Reply:

Similar Artilces:

Unhide rows in an Excel Macro
The worksheet has 120 rows required for data entry - this takes place in 4 row increments. (Row 1 is an Information row - no data input) Data input begins on row 2. The worksheet is protected except for specific cells. So, leaving the first 4 rows available for input, and the remaining rows hidden, I need a macro that will execute after the last cell has been input, and the enter key pressed, that will display the next 4 rows. The macro must terminate execution after row 121 has been displayed as there is more information and calculations that occur in the rows following. ...

Multiple subform issues
G'day all, I am using Access 2000 under Win2000 and encountering some exceedingly strange errors with some of the subforms in my solution. The usual error is for Access to freeze upon a requery of the subform after editing one of the subform records. I have set things up so that the user has to explicitly modify all records so that I can track changes and use permissions to restrict the ability to edit data. A stopgap measure is currently in place until I can set up all of the users' machines properly - I show a form in Dialog mode and ask for a password, close the form and then take...

Print selected rows of Listview ?
Anybody have any examples on how to add "Print Selected" to MFC's stadard print dialog? My app's ListView frequently has hundreds of rows and most users only need to print a few! I found out how to enable the "Print Selected" button but not sure how to pass the selected rows to print. I am using FULLROWSELECT so I could print selected rows one row at a time but I am not sure where to start. TIA Bill ...

Multiple Users desiring separate inbox and calendar
I have set up 2 users on one PC using Microsoft 2000/Windows 98. I thought when I set this up, each user would have their own inbox and calendar. However, I have realized that they are still the same inbox and calendar. Have I missed a step somewhere? Thanks in advance for any help. kay (tn) wrote: > I have set up 2 users on one PC using Microsoft 2000/Windows 98. I > thought when I set this up, each user would have their own inbox and > calendar. However, I have realized that they are still the same inbox > and calendar. Have I missed a step somewhere? Thanks in advance ...

Deliver mail from one address to multiple users?
Is it possible to configure Exchange 2003 to send (or forward) email that comes in for a certain email address to two different users on our system? If so, how can I make this happen? For example, we have an email address "sales@mycompany.com" and we have two different users "User1" and "User2" who both need to see the messages that are sent to this particular address. Of course they both still have their own individual email accounts that do not need to be shared. Currently, this sales address is configured as an additional SMTP address for User1, but if ...

how to protect appointed rows or columns to use the mouse and keyboard
hzh, You could include some text in your post. Maybe then we could figure out what you mean by "appointed"?? "To use the mouse and keyboard" makes absolutely no sense whatsoever. Maybe you're a new poster??? If so, take a look here before reposting: http://www.cpearson.com/excel/newposte.htm Most anything that could be asked in the newsgroups has already been answered. Take a look here and you may just find what you're looking for (whatever that is)?? http://tinyurl.com/29pby John "hzh" <hzh62@163.com> wrote in message news:7A2382EE-00E6-4679...

sumproduct and even numbered rows
Hi all, In column C I have amounts (e.g. 2345,89) in even numbered rows beginning with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in odd numbered rows, C5 up to C99. For instance: C4 123.45 C5 8 C6 33.91 C7 3 C8 0.88 C9 3 C10 14.47 C11 28 C12 3 C13 16 etc. In H8 I have one of the codenumbers, say 3. I want in H9 the sum of all amounts that are followed by that codenumber. That sum is in the example above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a codenumber, because C12 is an even numbered row). I tried the following ...

Update copies of contacts in multiple folders
I have copies of contacts copied to multiple folders, such as CONTACTS and FAMILY. When I edit one copy the other is not updated. How do I synchronize all copies of a contact? -- JK Well this is because these are separate items and they don't hold a link. You can't synchronize those folders. You are probably better of assigning categories to your contacts and then use views to show only a specific category. This way you don't have to duplicate your items either. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -...

Item search on multiple folders in exchange public folder
I am trying to perform an item search on multiple folders that was created in the Exchange Public folder, how can I do it? I am using outlook 2002 that access Exchange Public folder in Exchange server 5.5. Thanks for your help, SH ...

Multiple installations in one Domain?? MS CRM 1.2!
Hello, we have the following problem: We need a MS CRM of 1.2 server for some tests. We had installed a 1.2 Server in former times. We have since deinstalled the Server. Now we wanted to install CRM on another server. But MS CRM 1.2 wants to start the services of the first server. We have problems with the access to the ActiveDirectory. But we have all rights! A tried Redeployment breaks off while connecting the IIS. Any ideas? Greetings, Stefan I responded on the crm sandbox site. ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "...

inserting rows at top
I've put together a gas mileage spreadsheet. It's real simple. I enter a row and it calculates the mileage. The thing is, it gets it's beginning odometer reading from the ending odometer reading of the previous entry. And rather than start at the top of the spreadsheet and add new entries at the bottom, what I'd like to do is add entries at the top. How do I do this, and have my formulas in the new row? 1/3/2006 2.079 41.503077 19.963 168139 168589 450 22.54170215 168139 These are rows 2 and 3. I have a header row (1). How do I insert a new row at 2, that wil...

Find Value and delete rows above
Hello everyone, Ive got a sheet which draws data in via a web query, It then pull all the info and puts it into my sheet down the sheet in rows.... Depending on which page of my site it draws it from.. depends where the starting point in..... for example... Page 1 = A1 = blah blah 1 B1 = blah blah 2 C1 = Start D1 = Data E1 = Data F1 = End G1 = blah blah 3 Page 2 = A1 = blah blah 1 B1 = blah blah 2 C1 = blah blah 3 D1 = Start E1 = Data F1 = End G1 = blah blah 3 Is there a way I could get it to strip out everything from the START and above, and everything from END and below no matter how ...

multiply a row by a certain number?
i am having trouble with excel i want to multiply this row by 1.4 and make it appear next to it if anyone can help thank you if you mean multiply each item in a column then this will work. Modify to suit Sub multiplyall() For Each c In Range("c7:c10") c.Offset(, 1) = c * 1.4 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "multiply a row by a certain number?" <multiply a row by a certain number?@discussions.microsoft.com> wrote in message news:C84A6F67-03CD-4902-9760-36051A179831@microsoft.com... > i am having trouble with excel i want to multipl...

SumIF-multiple conditions/OR
I have a range of cells that I want to ck for a "Y", then in the next range can be "STOP" or a value of "Go"? Kinda a mix of conditions. A bit more detail, perhaps. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "GaryW" <GaryW@discussions.microsoft.com> wrote in message news:4191FD5D-C409-47AD-87ED-EE077D9A7D6B@microsoft.com... >I have a range of cells that I want to ck for a "Y", then in the next range > can be "STOP" or a value of "Go"? Kinda a mix of conditions. &quo...

Payees with Multiple Account Numbers
All, I have 2 different payees in my banking website (Bank of America) and I have 2 different account numbers for each payee. That's a total of 4 different account numbers. I need to know how to setup Money 06 correctly so that each bill pay goes to the correct account. Am I seriously going to have to setup different names or hows is this going to work? Regards, Nathan Bell "Nathan Bell" <nathan.bell@verizon.net> wrote in message news:uXtPQpBEGHA.1384@TK2MSFTNGP11.phx.gbl... > All, > > I have 2 different payees in my banking website (Bank of America)...

copy and paste a row doesn't get it all
I use a 7 page wide spread sheet for text data setup with many columns. I frequently need to copy a full row from one spreadsheet to another spreadsheet. I use the mouse to highlight the entire row, right click, then copy, and paste into the new spreadsheet. I recently have noticed that not all the cells are copied even though the highlighted area goes all the way across the spread sheets. And the cells are the same number of cells where I paste the info. Is there a sure-fire-way to be sure I copy and paste the whole row of cells from one spreadsheet into another? -- FW When you sele...

Multiple Criteria for SUMIF
I have the following list of info: !----A----!----B----!----C----!----D----! ! 2/3/05 ! 114 ! 4.5 ! ! ! 2/3/05 ! 114 ! 3.2 ! ! ! 2/3/05 ! 115 ! 5.7 ! ! ! 2/4/05 ! 113 ! 8.0 ! ! ! 2/4/05 ! 114 ! 4.5 ! ! ! 2/4/05 ! 114 ! 4.6 ! ! ! 2/5/05 ! 114 ! 6.7 ! ! This is where Column A is the date worked, B the employee number, and C the amount of hours split into different job types. Fo...

Pull data from multi-row records
I've got about 50 records that were imported. Each record has about 30 rows. A portion of one would look something like: 082 04$a813/.54$222 049 \\$aLVGA 100 1\$aKelton, Elmer. 245 10$aRanger's trail$h[electronic resource] /$cElmer Kelton. 260 \\$aPrince Frederick, Md. :$bRecorded Books ;$a[Boulder, Colo. :$bMade available electronically by] NetLibrary,$c2005. 300 \\$a1 sound file (9 hr.) :$bdigital, wma file. 440 \0$aLone Star audio 490 1\$aTexas Rangers series ;$v4 538 \\$aSystem requirements: Windows Media Player compatible, check NetLibrary we...

Clueless as to how to write VLOOKUP formula
I have just started a summer class that is basically an Excel class. The teacher doesn't really show us how to do anything and I am a novice excel user at best. I have a lab that requires us to use VLOOKUP and I am totally lost. Anyone have any tips on a training website or where I could find this information. :mad: -- moodwalker ------------------------------------------------------------------------ moodwalker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24055 View this thread: http://www.excelforum.com/showthread.php?threadid=376598 moodwalke...

Create view to get data from multiple databases
I have 26 databases database, Database,......Database. I have a view as below that I want to modify so that it can get data from all 26 databases. I would appreciate some help on this. create view Unpostedallcompany as select cpnyid Periodpost, jrnltype, trantype,origcpnyid,acct,sub, perent,refnbr, trandate, trandesc,dramt, cramt from gltran order by cpnyid Thanks sunny > I have a view as below that I want to modify so that it can get data from > all 26 databases. I would appreciate some help on this. > > create view Unpostedallcompany as > select ...

Formatting or Grouping multiple rows based on unique values
How can I format an excel worksheet to draw a border around a range of cells that a) contain a unique value in one column b) only where a value in another column matches one of another group of "valid" values. For example, my source data is: A B C D 2 UC 1 DD 0 3 UC 1 DD 0 4 UC 1 DD 1 6 UC 2 DD 1 7 UC 2 SDM 0 8 UC 2 MD 0 9 UC 2 DD 1 11 UC 3 DD 1 12 UC 3 SDR 0 13 UC 3 RD 0 14 UC 3 DD 1 I would like to draw a border around any unique UCs that also have either MD or RD or SDR in column C. In addition, I might like to highlight row 8 because ...

Multiple Copies of Store Operations on a Single System
We have one store, but we also have an offsite warehouse. Currently we are using a single copy of RMS Store Operations to manage our business. We have tried to use the offline inventory function in RMS to track stock at the warehouse. This is not working very well, in part because when we transfer inventory from offline to the store, we have to transfer all of the inventory or none of it, which is not what actually happens (we might transfer one or two of something, rather than all of it). In addition to the warehouse inventory, we want to track our store display inventory separatel...

Print wide chart over multiple pages?
Thanks you in advance for any help here. I am building a rather wide chart with four turn-around-time statistics for each of 40 facilities. I currently have them set up as 4 columns per facility, but need to have it print over multiple pages. Any thoughts on how to do this so the results are readable, and the x:y axis labels are there? Thanks! -Bill, in sunny San Francisco. ...

eliminating zero-value rows
Greetings, A series of rows comprise a product list we are using (eg "Item X, Item Y, Item Z"). A column next to these products is used to designate the quantity of each item we will need for a particular job. So, we might put a "3" in the column next to Item X, or a "0" next to Item Y. I would like to then have a tab that lists all the materials we need for a job, and their quantities, without having the blank rows if the quantity is zero. Any ideas how to accomplish this? Thanks for your help. Cheers, Scot B. I'd keep all my data in place an...

Inserting another excel sheet(rows&columns)into an...
How do I insert another excel sheet--(which includes several cells--into one cell block of another excel sheet? These cells only consist of text and need to be formatted to "shrink" or autofit into the one celled excel so they are readable. Thanks for your help Cells can contain values, but not other cells. One way to work around this is to copy a range and paste it in as a picture (hold the SHIFT key down when selecting the Edit menu), sizing appropriately. You'll have to edit the cells in place, but their content will show up where you've pasted the picture. In...