Tough One!!

[Big Ben]

Hi Everyone,

I have a list of transactions from an inventory report that I import
into excel and then from there I manipulate the data. I have over 2000
transactions per month and am deperately trying to automate.

The spreadsheet appears as follows:

W/O # Type Date Amount
5863 RCVD 02-Jan-04 2.5
5863 VCHR 19-Jan-04 5
5863 RCVD 17-Jan-04 2. 5
5867 RCVD 28-Jan-04 15
5867 VCHR 29-Jan-04 15

The spreadsheet is sorted by the Work Order #, however, the problem
comes in because I need to ensure that the total of the RCVD within
each W/O# matches the total for the VCHR total. This needs to be done
for each W/O set. So for example, for W/O# 5863, the 2 RCVD lines must
equal the VCHR total (2.5 + 2.5 must = 5). There are times when there
might be 3 RCVD's to 1 VCHR, or vice versa, and there could also be
times like in the above example with W/O # 5867 when there is 1 VCHR to
1 RCVD. The purpose of this report is to find the difference between
the total RCVD and the total VCHR'd for each W/O. So basically, I need
it to look for all lines with the same W/O# and then ensure that the
voucher total matches the RCVD total. I could use filters but this
would still be a long and painful process. I was thinking a formula
might be appropriate whereby I could copy the formula right down the
page. The W/O# are unique each month so a list or V-Lookup would not
be useful. If anyone could please give me any pointers if this can be
done it would be greatly appreciated! Thanks.

 

[Alan Beban]

So basically, I need
it to look for all lines with the same W/O# and then ensure that the
voucher total matches the RCVD total.

What do you want to return if it doesn't? If it's greater? If it's less?

Alan Beban

 

[mudraker]

This is one way

i am asuming
WO# is in column A
Date is in Column C
Amount in column D

Data needs to be sorted by W/O (ascending) # , Date (ascending)
Amount (Descending)

This should put your Total as the last entry for each WO#


enter this formula starting in e
2 then copy down

=IF(A6=A7,IF(A6=A5,E5+D6,D6),IF(D6<>E5,"error","Ok"))

This will display a 1 of 3

a running total
an error message
an OK messag

 

[Big Ben]

Hi Alan and mudraker,

Thanks for your help so far. I had a look at the following statemen
from you mudraker and although it is a very good idea, I don't thin
that it will work in this situation. The reason being that each wor
order can have various amounts of receipts (RCVD). So for one wor
order, we may have received 5 shipments of goods (which would me 5 RCV
lines), whereas the next work order may only have 2 receipts.
Therefore, this formula would work, but only if the # of receipts pe
work order were consistent. I could go through and check each formul
but I think it may take just as long to do it manually.

=IF(A6=A7,IF(A6=A5,E5+D6,D6),IF(D6<>E5,"error","Ok"))


As for your question Alan, the output I would like to obtain if fals
is the dollar difference in the amount received vs. the amount VCHR'
for each individual work order, as well as other info.

This is actually a really complicated formula because I have only give
you part of the entire project. Let me outline my goals and include a
attachment of something I have worked on since my post that may work.
Maybe you can think of something less complicated.

The purpose of this project is that I have to reconcile my inventor
balance between two different software programs and account for reason
why inventory would differ between the two programs. When looking a
each W/O grouping the possibile reason the inventory could be off i
the amount may have been RCVD and entered into one system, but not ye
VCHR and entered in the other system, thus throwing the two balance
off. The problem arises when you have to say to yourself, "look a
only work orders with this #, and then look to make sure the total RCV
equals the total VCHR column within that one W/O.

Thanks Again!!!!

Be

Attachment filename: inv.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=45100

 

[Alan Beban]

=A1&"
"&SUMPRODUCT((A$1$5=A1)*(B$1:B$5="RCVD")*D$1$5)-SUMPRODUCT((A$1$5=A1)*(B$1:B$5="VCHR")*D$1$5)

The above was written against the illustration you posted. Entered in
E1 and copied down, it returned

5863 0
5863 0
5863 0
5867 0
5867 0

With more RCVD than VCHR it will return a positive number instead of 0;
with mor VCHR than RCVD it will return a negative number.

Alan Beban