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
801 Views

Similar Articles

[PageSpeed] 10

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:

Multiple Search Criteria/ Index Match
I am using the following formula to retrieve data that matches tw specific criteria. =INDEX(C2:C80,MATCH(1,(A2:A80="Liverpool")*(B2:B80="January"),0)) using ctrl,shift, enter to give curly brackets to make it work. This formula is then repeated in the cell directly below, but searche for "February", below that "March" and so on. However when I copy my formula, I have to change the month for eac cell and therefore have to put in the curly brackets again. Is there anyway around this -- Message posted from http://www.ExcelForum.com Have a list with the ...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Multiple charts in ChartSpace; problems with double Categories
I want to show two or more charts with different categories and data in one chartspace, so I did the following: 1. Create Chartspace 2. Add Chart1 in Chartspace 3. Add Series in Chart1 4. Series.SetData chDimCategories 'A,B,C' 5. Series.SetData chDimValues '5,2,6' Now I see a chart with categories A (value=5), B (value=2) and C (value=6). So everything okay. Next thing I do: 6. Add Chart2 in Chartspace Now I see a second empty chart, BUT WITH already filled categories A,B and C!! I don't want this, because I want to use other categories in this second chart2. And whe...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Turn off auto adjust of row heights
I have a complex spreadsheet for my catalogue of products. I have set row heights, fonts and sizes of texts for different rows. Every now and then my row heights are automatically changed by EXCEL. I have turned off word wrap I have locked/protected my sheet. How can I stop EXCEL automatically readjusting row heights at its discretion. Thanks ...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Inserting Rows
Hi, I have 500+ rows of data that I would like to insert a blank row in between each row. Is there a globabl way of doing it rather than having to click on each row one at a time and then selecting 'insert row'? Thanks Ben Hi Ben have a look at http://tinyurl.com/2pt5r Frank Ben wrote: > Hi, > > I have 500+ rows of data that I would like to insert a > blank row in between each row. Is there a globabl way of > doing it rather than having to click on each row one at a > time and then selecting 'insert row'? > > Thanks > > Ben Thanks...

Forwarding Multiple Emails to AOL
I have about 2,400 email messages saved on Outlook that I need to transfer to an AOL Email account. How can I do this without emailing each individually? "awerhun" <awerhun@discussions.microsoft.com> wrote in message news:B2B78760-FADA-47C9-BF21-1597E345CF71@microsoft.com... >I have about 2,400 email messages saved on Outlook that I need to transfer to > an AOL Email account. How can I do this without emailing each individually? Simply create your AOL account in Outlook, then drag the messages to the AOL folders. -- Brian Tillman [MVP-Outlook] O...

Create Multiple Index
Access 2007 I want to build a multiple index on a table in my database. Can anyone tell me why this does not work? Private Sub Command0_Click() DoCmd.RunSQL "CREATE INDEX Models ON tblCatalogModels, (Year, Make, Model)" End Sub "NEWER USER" <NEWERUSER@discussions.microsoft.com> wrote in message news:88DBF188-1446-43D3-8C5F-24C9E3728097@microsoft.com... > Access 2007 > I want to build a multiple index on a table in my database. Can anyone > tell > me why this does not work? > > Private Sub Command0_Click() > > DoCmd.Ru...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

