Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to concatenate record from 2 tables
I need to add data to record from a different table - Example:
Table 1 contains: Custmer, Invoice Number, Shipment Number, Product Number, and many other details Table contains: Custmer, Invoice Number, Product Number and some data that are not exist in table 1 such as Amount left to be shipped Is there a formula that I cann find the correct data that match 3 parmeters in table 1 and to bring it from table 2 to table 1 ? hope I was clear enough' thanks NK |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to concatenate record from 2 tables
Look he
http://www.contextures.com/xlFunctio...tml#IndexMatch Check out Example 2 and Example 3. Your specific scenario may be a little more complicated, but YES you can do it! I think the limit is like 31 conditions, or something like that. Regards, Ryan-- -- RyGuy "Negda" wrote: I need to add data to record from a different table - Example: Table 1 contains: Custmer, Invoice Number, Shipment Number, Product Number, and many other details Table contains: Custmer, Invoice Number, Product Number and some data that are not exist in table 1 such as Amount left to be shipped Is there a formula that I cann find the correct data that match 3 parmeters in table 1 and to bring it from table 2 to table 1 ? hope I was clear enough' thanks NK |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to concatenate record from 2 tables
I have done this many times by adding a column to the left of the data,
concatenating the pertinent data to create a unique string and then using the vLookUp function. For example: Assume, after adding a new column A, that your Customer data is in Column B, your Invoice Number is Column C and your Product Number is Column D. In Column A concatenate the three (3) key columns (B, C and D) with a forumla in Cell A2 similar to: =B2&C2&D2 Copy the formula down and convert the column to values using Copy and PasteSpecial as Values. Do a similar step in the 2nd worksheet &/or workbook. Now utilize the vLookUp function using the new columns as the "lookup_value" and the "table_array" as the left-most column of the other data. For example: if you have your data in worksheet: Sheet(1) and Sheet(2) and you want to return the data that is in the 5th column of Sheet(2) where column A of both worksheets contain the "lookup_value", the vLookUp function would be: =VLOOKUP($A2,'Sheet (2)'!$A$2:$E$103,5,FALSE) Where 'Sheet (2)'!$A$2:$E$103 is the range containg the data on Sheet(2) I hope this is of some help. Good Luck. "Negda" wrote: I need to add data to record from a different table - Example: Table 1 contains: Custmer, Invoice Number, Shipment Number, Product Number, and many other details Table contains: Custmer, Invoice Number, Product Number and some data that are not exist in table 1 such as Amount left to be shipped Is there a formula that I cann find the correct data that match 3 parmeters in table 1 and to bring it from table 2 to table 1 ? hope I was clear enough' thanks NK |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula to concatenate record from 2 tables
Here is a method that uses
Pivot Table and VBA and no formulas. Excel 2003. http://www.freefilehosting.net/download/3ea76 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare data from two tables with varying record numbers | Excel Worksheet Functions | |||
Formula to record the date | Excel Worksheet Functions | |||
CONCATENATE Formula | Excel Discussion (Misc queries) | |||
edit a record like 17.00 gbp so it becomes 17.00 using a formula | Excel Discussion (Misc queries) | |||
Evaluating results of a concatenate formula, as a formula | Excel Worksheet Functions |