|
Vlookup multiple value
Is there any way to lookup multiple answers to a lookup formula.
Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
Vlookup multiple value
With
Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookup multiple answers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
Vlookup multiple value
DRAT! My fingers not typing what I'm thinking!
Formula 1 should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A $5)+($A$2:$A$5<$D$1)*10^99,0),ROWS($E$1:$E1))),"" ) Formula 2 (the array formula) should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)), ROWS($E$1:$E1))),"") Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookup multiple answers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
Vlookup multiple value
On Apr 5, 9:02 pm, Ron Coderre
wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen |
Vlookup multiple value
On Apr 5, 9:06 pm, Ron Coderre
wrote: DRAT! My fingers not typing what I'm thinking! Formula 1 should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A $5)+($A$2:$A$5<$D$1)*10^99,0),ROWS($E$1:$E1))),"" ) Formula 2 (the array formula) should be: E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),INDEX( $B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)), ROWS($E$1:$E1))),"") Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Pls kindly sugess other formula is not properly |
Vlookup multiple value
It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen |
Vlookup multiple value
On Apr 6, 4:48 pm, Ron Coderre
wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible |
Vlookup multiple value
Hi Ron,
your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible |
Vlookup multiple value
Assuming that A2:B5 contains the data, try the following...
Let D1 contains the part number of interest, such as 123 E2: =COUNTIF(A2:A5,D2) F2, copied down: =IF(ROWS(F$2:F2)<=$E$2,INDEX($B$2:$B$5,SMALL(IF($A $2:$A$5=$D$2,ROW($A$2:$ A$5)-ROW($A$2)+1),ROWS(F$2:F2))),"") Hope this helps! In article .com, "deen" wrote: Is there any way to lookup multiple answers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me |
Vlookup multiple value
Hi, Deen
Please post the file to one of the free file hosting services. Then post its location here. That way the file will be available to anyone interested in working on a solution for you. Here are some free file hosting options: http://www.flypicture.com/ http://cjoint.com/index.php http://www.savefile.com/index.php *********** Regards, Ron XL2002, WinXP "deen" wrote: Hi Ron, your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible |
Vlookup multiple value
On Apr 6, 8:22 pm, Ron Coderre
wrote: Hi, Deen Please post the file to one of the free file hosting services. Then post its location here. That way the file will be available to anyone interested in working on a solution for you. Here are some free file hosting options:http://www.flypicture.com/http://cjo....com/index.php *********** Regards, Ron XL2002, WinXP "deen" wrote: Hi Ron, your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible Dear Ron, As per your instruction i has been send the file thru below http://www.savefile.com/files/615770 Regards Deen |
Vlookup multiple value
Hi, Deen
I looked at the file you made available. See if this works for you.... Using the sample file you posted (Eg_Data_vlookup.xls) On sheet 'F16' L2: =IF(TRIM(I2)="Serical No",L1+1,L1) Copy that formula down as far as you need A2: =IF(AND(ISNUMBER(D2),B2<"Total"),L2,"") Copy that formula down as far as you need On Sheet 'Data' I2: =IF(A2MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$ A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUN TIF($A$1:$A2,A2))) Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste into I3 and down as far as you need A2: 1 A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$20 0,Sheet1!A2)) Copy that formula down as far as you need D2: =INDEX('F16'!$B$1:$B$200,$I2) E2: =INDEX('F16'!$D$1:$D$200,$I2) F2: =INDEX('F16'!$E$1:$E$200,$I2) Copy those formulas down as far as you need Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at' fields. Using your sample file, these values were returned: Sr.No Date on TDS Rate at 1 22/04/05 8.602 2.2 1 13/05/05 51.634 2.2 1 13/06/05 40.876 2.2 1 08/07/05 67.782 2.2 1 12/08/05 24.24 2.02 2 07.01.06 121.2 2.02 2 12/08/05 68.175 2.02 2 09/09/05 88.88 2.02 2 08/10/05 82.82 2.02 (I'm guessing you have other sources for the name & address field and the 'Name' field) I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 6, 8:22 pm, Ron Coderre wrote: Hi, Deen Please post the file to one of the free file hosting services. Then post its location here. That way the file will be available to anyone interested in working on a solution for you. Here are some free file hosting options:http://www.flypicture.com/http://cjo....com/index.php *********** Regards, Ron XL2002, WinXP "deen" wrote: Hi Ron, your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible Dear Ron, As per your instruction i has been send the file thru below http://www.savefile.com/files/615770 Regards Deen |
Vlookup multiple value
On Apr 7, 8:10 pm, Ron Coderre
wrote: Hi, Deen I looked at the file you made available. See if this works for you.... Using the sample file you posted (Eg_Data_vlookup.xls) On sheet 'F16' L2: =IF(TRIM(I2)="Serical No",L1+1,L1) Copy that formula down as far as you need A2: =IF(AND(ISNUMBER(D2),B2<"Total"),L2,"") Copy that formula down as far as you need On Sheet 'Data' I2: =IF(A2MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$ A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUN TIF($A$1:$A2,A2))) Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste into I3 and down as far as you need A2: 1 A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$20 0,Sheet1!A2)) Copy that formula down as far as you need D2: =INDEX('F16'!$B$1:$B$200,$I2) E2: =INDEX('F16'!$D$1:$D$200,$I2) F2: =INDEX('F16'!$E$1:$E$200,$I2) Copy those formulas down as far as you need Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at' fields. Using your sample file, these values were returned: Sr.No Date on TDS Rate at 1 22/04/05 8.602 2.2 1 13/05/05 51.634 2.2 1 13/06/05 40.876 2.2 1 08/07/05 67.782 2.2 1 12/08/05 24.24 2.02 2 07.01.06 121.2 2.02 2 12/08/05 68.175 2.02 2 09/09/05 88.88 2.02 2 08/10/05 82.82 2.02 (I'm guessing you have other sources for the name & address field and the 'Name' field) I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 6, 8:22 pm, Ron Coderre wrote: Hi, Deen Please post the file to one of the free file hosting services. Then post its location here. That way the file will be available to anyone interested in working on a solution for you. Here are some free file hosting options:http://www.flypicture.com/http://cjo...tp://www.savef... *********** Regards, Ron XL2002, WinXP "deen" wrote: Hi Ron, your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible Dear Ron, As per your instruction i has been send the file thru below http://www.savefile.com/files/615770 Regards Deen Hi Ron, Can pls send Eg: excel sheet, bcoz that's help me to under stand comfort |
Vlookup multiple value
On Apr 7, 8:10 pm, Ron Coderre
wrote: Hi, Deen I looked at the file you made available. See if this works for you.... Using the sample file you posted (Eg_Data_vlookup.xls) On sheet 'F16' L2: =IF(TRIM(I2)="Serical No",L1+1,L1) Copy that formula down as far as you need A2: =IF(AND(ISNUMBER(D2),B2<"Total"),L2,"") Copy that formula down as far as you need On Sheet 'Data' I2: =IF(A2MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$ A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUN TIF($A$1:$A2,A2))) Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste into I3 and down as far as you need A2: 1 A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$20 0,Sheet1!A2)) Copy that formula down as far as you need D2: =INDEX('F16'!$B$1:$B$200,$I2) E2: =INDEX('F16'!$D$1:$D$200,$I2) F2: =INDEX('F16'!$E$1:$E$200,$I2) Copy those formulas down as far as you need Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at' fields. Using your sample file, these values were returned: Sr.No Date on TDS Rate at 1 22/04/05 8.602 2.2 1 13/05/05 51.634 2.2 1 13/06/05 40.876 2.2 1 08/07/05 67.782 2.2 1 12/08/05 24.24 2.02 2 07.01.06 121.2 2.02 2 12/08/05 68.175 2.02 2 09/09/05 88.88 2.02 2 08/10/05 82.82 2.02 (I'm guessing you have other sources for the name & address field and the 'Name' field) I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 6, 8:22 pm, Ron Coderre wrote: Hi, Deen Please post the file to one of the free file hosting services. Then post its location here. That way the file will be available to anyone interested in working on a solution for you. Here are some free file hosting options:http://www.flypicture.com/http://cjo...tp://www.savef... *********** Regards, Ron XL2002, WinXP "deen" wrote: Hi Ron, your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible Dear Ron, As per your instruction i has been send the file thru below http://www.savefile.com/files/615770 Regards Deen Dear Ron, Yes its great, But i need the result in F16, in the data sheet all data has been cotain, f16 is the certificate i want provide to the customer, i need the result in F16 sheet pls help. fyi http://www.savefile.com/files/615770 Details: In Data sheet i already have a data, i need produced the result in F16 sheet in same format, Eg: in F16 have Sr.No in column A:, once i enter sr.no: in column a:25,a: 77,a:130, weather they result produce in the column D:25-D:42 (based on how many record in data sheet) and E:25-E:42(based on how many record in data sheet) pls make ur formula complete reverse. sorry for distrub U. Regards, Deen |
Vlookup multiple value
Thanks for clarifying that. I inferred from the file you posted that you
might want the form filled in from the table, but your original post indicated differently. Based on that file, it appears that you are building a separate form for each person. That is a very inefficient way to proceed. I suggest building a master form which contains formulas to complete ALL fields by using an amount activity list AND other lists or cells. Otherwise, every time you add a person, you'll need to create a new form. In addition to the amount activity list, you'll need a list of your company's static information and a Contractor list that includes this information: ID Num Name Address PAN/GIN Num Tax ID etc Once that is done, it should be relatively easy to just select a person from a drop-down list and have all applicable fields in the form fill-in via formulas. Then post back for assistance with those formulas. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 7, 8:10 pm, Ron Coderre wrote: Hi, Deen I looked at the file you made available. See if this works for you.... Using the sample file you posted (Eg_Data_vlookup.xls) On sheet 'F16' L2: =IF(TRIM(I2)="Serical No",L1+1,L1) Copy that formula down as far as you need A2: =IF(AND(ISNUMBER(D2),B2<"Total"),L2,"") Copy that formula down as far as you need On Sheet 'Data' I2: =IF(A2MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$ A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUN TIF($A$1:$A2,A2))) Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste into I3 and down as far as you need A2: 1 A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$20 0,Sheet1!A2)) Copy that formula down as far as you need D2: =INDEX('F16'!$B$1:$B$200,$I2) E2: =INDEX('F16'!$D$1:$D$200,$I2) F2: =INDEX('F16'!$E$1:$E$200,$I2) Copy those formulas down as far as you need Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at' fields. Using your sample file, these values were returned: Sr.No Date on TDS Rate at 1 22/04/05 8.602 2.2 1 13/05/05 51.634 2.2 1 13/06/05 40.876 2.2 1 08/07/05 67.782 2.2 1 12/08/05 24.24 2.02 2 07.01.06 121.2 2.02 2 12/08/05 68.175 2.02 2 09/09/05 88.88 2.02 2 08/10/05 82.82 2.02 (I'm guessing you have other sources for the name & address field and the 'Name' field) I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 6, 8:22 pm, Ron Coderre wrote: Hi, Deen Please post the file to one of the free file hosting services. Then post its location here. That way the file will be available to anyone interested in working on a solution for you. Here are some free file hosting options:http://www.flypicture.com/http://cjo...tp://www.savef... *********** Regards, Ron XL2002, WinXP "deen" wrote: Hi Ron, your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible Dear Ron, As per your instruction i has been send the file thru below http://www.savefile.com/files/615770 Regards Deen Dear Ron, Yes its great, But i need the result in F16, in the data sheet all data has been cotain, f16 is the certificate i want provide to the customer, i need the result in F16 sheet pls help. fyi http://www.savefile.com/files/615770 Details: In Data sheet i already have a data, i need produced the result in F16 sheet in same format, Eg: in F16 have Sr.No in column A:, once i enter sr.no: in column a:25,a: 77,a:130, weather they result produce in the column D:25-D:42 (based on how many record in data sheet) and E:25-E:42(based on how many record in data sheet) pls make ur formula complete reverse. sorry for distrub U. Regards, Deen |
Vlookup multiple value
On Apr 9, 7:32 pm, Ron Coderre
wrote: Thanks for clarifying that. I inferred from the file you posted that you might want the form filled in from the table, but your original post indicated differently. Based on that file, it appears that you are building a separate form for each person. That is a very inefficient way to proceed. I suggest building a master form which contains formulas to complete ALL fields by using an amount activity list AND other lists or cells. Otherwise, every time you add a person, you'll need to create a new form. In addition to the amount activity list, you'll need a list of your company's static information and a Contractor list that includes this information: ID Num Name Address PAN/GIN Num Tax ID etc Once that is done, it should be relatively easy to just select a person from a drop-down list and have all applicable fields in the form fill-in via formulas. Then post back for assistance with those formulas. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 7, 8:10 pm, Ron Coderre wrote: Hi, Deen I looked at the file you made available. See if this works for you.... Using the sample file you posted (Eg_Data_vlookup.xls) On sheet 'F16' L2: =IF(TRIM(I2)="Serical No",L1+1,L1) Copy that formula down as far as you need A2: =IF(AND(ISNUMBER(D2),B2<"Total"),L2,"") Copy that formula down as far as you need On Sheet 'Data' I2: =IF(A2MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$ A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUN TIF($A$1:$A2,A2))) Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste into I3 and down as far as you need A2: 1 A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$20 0,Sheet1!A2)) Copy that formula down as far as you need D2: =INDEX('F16'!$B$1:$B$200,$I2) E2: =INDEX('F16'!$D$1:$D$200,$I2) F2: =INDEX('F16'!$E$1:$E$200,$I2) Copy those formulas down as far as you need Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at' fields. Using your sample file, these values were returned: Sr.No Date on TDS Rate at 1 22/04/05 8.602 2.2 1 13/05/05 51.634 2.2 1 13/06/05 40.876 2.2 1 08/07/05 67.782 2.2 1 12/08/05 24.24 2.02 2 07.01.06 121.2 2.02 2 12/08/05 68.175 2.02 2 09/09/05 88.88 2.02 2 08/10/05 82.82 2.02 (I'm guessing you have other sources for the name & address field and the 'Name' field) I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 6, 8:22 pm, Ron Coderre wrote: Hi, Deen Please post the file to one of the free file hosting services. Then post its location here. That way the file will be available to anyone interested in working on a solution for you. Here are some free file hosting options:http://www.flypicture.com/http://cjo...tp://www.savef... *********** Regards, Ron XL2002, WinXP "deen" wrote: Hi Ron, your are not able to undersatand what i was say, with ur permission shall i send excel sheet , that given clear structure. On Apr 6, 5:48 pm, "deen" wrote: On Apr 6, 4:48 pm, Ron Coderre wrote: It seems like you only want a copy of the original data, but with duplicate consecutive CodeNo values hidden. Here are a couple options: 1)Use a Pivot Table on Sheet2 with these settings: ROW: Code No, Amt DATA: Sum of Amt Double-click on the "Code No" field and set Totals to: None Table options: UNcheck Grand Totals for Rows and Columns The final Pivot Table will look like this: Sum of Amt Code No Amt Total F0001 100 100 10 10 20 20 F0002 50 50 60 60 70 70 200 200 The last step is to hide the Total column. or 2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted) Enter formulas on Sheet2 that simply refer to the cells on Sheet1 Example (on Sheet2): A1: =Sheet1!A1 B1: =Sheet1!B1 Then.... Select from A2 through the last data cell in Col_A <format<conditional formatting Formula is: =(A1=A2) Click the [format] button and set the font color to white Due to the CF, the duplicate values will be "invisible", because of the white fonts, and look like this: Code No Amt F0001 100 10 F0001 20 50 F0002 60 70 200 Alternatively, you might be able to just copy the original data onto Sheet2 and use the Conditional Formatting. I hope that helps. *********** Regards, Ron XL2002, WinXP "deen" wrote: On Apr 5, 9:02 pm, Ron Coderre wrote: With Col_A containing PartNums, beginning in Cell A2 and Col_B containing WorkOrders, beginning in cell B2 Then D1: (a part number) This regular formula returns the WorkOrders associated with that PartNum E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"") Adjust range references to suit your situation. Copy that formula down as far as you need. Alternatively, you could use this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter].....instead of just [enter]) E1: =IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "") Is that something you can work with? *********** Regards, Ron XL2002, WinXP "deen" wrote: Is there any way to lookupmultipleanswers to a lookup formula. Normally, Excel will stop at the first match it finds and return that as the answer when, in fact, there may be more than one answer. I want to find the other matches as well and list them separately. Here's an example. Part No. Work Order 123 6000 123 6010 130 7000 135 7050 Part No. 123 has 2 (production) work orders associated with it: 6000 and 6010. I want to do a lookup of Part No. 123 and find ALL the work orders associated with it (listed separately, of course). Is there any way to do I want Data Excel sheet2? Pls help Me Dear Ron, Thx for your quick reply, i am not able to understand the formula, Eg: In Sheet1 i have data like Code No Amt F0001 100 F0001 10 F0001 20 F0002 50 F0002 60 F0002 70 F0002 200 I Need result in sheet2 like, Code No Amt F0001 100 10 20 F0002 50 60 70 200 Pls Help Me, Regards Deen Thx Mr.Ron, With use of macro they have any possible Dear Ron, As per your instruction i has been send the file thru below http://www.savefile.com/files/615770 Regards Deen Dear Ron, Yes its great, But i need the result in F16, in the data sheet all data has been cotain, f16 is the certificate i want provide to the customer, i need the result in F16 sheet pls help. fyi http://www.savefile.com/files/615770 Details: In Data sheet i already have a data, i need produced the result in F16 sheet in same format, Eg: in F16 have Sr.No in column A:, once i ... read more » Hi Ron, can pls sugess there any way do like yesterday we discuss, bcoz f16 i want take printout one time, bcoz of i have more than 4000 data. pls help me with EG: excel sheet. regards, Deen |
| All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com