Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique match

[Pete_UK]

I've just been checking back on earlier posts. My first macro was
comparing exact values, so you could re-instate that line if you wish.
A few days later I gave you a modification to look only at the tenths.
If you now want to look at the hundredths, then you should modify that
line in the macro and change 10 to 100 (four times in the line).

I also noticed that your posting on October 9th said that you may have
a one-to-three way cancelling, which contradicts what you said earlier
- just trying to get a more complete understanding. <bg>

Pete

 

[pogster]

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

 

[Pete_UK]

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

 

[pogster]

Pete,

Having the macro in its own file and having it run directly within my
source file is a great idea!

As for scanning multiple times with different criteria of decimal
place; the issue here isnt even matching just numbers anymore, that is
the easy part as you have already coded a suitable solution. The
problem now is cancelling the correct matching numbers, with the added
difficulty of "number status" as i explained earlier with the Reversal
column (B,R,N). I don't think that feature will be necessary,
scanning to the hundreths should be sufficient for my application.
There will always be one or two mistakes, i will do a visual search
myself to insure accuracy. No application is perfect.

But it is a good suggestion, maybe just one that i wont need to
implement at this moment. But if you wish to have two seperate
versions of the macro, please do, by all means. But the one without
this added code may be more useful to me.

That is a very interesting suggestion with the date sorting and making
negative numbers negative dates. Very smart solution. If the macro
could auto sort in the desired pattern, run itself, and then get rid
of the negative dates (normalize them again), that would be immensly
useful. The sort can remain, i can always autofilter and sort later
on without a problem.

As for the file, i have sent the full file to you, but it comes with a
sidenote: the queries run to generate these files change all the
time, different people modify them for different reasons, and i do as
well. Therefore, the order and placement of the columns is not
completely static. Therefore, if the macro is column-centric, it
would be nice to have some comments along with the code so i can
revise it myself if need be. If its not too much trouble.

One other way around this would be to look for the column names (which
are in fact static), and then adjust the column values automatically
at which the macro looks. Would this be a possible solution? Dont
worry about it for now though. Feel free to assume the columns dont
move, but just letting you know this is not always the case.

I hope you are being challenged with this macro dilemma, so far you
have been passing with flying colors, and helping me alot in the
process.

Thanks again Pete,

-Pogster

 

[Pete_UK]

The main challenge is actually finding time to sit down undisturbed
and get the coding done - perhaps over the weekend, as it may be too
cold to go digging in the garden !! <bg>

From what you have said about the Reversal column, this has to take a
higher priority than the date, although date may come into it if we
have matching reversal codes. In my earlier posts I was hoping that
the date would be the key to identifying the matches, but it is clear
now that that is not the only criterion.

It's a pity about the moveable columns in your source sheet, but I
could include in the Compare_master file a facility whereby you could
specify which column is which before running the macro. Picking up on
the column names is another way, but I don't like relying on "... this
will never change ...", as invariably it will sometime in the future.

I had been planning to use the sheet in the compare_master file,
anyway, to allow you to specify the default path to the folder where
your file is, so it's just an extension of that.

Pete

 

[pogster]

Pete,

You are probably right, its better to plan around every possibility
instead of settling on assumptions. Specifying which is which column
would probably be best.

It is going to be freezing this weekend, but please, by all means,
take your time. There is no rush on this, the last thing i want is
for you to lose sleep or massive ammounts of time over this. Like i
said before, the original macro is almost perfect, and finding the
errors does not take that much time, just a matter of simple
autofiltering.

Please let me know how it goes though, either way, or if you need any
more information from me. Ill be checking back here as often as
possible.

Again, thank you for all of your effort Pete.
Have a nice weekend!

-pogster