Reconicle with Arrays

[Guest]

Hello, thank you in advance for any assistance. I'm trying to put together a
reconciler that compares what I know to what is coming into me from an
outside source. I have some experience w/ VBA but none with arrays. I think
that an array is most likely the best way to do something like this.

On the sheet "Input" (where I put in what I know)

Product Qty Price Contract Qualifier #1 Qualifier #2
ID

I have:
*12 choices for products
*Any number for the Qty (possible if we bought, negative if we sold)
*Price is from .1 to 30001
*39 choices of contracts that vary each month (I have a reference in a
column on the "Main" sheet of the available contracts)
*Qualifier #1 is like a bar code #1000 to 30001
*Qualifier #2 has 3 choices A,B,&C
*ID is 1 of the 10 people responisble for the product transaction (I have a
reference in a column on the "Main" sheet of the available people)

The sheet "Account" shows the statement we receive that shows the
counterparty's recognition of the transaction. The two sides often don't
match and I'm trying to find a good way to hilite where it does not match.
The "Account" sheet is formated in the exact same way and order of columns as
the "Input" sheet.

So, I would like to compare our side to their side and output only where we
do not match.

I would like to criteria and output to look like this:

A is our side
B is their side

QtyA-QtyB
where ProductA = ProductB & PriceA = PriceB & ContractA = ContractB &
Qualifier#1A = Qualifier#1B & Qualifier#2A = Qualifier#2B

If QtyA-QtyB = 0 then
Show nothing

If QtyA-QtyB <> 0 then
Show Qty Difference and list all transactions in that product, contract,
price, qualifier#1, & qualifier#2.


Please let me know if you need more information. Again, thank you for any
help in advance.

 

[Guest]

To clarify because the formatting messed it up a little, "ID" is the last
column, not part of the "Product" column. Sorry for any confusion.

 

[Zone]

Big, I don't see why you even need an array. Say the two sheets are in
the same layout, with the data in columns A through G (that is, columns
1-7), beginning in row 2. Copy this sub into a standard module, open
both workbooks and run the sub.
Let me know how you come out.
James

Sub CompareTwo()
Dim Sht1 as worksheet, Sht2 as worksheet, LastRowSht1 as Long
Dim a as long, b as integer
Set Sht1=Workbooks("YourWorkbookNameHere.xls").Worksheets(1)
Set Sht2=Workbooks("TheirWorkbookNameHere.xls").Worksheets(1)

LastRowSht1=Sht1.Cells(65535,"a").end(xlup).row
Sht2.activate
cells.interior.colorindex=xlnone
For a=2 to LastRowSht1
For b=1 to 7
If sht1.cells(a,b)<>sht2.cells(a,b) then
cells(a,b).interior.colorindex=15
Next b
Next a
End Sub

 

[Guest]

Zone, thank you for helping me clarify my scenario.

The reason that your solution would not work is because, for example, if I
were to buy 10 of product of A, I would record that as the purchase of 10
A's. However, when I see my statement, the 10 could be a collection of
random 1's that total up to 10. So, I would like the array to sum all
products at that specific price, and qualifications and then show any
product/price where the totals do not match.

Thank you very much for your help.

 

[Zone]

Big,
Yes, I see how that could be a problem. I find your problem
interesting, so I'll go out on a limb here. If you want to discuss
further, e-mail me at JKend6931nospam. I won't include the rest of it
to avoid an avalanche of spam, but replace the nospam part of the
address with a 5. I'm at AOL.com, so you can figure it out. Write me
if you want and we'll discuss an array solution. James