Thanks for resurrecting the thread and giving some feedback on using
the macro.

As I understand it, you extract just a single column of numbers from a
multi-column file, and the macro works on that column, ensuring it
remains in the same order at the end so that you can paste it back
into the original file.

But, if you also copied the dates associated with each number into
your extracted file, then it would be possible to sort the data not
only by number, but also by date, such that you have the largest
positive number going down to the largest negative number, but also
ensuring that equal (positive) values are listed in increasing date
order and equal negative values are listed in decreasing date order.
That way the macro would find the earliest +100 and match it with the
earliest -100.

However, this might not be exactly what you want, though. Imagine you
have -100 on 1st Nov, +100 on 3rd Nov and -100 on 4th Nov. YOU might
have reasons for pairing the 3rd and 4th November numbers and leaving
the 1st Nov value unpaired, but if these were the only values of 100
then the (date-enhanced) macro would pair the 1st and 3rd Nov values
together.

This being the case, would you prefer to match equal numbers on the
minimum days spread? I'd have to think a bit about an appropriate
algorithm for that !! Or could it be that you always have a +ve number
first, and the -ve match to this always occurs after the +ve? (or vice-
versa). Is it possible to incorporate unpaired values from an earlier
month (or whatever period your file covers), and then begin matching
those?

Of course, another approach would be to have the macro applied
directly to your original file, as I mentioned in an earlier post.
There might be other items of data in there that would help to
identify the appropriate pairings, and it would cut down on you having
to extract the data as you do now. You can easily use File | Save As
to save the file with a different name, so that the original is not
changed, and even this can be automated quite easily within the macro.

If you feel you can trust me, you might like to send a file directly
to me:

pashurst <at auditel.net

Change the obvious.

Though I prefer to keep discussions going in the newsgroups, so that
everyone can benefit, there are times when sensitive data needs to be
kept more secure.

Pete

On Nov 12, 6:44 pm, wrote:
Wow Pete,

Inredibly sorry for taking the better part of a month in getting back
to you here.

To answer your first question, the numbers do not have muliple
matches, i.e. -12 matches with +12 once, and they are counted, end of
story. That is how your macro works and that is the correct method.
But there can be multiple matches in the sense that there are more
than one -12 cancelling with more than one +12, and obv. if there is
an odd number of either, one will be uncancelled in the end, or a
numerous ammount of equal value and sign, which also remain
uncancelled.

Every number in the list is accounted for, but only in the sense of a
1-to-1 pairing, or lack thereof.

In continuing to use your very successful macro for most of this past
month, i have noticed that, the order my data is in (by date)
conflicts with the way the macro searches through the data.

For example, one entry of -100 should cancel with a +100 about three
entires down. But there is a +100 at the very bottom of the list. So
the first, and last -/+ 100 cancel out, are highlighted and forgotten
by the macro...whereas that second +100 a few down from the first
value, was the one which actually cancelled with that first value,
while the one at the bottom is simply a new outstanding balance.

Your macro processes one by one top value, compared to bottom, and
advances each variable position closer until they essentially meet in
the middle, am i correct?

Given this pattern, i sometimes have problems with the wrong pairs
being created. Usually not too many so going through by hand and
correcting doesnt take too long. The pairs are not wrong when
speaking strictly numerically, but in regards to real-world scenarios,
the wrong ammounts are being paired.

I have tried resorting my data in a way that would better suit the
macro's processes, but theres really no way i can think of to do it
properly.

To explain the reason for this (and i will attach an expanded dataset
as well) is that some of these journal entires are marked "N" for
manual, "B" for Will reverse at the beginning of next period, and "R"
for this is a reversal entry. Any reversal entries will obviously be
cancelling something out prior to the date at which that reversal is
posted and should be highlighted. The N is simply a manual entry
which could be unique (uncancelled) or a manual reversal as well.
Whereas the B marks an entry which will be automatically reversed at
the beginning of the next month/period. If the B is in this period, it
is highly probable that it remains uncancelled, unless some idiot went
in and reversed it manually...which would only create a problem later
since the system will still autoreverse.

I guess this will be easiest to understand with a dataset so i will
attach one for you to look at.
A Few tips:
The journal ID's usually coincide with B's and their corresponding
R's...unless it is a manual N entry, in which case the Journal ID's
could differ. I thought about this when i was trying to resort my data
to better cater to the macro's process.

I hope this answers your questions and doesnt cause too many
problems. If you do not feel the need to work on this project, that
is totally fine, as you have done so much for me already. I will
simply continue forward manually(highly likely) until i become a VBA
guru (highly unlikely).

If you are still interested in taking a further crack at this problem,
your help would be greatly appreciated!!
I will post a link to a secure website containing the dataset of which
I speak tonight (as i cant upload anything here at work). It will
expire in 7 days, let me know if the link does not work and i will
repost.

Thanks again pete.

-Pogster

P.S. - i swear not to let this forum get too dusty, i will check back
regularly from now on!