I need help creating a formula. I have an Excel 2003 Worksheet that contains
information. I would like the formula to look at the column ProductID where
this column contains numerical information and the same number could be
listed in this column more than once. So I would like the formula to look at
the ProductID, Count & QOH columns. If the ProductID value is listed more
than once it would go to the count column add up the amount and verify that
it matches what is listed in the QOH if not then move that ProductID, Count
& QOH and Area which is on this worksheet to a new sheet. Example is as
follows:
ProductID Count QOH
1234 4 5
1234 1 5

So the formula would look at the 1234 which is listed twice and now total
the Count and Verify with QOH that it matches which it does so it would do
nothing. Now the next example is what I would like it to do if it does not
match:
ProductID Count QOH
5678 2 10
5678 4 10
5678 1 10
So now the formula would look at the '5678' go to the Count column total
that up which the results would be 7, look at the QOH column which is 10.
Does not match so it would move the information from the worksheet to a new
sheet. If a formula would not work for this is there another way of
gathering this information? Thank You in advance for your help and time.


--
kwall

A NON-VBA way would be as follows:
On your main sheet, assuming your data starts in column A row 2, insert a
row before row A, and type the following into cell A2:
=IF(SUMPRODUCT(--($B$2:$B$100=B2),($C$2:$C$100))=D2,"",MAX($A$1:$A1 )+1)
Assuming your example below is contiguous, it would leave the cells next to
1234 both blank, and number the cells before 5678 1, 2, and 3.

Then on your new sheet, type the following into cell A2, copy over to cell
C2, and down as needed:
=IF(ROW()-1<=MAX(Sheet1!$A:$A),VLOOKUP(ROW()-1,Sheet1!$A:$D,COLUMN()+1,FALSE),"")




--
John C


"kwall" wrote:

I need help creating a formula. I have an Excel 2003 Worksheet that contains
information. I would like the formula to look at the column ProductID where
this column contains numerical information and the same number could be
listed in this column more than once. So I would like the formula to look at
the ProductID, Count & QOH columns. If the ProductID value is listed more
than once it would go to the count column add up the amount and verify that
it matches what is listed in the QOH if not then move that ProductID, Count
& QOH and Area which is on this worksheet to a new sheet. Example is as
follows:
ProductID Count QOH
1234 4 5
1234 1 5

So the formula would look at the 1234 which is listed twice and now total
the Count and Verify with QOH that it matches which it does so it would do
nothing. Now the next example is what I would like it to do if it does not
match:
ProductID Count QOH
5678 2 10
5678 4 10
5678 1 10
So now the formula would look at the '5678' go to the Count column total
that up which the results would be 7, look at the QOH column which is 10.
Does not match so it would move the information from the worksheet to a new
sheet. If a formula would not work for this is there another way of
gathering this information? Thank You in advance for your help and time.


--
kwall

A formula cannot move a block of cells to another sheet, so either you
do this manually (with the aid of formulae) or you use a macro.

This formula:

=SUMIF(A:A,A2,B:B)

will give you a sum of the count for each product code, assuming the
ProductID is in column A, the Count is in column B, and your data
starts in row 2. This can then be compared with the QOH column
(assumed to be column C) and flag each row if it needs to be moved,
eg:

=IF(SUMIF(A:A,A2,B:B)<C2,"Move","ok")

If this is copied down column D, say, from D2, then you could apply
autofilter to the column and select Move from the filter drop-down, By
highlighting just the visible cells, you can copy these into another
sheet. If you click back to the other sheet, you can use Edit | Delete
Row to remove them.

Hope this helps.

Pete

On Aug 4, 4:21*pm, kwall wrote:
I need help creating a formula. *I have an Excel 2003 Worksheet that contains
information. *I would like the formula to look at the column ProductID where
this column contains numerical information and the same number could be
listed in this column more than once. *So I would like the formula to look at
the ProductID, Count & QOH columns. *If the ProductID value is listed more
than once it would go to the count column add up the amount and verify that
it matches what is listed in the QOH if not then *move that ProductID, Count
& QOH and Area which is on this worksheet to a new sheet. *Example is as
follows:
ProductID * Count * QOH
1234 * * * * * 4 * * 5
1234 * * * * * 1 * * 5

So the formula would look at the 1234 which is listed twice and now total
the Count and Verify with QOH that it matches which it does so it would do
nothing. *Now the next example is what I would like it to do if it does not
match:
ProductID * *Count * QOH
5678 * * * * *2 * * * 10
5678 * * * * *4 * * * 10
5678 * * * * *1 * * * 10
So now the formula would look at the '5678' go to the Count column total
that up which the results would be 7, look at the QOH column which is 10. *
Does not match so it would move the information from the worksheet to a new
sheet. *If a formula would not work for this is there another way of
gathering this information? *Thank You in advance for your help and time. * *

--
kwall