extact certain information from sheet 1 to shhet 2 columns e:e and

[hotlh]

hi there,
i have information on sheet 1 that has been pulled from our
database and what l would like to do is start a new sheet like (sheet2) and
put ceratin information in there like the following.
i have our product codes that have been taken from three months ago up until
today.
example
Sheet 1 column E column w
00048003 quantity is 48
00048003 qty is 5
00048003 qty is 15
01671006 qty is 32
and so on along with different product codes and varies qty next to
them.(each product code has a different quantity of rows. For instance
product code 00048003 there might be 100 rows with different quantity amounts
but then product code 01671006 might only have 5 rows with different
quantity's.)

In sheet 2 column A l would like to put one of each product code from sheet
1 column E into sheet 2, so it would look like this
00048003
01671006
01672006
and so on
Then in sheet 2 column B l would like to have the total quantity of each
product code from sheet 1 column W to match sheet 2 column A (product code)
example
00048003 total qty is 500 cartons
01671006 " " is 250 cartons
01672006 " " is 800 cartons

sorry to confuse anybody. just trying to work out what is my fastest moving
stock in the warehouse?

 

[carlo]

hi chris

if you get this data out of your database, why don't you use a query
which groups and sums it automatically, would be maybe the easiest
way.

(maybe that doesn't help at all, but if you want to do that more than
one time it's maybe easiest to do by query)
Carlo

 

[Ron Rosenfeld]

hi there,
i have information on sheet 1 that has been pulled from our
database and what l would like to do is start a new sheet like (sheet2) and
put ceratin information in there like the following.
i have our product codes that have been taken from three months ago up until
today.
example
Sheet 1 column E column w
00048003 quantity is 48
00048003 qty is 5
00048003 qty is 15
01671006 qty is 32
and so on along with different product codes and varies qty next to
them.(each product code has a different quantity of rows. For instance
product code 00048003 there might be 100 rows with different quantity amounts
but then product code 01671006 might only have 5 rows with different
quantity's.)

In sheet 2 column A l would like to put one of each product code from sheet
1 column E into sheet 2, so it would look like this
00048003
01671006
01672006
and so on
Then in sheet 2 column B l would like to have the total quantity of each
product code from sheet 1 column W to match sheet 2 column A (product code)
example
00048003 total qty is 500 cartons
01671006 " " is 250 cartons
01672006 " " is 800 cartons

sorry to confuse anybody. just trying to work out what is my fastest moving
stock in the warehouse?

There are a few solutions to this problem. They require that either column w
be a "number", or that you extract the number to some adjacent column. You
don't supply sufficient information to determine which approach will be
required.

1.
a. Use Data/Filter and generate a list of unique product codes which
you place into sheet 2
b. Use formula such as =sumif(sheet1!E:E, B2, sheet1!W:W)

2. Copy the entire table to sheet2. Then sort by product code and use the
SUBTOTAL wizard.

3. Set up a Pivot table, with product code in the ROWS area and SUM of qty
in the data area.
--ron