How do I do this complicated macro???

I have 2 workbooks. Both contain data on machines in a different format
One spreadsheet is created from data that is got off a data capture and
the other is data thats input manually.

What it is that there is a workbook shown below


NLC MACHINE START FINISH AMOUNT
1234 6584 26-Feb-06 28-Feb-06 £100.00
6929 5698 24-Jan-06 15-Feb 06 £569.35

This is the data thats input manually. Shown below is the data thats
created by the data capture computer and stored on a seperate
spreadsheet.

NLC MACHINE START TIME FINISH TIME AMOUNT
1234 6584 26FEB 06 00.01 26 FEB 06 23.59 55.00
1234 6584 27FEB 06 00.01 27 FEB 06 23.59 40.00
1234 6584 28 FEB 06 00.01 28 FEB 06 10.00 5.00
1234 6584 28 FEB06 10.10 28 FEB 06 23.59 15.00

What im trying to do is to have a function that matches up the data
thats been input in the input file with the data thats in the data
capture file. I cannot see a way it can be done as the data input is
between 2 dates and the data capture is on a daily basis. I need them
to match exactly between the 2 dates and when looked up the amounts
taken daily adds up to the amount input manually. I also need it to
somehow highlight or seperate dates and amounts that have not been
accounted for between the 2 books.


--
tweacle

On Thu, 2 Mar 2006 20:43:01 +0000, tweacle
<[email protected]> wrote:

>
>I have 2 workbooks. Both contain data on machines in a different format
>One spreadsheet is created from data that is got off a data capture and
>the other is data thats input manually.
>
>What it is that there is a workbook shown below
>
>
>NLC MACHINE START FINISH AMOUNT
>1234 6584 26-Feb-06 28-Feb-06 £100.00
>6929 5698 24-Jan-06 15-Feb 06 £569.35
>
>This is the data thats input manually. Shown below is the data thats
>created by the data capture computer and stored on a seperate
>spreadsheet.
>
>NLC MACHINE START TIME FINISH TIME AMOUNT
>1234 6584 26FEB 06 00.01 26 FEB 06 23.59 55.00
>1234 6584 27FEB 06 00.01 27 FEB 06 23.59 40.00
>1234 6584 28 FEB 06 00.01 28 FEB 06 10.00 5.00
>1234 6584 28 FEB06 10.10 28 FEB 06 23.59 15.00
>
>What im trying to do is to have a function that matches up the data
>thats been input in the input file with the data thats in the data
>capture file. I cannot see a way it can be done as the data input is
>between 2 dates and the data capture is on a daily basis. I need them
>to match exactly between the 2 dates and when looked up the amounts
>taken daily adds up to the amount input manually. I also need it to
>somehow highlight or seperate dates and amounts that have not been
>accounted for between the 2 books.

Are you trying to identify where the manual input does not agree with
the data capture input. For example, with the numbers above, are you
trying to show that the first manual record for Machine No. 6584 is
£100 whereas the data captured shows a total of £115?

If so then you don't need a macro to do this, a simple SumProduct
formula will do.

Assuming your manual data is in A2:E3 and data capture data in A10:G20
enter the following in I2 and copy down against your manual data.

=SUMPRODUCT((A10:A20=A2)*(B10:B20=B2)*(C10:C20>=C2)*(E10:E20<=D2)*(G10:G20))

This will give you the sum of the data capture data. In J2 you could
strike the difference with

=I2-E2

or you could just add -E2 to the end of the first formula to identify
differences.

As for the second part of the question, I'm not quite clear what you
mean by highlight dates that have not been accounted for. Could you
give an example?

HTH

Richard Buttrey
__