| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Values
My next problem is I need to extract only certain information from a
worksheet as sample- Worksheet A Worksheet B Column A Column A Column B Column C SP001 SP001 PL001 16 SP001 PL002 1 SP001 DC001 5 Worksheet B is my Bill of Material but I only want to extract only certain info such as PL001 / PL002 with the qty's. Any assistance is appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Values
JJ,
The display of your example data got really messed up, to make sure we give you a good solution could you repost and give the example something like this: Worksheet A [A] [b] [C] 1 PL001 SP001 DL001 Worksheet B [A] [b] 1 PL001 5 2 DL001 8 But based on those layouts, we could use SUMPRODUCT() on sheet A to give combined quantity for each item. In A2 use a formula like this: =SUMPRODUCT(--('Worksheet B'!$A$1:$A$1000=A$1), ('Worsheet B'!$A$1:$A$1000)) Fill the formula across the sheet. Actually, in this case, you could even use the simpler SUMIF() formula because there is only one thing to match (the item ID): =SUMIF('Worksheet B'!$A$2:$A$1000,$A$1,'Worksheet B'!$B$2:$B$1000) If Worksheet A is laid out like: [A] [b] 1 PL001 formula 2 SP001 3 DL001 Then in B1 the SUMIF() formula would be =SUMIF('Worksheet B'!$A$1:$A$1000,$A1,'Worksheet B'!$B$1:$B$1000) "JJ" wrote: My next problem is I need to extract only certain information from a worksheet as sample- Worksheet A Worksheet B Column A Column A Column B Column C SP001 SP001 PL001 16 SP001 PL002 1 SP001 DC001 5 Worksheet B is my Bill of Material but I only want to extract only certain info such as PL001 / PL002 with the qty's. Any assistance is appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Values
HI
Tx for the response. This is how is should be:- Worksheet A [A] SP001 Worksheet B [A] [b] [C] 1 SP001 PL001 16 2 SP001 PL002 1 3 SP001 DL001 5 Worksheet B is my Bill of Material but I need to extract only certain info such as PL001/PL002 with the qty. I will try your formula in the meantime and verify if it is working. JJ "JLatham" wrote: JJ, The display of your example data got really messed up, to make sure we give you a good solution could you repost and give the example something like this: Worksheet A [A] [b] [C] 1 PL001 SP001 DL001 Worksheet B [A] [b] 1 PL001 5 2 DL001 8 But based on those layouts, we could use SUMPRODUCT() on sheet A to give combined quantity for each item. In A2 use a formula like this: =SUMPRODUCT(--('Worksheet B'!$A$1:$A$1000=A$1), ('Worsheet B'!$A$1:$A$1000)) Fill the formula across the sheet. Actually, in this case, you could even use the simpler SUMIF() formula because there is only one thing to match (the item ID): =SUMIF('Worksheet B'!$A$2:$A$1000,$A$1,'Worksheet B'!$B$2:$B$1000) If Worksheet A is laid out like: [A] [b] 1 PL001 formula 2 SP001 3 DL001 Then in B1 the SUMIF() formula would be =SUMIF('Worksheet B'!$A$1:$A$1000,$A1,'Worksheet B'!$B$1:$B$1000) "JJ" wrote: My next problem is I need to extract only certain information from a worksheet as sample- Worksheet A Worksheet B Column A Column A Column B Column C SP001 SP001 PL001 16 SP001 PL002 1 SP001 DC001 5 Worksheet B is my Bill of Material but I only want to extract only certain info such as PL001 / PL002 with the qty's. Any assistance is appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Values
Try this in colum B of Worksheet A
=SUMIF('Worksheet B'!$B$1:$B$1000,$A1,'Worksheet B'!$C$1:$V$1000) "JJ" wrote: HI Tx for the response. This is how is should be:- Worksheet A [A] SP001 Worksheet B [A] [b] [C] 1 SP001 PL001 16 2 SP001 PL002 1 3 SP001 DL001 5 Worksheet B is my Bill of Material but I need to extract only certain info such as PL001/PL002 with the qty. I will try your formula in the meantime and verify if it is working. JJ "JLatham" wrote: JJ, The display of your example data got really messed up, to make sure we give you a good solution could you repost and give the example something like this: Worksheet A [A] [b] [C] 1 PL001 SP001 DL001 Worksheet B [A] [b] 1 PL001 5 2 DL001 8 But based on those layouts, we could use SUMPRODUCT() on sheet A to give combined quantity for each item. In A2 use a formula like this: =SUMPRODUCT(--('Worksheet B'!$A$1:$A$1000=A$1), ('Worsheet B'!$A$1:$A$1000)) Fill the formula across the sheet. Actually, in this case, you could even use the simpler SUMIF() formula because there is only one thing to match (the item ID): =SUMIF('Worksheet B'!$A$2:$A$1000,$A$1,'Worksheet B'!$B$2:$B$1000) If Worksheet A is laid out like: [A] [b] 1 PL001 formula 2 SP001 3 DL001 Then in B1 the SUMIF() formula would be =SUMIF('Worksheet B'!$A$1:$A$1000,$A1,'Worksheet B'!$B$1:$B$1000) "JJ" wrote: My next problem is I need to extract only certain information from a worksheet as sample- Worksheet A Worksheet B Column A Column A Column B Column C SP001 SP001 PL001 16 SP001 PL002 1 SP001 DC001 5 Worksheet B is my Bill of Material but I only want to extract only certain info such as PL001 / PL002 with the qty's. Any assistance is appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Values
Hi JLatham I tried the formula but I am not getting the results I require. Firstly I am getting 0 value. Secondly what I require is that the the data in B PL001, PL002 with corresponding values in C. to be extracted not DL001. I require only certain data from the bill of material. Tx Again for your assistance. JJ "JLatham" wrote: Try this in colum B of Worksheet A =SUMIF('Worksheet B'!$B$1:$B$1000,$A1,'Worksheet B'!$C$1:$V$1000) "JJ" wrote: HI Tx for the response. This is how is should be:- Worksheet A [A] SP001 Worksheet B [A] [b] [C] 1 SP001 PL001 16 2 SP001 PL002 1 3 SP001 DL001 5 Worksheet B is my Bill of Material but I need to extract only certain info such as PL001/PL002 with the qty. I will try your formula in the meantime and verify if it is working. JJ "JLatham" wrote: JJ, The display of your example data got really messed up, to make sure we give you a good solution could you repost and give the example something like this: Worksheet A [A] [b] [C] 1 PL001 SP001 DL001 Worksheet B [A] [b] 1 PL001 5 2 DL001 8 But based on those layouts, we could use SUMPRODUCT() on sheet A to give combined quantity for each item. In A2 use a formula like this: =SUMPRODUCT(--('Worksheet B'!$A$1:$A$1000=A$1), ('Worsheet B'!$A$1:$A$1000)) Fill the formula across the sheet. Actually, in this case, you could even use the simpler SUMIF() formula because there is only one thing to match (the item ID): =SUMIF('Worksheet B'!$A$2:$A$1000,$A$1,'Worksheet B'!$B$2:$B$1000) If Worksheet A is laid out like: [A] [b] 1 PL001 formula 2 SP001 3 DL001 Then in B1 the SUMIF() formula would be =SUMIF('Worksheet B'!$A$1:$A$1000,$A1,'Worksheet B'!$B$1:$B$1000) "JJ" wrote: My next problem is I need to extract only certain information from a worksheet as sample- Worksheet A Worksheet B Column A Column A Column B Column C SP001 SP001 PL001 16 SP001 PL002 1 SP001 DC001 5 Worksheet B is my Bill of Material but I only want to extract only certain info such as PL001 / PL002 with the qty's. Any assistance is appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Values
Hi JLatham,
The formula I require has to in Worksheet A :- [B1] lookup DATA in Worksheet A [A1] SP001 in worksheet B [A] SP001 SP001 SP001 Search for all Text String starting with "PL" in Worksheet B [b] PL001 PL002 DL001 and return with values from Worksheet B [C] 1 16 5 as 1 and 16. I need the values to be seperated and not summed. Tx and waiting for your prompt response. JJ "JJ" wrote: Hi JLatham I tried the formula but I am not getting the results I require. Firstly I am getting 0 value. Secondly what I require is that the the data in B PL001, PL002 with corresponding values in C. to be extracted not DL001. I require only certain data from the bill of material. Tx Again for your assistance. JJ "JLatham" wrote: Try this in colum B of Worksheet A =SUMIF('Worksheet B'!$B$1:$B$1000,$A1,'Worksheet B'!$C$1:$V$1000) "JJ" wrote: HI Tx for the response. This is how is should be:- Worksheet A [A] SP001 Worksheet B [A] [b] [C] 1 SP001 PL001 16 2 SP001 PL002 1 3 SP001 DL001 5 Worksheet B is my Bill of Material but I need to extract only certain info such as PL001/PL002 with the qty. I will try your formula in the meantime and verify if it is working. JJ "JLatham" wrote: JJ, The display of your example data got really messed up, to make sure we give you a good solution could you repost and give the example something like this: Worksheet A [A] [b] [C] 1 PL001 SP001 DL001 Worksheet B [A] [b] 1 PL001 5 2 DL001 8 But based on those layouts, we could use SUMPRODUCT() on sheet A to give combined quantity for each item. In A2 use a formula like this: =SUMPRODUCT(--('Worksheet B'!$A$1:$A$1000=A$1), ('Worsheet B'!$A$1:$A$1000)) Fill the formula across the sheet. Actually, in this case, you could even use the simpler SUMIF() formula because there is only one thing to match (the item ID): =SUMIF('Worksheet B'!$A$2:$A$1000,$A$1,'Worksheet B'!$B$2:$B$1000) If Worksheet A is laid out like: [A] [b] 1 PL001 formula 2 SP001 3 DL001 Then in B1 the SUMIF() formula would be =SUMIF('Worksheet B'!$A$1:$A$1000,$A1,'Worksheet B'!$B$1:$B$1000) "JJ" wrote: My next problem is I need to extract only certain information from a worksheet as sample- Worksheet A Worksheet B Column A Column A Column B Column C SP001 SP001 PL001 16 SP001 PL002 1 SP001 DC001 5 Worksheet B is my Bill of Material but I only want to extract only certain info such as PL001 / PL002 with the qty's. Any assistance is appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
| Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
| Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
| create a list of single values from multiple values | Excel Worksheet Functions | |||
Linear Mode
