Hi Pogster,

You said in an earlier thread that your data was from column O of your
source file, but the file I now have has this in column G, so it is
not an exact copy of one of your source files. To apply the macro to
the source file I would need to know what columns are used in your
source file and where the data actually starts, as you seem to have a
header row now. Could you send me one of your source files with all
the data removed, so I can see what it looks like? You can add
comments to the file instead of data to point out anything you think I
should know.

What I have in mind is for the macro to be located in its own file -
let's say Compare_master.xls. When the macro is run it will ask you to
identify the folder and file with a normal File|Open dialogue, and it
will then open the source file and act upon it. At the end of the
macro the amended file will be saved automatically with a different
name (eg with "_a" added on to the filename), so that the original
source file and the Compare_master file remain unchanged, and you can
do what you like with the amended file.

In the first file I downloaded, your values only had 2 decimal places,
whereas the latest has 3. The macro could easily be made to scan
through the data looking for exact matches first, then looking for
matches on 1/100ths, and finally looking for matches on the tenths
(maybe an appropriate use of the colour coding?!). This shouldn't slow
things down too much - maximum it would be is three times the time
taken now (i.e. 6 seconds).

To get the date sorted as I suggested the other day, it's just a
matter of making the sign of the number representing the date the same
as the dollar amount, so a negative number will have a negative date
(this won't display correctly, but we're not bothered about that).
However, I have to bear in mind what you have said about the other
codes, so I'm still getting my head around that. These codes might be
obvious to you because you use them everyday, but it will take me a
while to get used to them. Any other codes I should know about?

Pete

On Nov 14, 2:10 pm, wrote:
Hey Pete,

Well now that you have the source file, you can understand the kind of
situation i am dealing with. The order matters because of the other
data attached to each value.

As for the one-to-three way cancelling, im not sure exactly what i may
have said before, but the definitive version is that one value will
cancel with its opposite once. There may be multiples of that value
in a + or - fashion, but if there are an even number of each sign,
all of the values will probably cancel.

As for the macro running directly in the source file, it would be
useful. I could just sort the numbers how i saw fit, and then run the
macro right there. Unfortunatley, dates and value sizes arent the
only things i need to worry about when developing a solution to
cancelling out values without making any mistakes. Its hard to cover
all of the bases because there are so many variables...date of
posting, current period, journal status (N, B, or R), etc.

If you wanted to alter the macro to make it run in the source file
(which resembles what i sent you), i would welcome that change. It
would be nice if you could just add comments to the code so i knew how
to change which columns it looked at, etc...

I will revert the tenths to the hundreths place in the macro's search,
that was my mistake for changing it, it is better off being more
precise.

As for my earlier problem of the wrong pairs being created (unique N's
being highlighted instead of the B and R entires that should cancel),
it is a pretty easy fix manually, all i do is sort out the uncancelled
values, and sort by Journal type to weed out all the R's which i know
should cancel. Then i find their corresponding N value which was
highlighted mistakenly, and just reverse the highlighting so the
correct one is left unhighlighted (N).

I believe a combination of resorting the data, possibly in the way in
which you suggested, along with comparing numbers to the hundreths
place, and possible having the macro pay attention to journal entry
type (N, B, R) will solve this problem.

Let me know once you have looked at the dataset if you had any ideas
as to how to cater to this particular issue.

Thanks again for all of your help Pete.

-Pogster