Multiple Values

[JJ]

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.

 

[JLatham]

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] [C]
1 PL001 SP001 DL001

Worksheet B
[A]
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]
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]

HI

Tx for the response.

This is how is should be:-
Worksheet A
[A]
SP001

Worksheet B
[A] [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

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] [C]
1 PL001 SP001 DL001

Worksheet B
[A]
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]
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)

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.

 

[JLatham]

Try this in colum B of Worksheet A
=SUMIF('Worksheet B'!$B$1:$B$1000,$A1,'Worksheet B'!$C$1:$V$1000)

HI

Tx for the response.

This is how is should be:-
Worksheet A
[A]
SP001

Worksheet B
[A] [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

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] [C]
1 PL001 SP001 DL001

Worksheet B
[A]
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]
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)

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.

 

[JJ]

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

Try this in colum B of Worksheet A
=SUMIF('Worksheet B'!$B$1:$B$1000,$A1,'Worksheet B'!$C$1:$V$1000)

HI

Tx for the response.

This is how is should be:-
Worksheet A
[A]
SP001

Worksheet B
[A] [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

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] [C]
1 PL001 SP001 DL001

Worksheet B
[A]
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]
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)


:

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.

 

[JJ]

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

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

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

Try this in colum B of Worksheet A
=SUMIF('Worksheet B'!$B$1:$B$1000,$A1,'Worksheet B'!$C$1:$V$1000)

HI

Tx for the response.

This is how is should be:-
Worksheet A
[A]
SP001

Worksheet B
[A] [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

:

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] [C]
1 PL001 SP001 DL001

Worksheet B
[A]
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]
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)


:

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.