Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to extract the data
Hi, Please find below are the two tables, one is the master table and other is the entry table. The master table contains size(1,2,etc),spec(A!,A2,etc),material code(E90,E45,etc) and description. The entry table contains size,spec,material code. What I want to do is, with the size,spec and material code in the entry table has to check the corresponding size,spec and material code in the master table and retrieve the description aginst these matching column. Could anybody help me how to go on this MASTER TABLE DESCRIPTION SPECMATERIALCODESIZE ELBOW,90LR,WRT,BW,XS,ASTM A234 GR.WPB,ASME B16.9 A1 E90 2 ELBOW,45LR,WRT,BW,XS,ASTM A234 GR.WPB,ASME B16.9 A1 E45 2 ELBOW,90LR,WRT,SW,3000#,ASTM A105,ASME B16.11 A1 E90 1 ELBOW,45LR,FRGD,SW,3000#,ASTM A105,ASME B16.11 A1 E45 1 ELBOW,90LR,WRT,BW,STD,ASTM A234 GR.WPB,ASME B16.9 A2 E90 2 ELBOW,45LR,WRT,BW,STD,ASTM A234 GR.WPB,ASME B16.9 A2 E45 2 ENTRY TABLE SPEC MATERIAL CODE SIZE A1 E90 2 A1 E45 2 Regards, Shiva -- Shiva ------------------------------------------------------------------------ Shiva's Profile: http://www.excelforum.com/member.php...o&userid=28449 View this thread: http://www.excelforum.com/showthread...hreadid=480504 |
#2
|
|||
|
|||
How to extract the data
Hi
Into Master table, add a new column as leftmost (A:A), you can name it p.e. ID I wasn't able to decipher, which columns contain material code, specification, and size, so let's assume they are columns X:Z Into A2, enter the formula =IF(OR(X2="",Y2="",Z2=""),"",X2&"/"&Y2&"/"&Z2 and copy down for number of rows you think as reasonable. On Entry sheet, to retrieve information from master table, use VLOOKUP formulas like this =IF(OR(A2="",B2="",C2="",ISERROR(VLOOKUP(B2&"/"&A2&"/"&C2,Master!$A$2:$Z$1000,2,0))),"",VLOOKUP(B2& "/"&A2&"/"&C2,Master!$A$2:$Z$1000,2,0)) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Shiva" wrote in message ... Hi, Please find below are the two tables, one is the master table and other is the entry table. The master table contains size(1,2,etc),spec(A!,A2,etc),material code(E90,E45,etc) and description. The entry table contains size,spec,material code. What I want to do is, with the size,spec and material code in the entry table has to check the corresponding size,spec and material code in the master table and retrieve the description aginst these matching column. Could anybody help me how to go on this MASTER TABLE DESCRIPTION SPECMATERIALCODESIZE ELBOW,90LR,WRT,BW,XS,ASTM A234 GR.WPB,ASME B16.9 A1 E90 2 ELBOW,45LR,WRT,BW,XS,ASTM A234 GR.WPB,ASME B16.9 A1 E45 2 ELBOW,90LR,WRT,SW,3000#,ASTM A105,ASME B16.11 A1 E90 1 ELBOW,45LR,FRGD,SW,3000#,ASTM A105,ASME B16.11 A1 E45 1 ELBOW,90LR,WRT,BW,STD,ASTM A234 GR.WPB,ASME B16.9 A2 E90 2 ELBOW,45LR,WRT,BW,STD,ASTM A234 GR.WPB,ASME B16.9 A2 E45 2 ENTRY TABLE SPEC MATERIAL CODE SIZE A1 E90 2 A1 E45 2 Regards, Shiva -- Shiva ------------------------------------------------------------------------ Shiva's Profile: http://www.excelforum.com/member.php...o&userid=28449 View this thread: http://www.excelforum.com/showthread...hreadid=480504 |
#3
|
|||
|
|||
How to extract the data
Hi Arvi Laanemets, Great, It is working!!! Thank you very much. Warm regards, Shiva -- Shiva ------------------------------------------------------------------------ Shiva's Profile: http://www.excelforum.com/member.php...o&userid=28449 View this thread: http://www.excelforum.com/showthread...hreadid=480504 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract data from a cell in a formula in another cell | Excel Worksheet Functions | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
Data extract from access query | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Extract Data | Excel Discussion (Misc queries) |