multiple item select from a combo box
I want to use this as a navigational tool, rather than to "input" values into a database. This control will be on a form that sets parameters for graphical chart displays. One of the requirements is to be able to select multiple values from a 2nd combo box after a 'parent' 1st combo box determines what should be in the 2nd one. I have a couple of ideas of how to do this, but was just wondering if someone has already tackled something like this before. I suppose I could populate a temporary table that has yes/no values in it and display that in a subform (which scroll...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

Publisher 2000-XP multiple pages not visible
Hi, I am new to this group seeking some help. I was working on a large number of pages that I had scanned images onto and entered text. In one document, I can see the first page only and not the other 6. When I print, it prints out the pages correctly, but I cannot view these pages, and needless to say, make any corrections. My other documents, which have 100 pages in them, show nothing at all. They also print. When I made a new document to see what was going on, I can Insert new pages, and nothing shows up on the bottom of the screen to show the pages. In My Documents, the file sizes ind...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

Combining multiple files into one work sheet
Good afternoon, I have multiple files single worksheet files (30), each in it's own directory that I would like to have combined into a single workbook. Each file name is unique, however the midships of each file contains the date the file was created "C:\XVG001\XVG001_20090713_Reviewed.xls". Is there any way I can create a function or macro that will pull these files from their respective directories and combine these sheets automatically into one workbook? Thanks Try this add-in http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/t...

VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...

incorporate paste value in vlookup
is there a way to incorporate "paste value" in a vlookup formula ? I have the vlookups in the column working and feeding off of the multiple worksheets. the data will be updated weekly (and new columns with the updated data added) and i would rather just copy new data on top of the old in the worksheets instean of having to add new worksheets with new data... as to keep the previous column from changing. -- problem ...

Finding Data from Multiple sheets
Hi I have a spreadsheet that contains the following: Rows = Products Columns = Months Data = how many products sold in each month This data is replicated on numerous sheets, each sheet is an individual client. I need to look at the products sold over each month, for each customer and identify - who sold the most per month? Ideally creating a list of the top ten customers. I have tried copy and pasting data from each sheet into one and used this as a database to create a pivot table, but the whole process of copying and pasting each clients data takes so long to do. Any advice would b...

delete a row
a macro which deletes a row if all the cells in that row from column to column Z are empty thanks a lo -- Message posted from http://www.ExcelForum.com Hi see: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows -- Regards Frank Kabel Frankfurt, Germany > a macro which deletes a row if all the cells in that row from column A > to column Z are empty > > thanks a lot > > > --- > Message posted from http://www.ExcelForum.com/ hi frank , i need a smaller command not big macro... thank u so muc -- Message posted from http://www.ExcelForum.com Hi either ...

printing a single row of info.
How can I print just one row of info from a work sheet that is normanlly about 20 rows and 75 columns? Select the area to be printed & go to Print dialogbox. Choose "Selection", then Print. Or select the cells to be printed & go to File>Print Area>Set Print Area, then Print. HTH |:>) "Gary" wrote: > How can I print just one row of info from a work sheet that is normanlly > about 20 rows and 75 columns? Thanks, but it prints on several pages, any ideas on how to print on one page Gary "CyberTaz" wrote: > Select the area to be p...

How do I change the row/column format in a macro to beyond letters?
Hi all, I recorded a very long VBA macro in Excel and the index of ranges is in letter format, for example, "M14:M19", etc. Now I am going to run this macro programmatically and automatically in a for loop and expand it from the left to the right so I want to change the "M" in the above example automatically. But after 26 letters, there will be AA, AB, etc. which is really hard to program. Is there a way to adapt the recorded macro (by changing as little as possible) to more than 26 letters. I really want to change as little as possible because I spent lots of time re...

vlookup inside an if statement?
-------------------------------------------------------------------------------- Hi everybody! I have to do a vertical lookup of an object that might or might not exist, in case it doesnt exist normally it gives back Ref! but the thing is that if this objet doesnt appear on the data base is because is zero. This Ref! is not allowing me to make a sum since this objet is part of it. Maybe with an example would be easier to explain. i have to pull from a database the number of cars sold of three brands A,B, C, In the database unless is one car of the type it will not appear. lets say we hav...

Nested vlookup
My formula is: =((VLOOKUP($W$5181,Download!$A$1:$X$3403,3,FALSE))+(VLOOKUP($W$5182,Download!$A$1:$X$3403,3,FALSE)))*0.001 The formula looks up the value in "$W..."and returns the amount of orders. I alwayshave values for the first part of the lookup, but niot the second. THe issue is that if there nothing to report back for the second lookup, it gives me an "#NA" errror instead of at least returning the value for the first lookup. Any help? Thanks, TJ Wrap both Lookups in their own ISNA() wrapper to trap for that error:- =(IF(ISNA(VLookup1),0,VLookup1)+IF(ISNA(VL...