Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for a value that repeats several times in another table
Hi, i have a table that looks as follows:
CUSTID CUSTNAME ADDRESS 123 customer1 The street 345 customer2 The road And I have another table that looks as follows CUSTID PRODUCT VOLUME 123 z-Units 13 123 a-units 25 345 z-units 10 345 b-units 5 123 b-units 12 And I need and output table that loos as follows: CUSTID CUSTNAME ADDRESS PRODUCT VOLUME 123 customer1 The street z-units 13 123 customer1 The street a-units 25 123 customer1 The street b-units 12 345 customer2 The road z-units 10 345 customer2 The road b-units 5 I have try to do this with VLOOKUP, and also with a combination of OFFSET and MATCH, but I always get an N/A. As tbale 1 and table 2 can have variable lenghts, I wonder what can I do. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for a value that repeats several times in another table
Hi,
1. Copy your second table to A1 of a new sheet and insert 2 columns between B:C 2. In cell B2 enter the formula =VLOOKUP($A2,SourceSheet!$A$2:$C$3,2,FALSE) Copy this formula down. Copy the first formula over to C2 and change the 2 to a 3 =VLOOKUP($A9,SourceSheet$A$2:$C$3,3,FALSE) and copy it down. This assumes that the SourceSheet is where your first table is and A2:C3 contains the data. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "RobertoB" wrote: Hi, i have a table that looks as follows: CUSTID CUSTNAME ADDRESS 123 customer1 The street 345 customer2 The road And I have another table that looks as follows CUSTID PRODUCT VOLUME 123 z-Units 13 123 a-units 25 345 z-units 10 345 b-units 5 123 b-units 12 And I need and output table that loos as follows: CUSTID CUSTNAME ADDRESS PRODUCT VOLUME 123 customer1 The street z-units 13 123 customer1 The street a-units 25 123 customer1 The street b-units 12 345 customer2 The road z-units 10 345 customer2 The road b-units 5 I have try to do this with VLOOKUP, and also with a combination of OFFSET and MATCH, but I always get an N/A. As tbale 1 and table 2 can have variable lenghts, I wonder what can I do. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for a value that repeats several times in another table
Assuming these tables are on different sheets in the same workbook, first of
all insert new columns B and C in the second sheet/table and enter CUSTNAME in B1 and ADDRESS in C1. Then put this formula in B2: =VLOOKUP($A2,Sheet1!$A:$C,COLUMN(B1),0) Copy it into C2, and then copy B2:C2 down for as many rows as you need. Hope this helps. Pete "RobertoB" wrote in message ... Hi, i have a table that looks as follows: CUSTID CUSTNAME ADDRESS 123 customer1 The street 345 customer2 The road And I have another table that looks as follows CUSTID PRODUCT VOLUME 123 z-Units 13 123 a-units 25 345 z-units 10 345 b-units 5 123 b-units 12 And I need and output table that loos as follows: CUSTID CUSTNAME ADDRESS PRODUCT VOLUME 123 customer1 The street z-units 13 123 customer1 The street a-units 25 123 customer1 The street b-units 12 345 customer2 The road z-units 10 345 customer2 The road b-units 5 I have try to do this with VLOOKUP, and also with a combination of OFFSET and MATCH, but I always get an N/A. As tbale 1 and table 2 can have variable lenghts, I wonder what can I do. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for a value that repeats several times in another table
Hi,
I did not mention that you can't build the entire table off of the first column using VLOOKUP because there are repeat entries. As a side - when you say you used a formula and it didn't work, it's often best to show us the formula. -- Thanks, Shane Devenshire "RobertoB" wrote: Hi, i have a table that looks as follows: CUSTID CUSTNAME ADDRESS 123 customer1 The street 345 customer2 The road And I have another table that looks as follows CUSTID PRODUCT VOLUME 123 z-Units 13 123 a-units 25 345 z-units 10 345 b-units 5 123 b-units 12 And I need and output table that loos as follows: CUSTID CUSTNAME ADDRESS PRODUCT VOLUME 123 customer1 The street z-units 13 123 customer1 The street a-units 25 123 customer1 The street b-units 12 345 customer2 The road z-units 10 345 customer2 The road b-units 5 I have try to do this with VLOOKUP, and also with a combination of OFFSET and MATCH, but I always get an N/A. As tbale 1 and table 2 can have variable lenghts, I wonder what can I do. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use pivot table to count repeats of unique fields | Excel Discussion (Misc queries) | |||
adding up times x occurs, excluding if y repeats in a different co | Excel Discussion (Misc queries) | |||
Pivot Table Repeats Names in multiple rows | Excel Discussion (Misc queries) | |||
need to search a table such as distance table in road maps. | Excel Discussion (Misc queries) | |||
How can I count the number of times a letter repeats in a string? | Excel Worksheet Functions |