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

Balan,

Your macro does in fact work, but you are correct, it is very time
consuming (and resource consuming) due to its recursive nature.

Thank you again for all of your time and effort, you have done very
well! Especially as some just getting into VBA programming!

Well Done, Bravo!

You do not need to worry about this particular problem anymore, do not
let it distract you.
I will work with what Pete and Max have provided and figure it out
from there.

Thank you again Balan, you have been invaluable!

-Pogster

Pete,

The changes you suggested worked like a charm!

Your macro is fast, efficient, and does exactly what it is supposed
to.

It is amazing, the number of shortfalls Excel 2003 has compared to the
new 2007 version (which allows me to conditionally format by searching
for duplicates)
But your macro has brought 2003 up to speed, at least in this one
case.

Thanks so much for your help, this macro should definitley be posted
somewhere where it can be easily found, it is very useful, and
modifiable to suit other specific needs.

Thank you again for the time you spent in helping me with this issue,
and coding a great solution.
This forum is made great by people like yourself and the others who
posted with replies to my issue

You are all great! Many Thanks!

-pogster

Well, thanks for such effusive praise - I'm glad the macro does what
you want it to do.

As regards storing it somewhere, well Google maintains archives of
these newsgroups, so it should be available for anyone who searches
for it.

If after thorough testing you are convinced that it does the job, then
you might like to apply it directly to your other files, as you said
in one post that you extract this data from a file and that the order
was very important so that you can paste it back to the same file - so
this could save you a bit of time. Add two new lines near the
beginning of the macro to insert two new columns, so that it would
read:

Dim my_top As Long
Dim my_bottom As Long
Application.ScreenUpdating = False
Columns("B:C").Select 'new
Selection.Insert Shift:=xlToRight 'new
Range("B1").Select

and so on ...

Then near the end of the macro change this line:

Columns("B:B").Select

to this:

Columns("B:C").Select

You could now safely apply the macro to your other files, as the
contents of the other columns get shifted out of the way - well, only
if the numbers are in column A of the other files.

Perhaps you could explain what it is you are working with sometime ...

Pete

On Oct 8, 9:15 pm, wrote:
Pete,

The changes you suggested worked like a charm!

Your macro is fast, efficient, and does exactly what it is supposed
to.

It is amazing, the number of shortfalls Excel 2003 has compared to the
new 2007 version (which allows me to conditionally format by searching
for duplicates)
But your macro has brought 2003 up to speed, at least in this one
case.

Thanks so much for your help, this macro should definitley be posted
somewhere where it can be easily found, it is very useful, and
modifiable to suit other specific needs.

Thank you again for the time you spent in helping me with this issue,
and coding a great solution.
This forum is made great by people like yourself and the others who
posted with replies to my issue

You are all great! Many Thanks!

-pogster

Actually, the numbers are always in column O.

Thanks for the suggestion though.

What i am working with are balance sheets for clients of an insurance
company. I am weeding out all of the unnessecary entries (which
cancel) to track down the entries which actually contribute to the
final account balance. There are hundreds of balance sheets, spanning
an average of 2000 rows apiece. Its a big job to do manually, but this
macro really helps.

The only other variable to consider (one which is probably impossible
to code for), is that some of the numbers which DO NOT contribute to
the balance (and cancel out) are not just single numbres, but made up
of 2 or more other individual entries. A simple example: -100 cancel
with SUM(50+10+40) Except in reality the numbers are not that simple
or easy to locate.

To accomplish this, you would need a smart macro that could detect
numbers that could add together to create other numbers in this 2000
row sheet. That would take alot of code and thought, i think. No need
to bother with it. Its easier to try to do that part by hand. But
cancelling identical cancelling numbers is 90% of the task.

Like i said, hours of tedius head-ache inducing searching, simplified
to two clicks by your great macro. Cant thank you enough.

Hope this answers your question, which is a nice change from you
answering mine =)

Cheers.

-Pogster

Yeah, thanks for taking the trouble to post back - it's always
interesting to find out what the real situation is, as often we just
get a snippet.

The other problem you refer to crops up quite often in the groups,
where people want to reconcile payments against invoices. I think
Harlan Grove had a macro to do it, but it is a very number-intensive
exercise (not one that I'm going to attempt).

I dabbled a bit with the macro last night in making the colour cycle
through a range of values, rather than just one colour (green), i.e.
the colour changes each time a pairing is found - would you be
interested in a multi-colour version?

You can't actually do very much with coloured cells - would you like
the macro to put something in column B (eg "Y") to indicate that the
cell has been paired, so that it can help you to eliminate them and
thus concentrate on your other problem?

Pete

On Oct 9, 9:33 pm, wrote:
Actually, the numbers are always in column O.

Thanks for the suggestion though.

What i am working with are balance sheets for clients of an insurance
company. I am weeding out all of the unnessecary entries (which
cancel) to track down the entries which actually contribute to the
final account balance. There are hundreds of balance sheets, spanning
an average of 2000 rows apiece. Its a big job to do manually, but this
macro really helps.

The only other variable to consider (one which is probably impossible
to code for), is that some of the numbers which DO NOT contribute to
the balance (and cancel out) are not just single numbres, but made up
of 2 or more other individual entries. A simple example: -100 cancel
with SUM(50+10+40) Except in reality the numbers are not that simple
or easy to locate.

To accomplish this, you would need a smart macro that could detect
numbers that could add together to create other numbers in this 2000
row sheet. That would take alot of code and thought, i think. No need
to bother with it. Its easier to try to do that part by hand. But
cancelling identical cancelling numbers is 90% of the task.

Like i said, hours of tedius head-ache inducing searching, simplified
to two clicks by your great macro. Cant thank you enough.

Hope this answers your question, which is a nice change from you
answering mine =)

Cheers.

-Pogster

Hey, sorry for the late post. Been busy with work lately.

As for the color changing, its really not necssary but thanks for the
suggestion.

As for the marker in column B, the trouble with moving the numbers out
of the order i have them in, or extracting unique values is that each
number also has about 11 columns of other information such as acct
numbers and journal ID's and dates and descriptions etc. that go along
with that particular value. So moving it out of the order i get it in
has the possibility of mucking everything up for me.

Its easier just to point out, visually, the values to ignore (or focus
on), and in the process retain the order.

If the marker just marked off the Unique values in column B, i could
of course insert a column next to col O and use it that way somehow,
to sort it, which would actually be quite helpful i think. Maybe if
the macro could look directly at column O, create a column to use for
its data, which it will delete, but also insert a column to the right
of Col O and place the markers there.

Would this be difficult to accomplish?

If its easier, yes, just placing a marker in column B would work just
as well, i can shift columns manually without a problem.

Thanks for the good idea though, as this would allow me to sort by
Unique or cancelled value (since you cannot sort by formatting in
2003)

-Pogster

I was thinking that you would do this in your extracted file - in
addition to ending up with numbers in column A which are highlighted
if paired, you would also have a column B which would have some marker
in to indicate these pairings (like "x"). You can still copy column A
back to column O of your original sheet, and copy column B to the next
empty column in your original sheet (which might be column P, but I
didn't know the layout of your data in the original file).

I thought you could then apply autofilter to this new column, along
the lines of:

Custom, Not Equal To, x ...

so that only the rows which have not been paired would then be
visible, so that you can then concentrate on doing what you need to do
with those. Of course, you could sort the data by this new column, to
bunch up the unpaired amounts and then concentrate on them - it's up
to you how you use it.

The thing is - would you find it useful?

OK, I'll ditch the multi-coloured idea.

Pete



On Oct 15, 8:43 pm, wrote:
Hey, sorry for the late post. Been busy with work lately.

As for the color changing, its really not necssary but thanks for the
suggestion.

As for the marker in column B, the trouble with moving the numbers out
of the order i have them in, or extracting unique values is that each
number also has about 11 columns of other information such as acct
numbers and journal ID's and dates and descriptions etc. that go along
with that particular value. So moving it out of the order i get it in
has the possibility of mucking everything up for me.

Its easier just to point out, visually, the values to ignore (or focus
on), and in the process retain the order.

If the marker just marked off the Unique values in column B, i could
of course insert a column next to col O and use it that way somehow,
to sort it, which would actually be quite helpful i think. Maybe if
the macro could look directly at column O, create a column to use for
its data, which it will delete, but also insert a column to the right
of Col O and place the markers there.

Would this be difficult to accomplish?

If its easier, yes, just placing a marker in column B would work just
as well, i can shift columns manually without a problem.

Thanks for the good idea though, as this would allow me to sort by
Unique or cancelled value (since you cannot sort by formatting in
2003)

-Pogster

Hey Pete,

Yes, absolutely, that would probably be useful. Thing is, management
is going to look at what ive done and decide what format they want it
in anyway. So i could group them by paired or unpaired and then they
could just go and undo it with some stupid autofilter, but i believe
it would look better and be easier to digest in such a sorted format.

In short: yes this would be useful !!!

As for the multicolor idea which i shot down, i can see how it would
be useful if say a seperate color was applied to ammount under 50k,
50k - 150k, 150k - 250k, 250 - 500k, 500k - 1M, etc. etc.

This way the colors could represent ranges of values.

Again, i do not need this functionality, but it would be interesting
if you wanted to implement it for others use.

Thanks again for keeping up with this, i hope your code can be put to
good use outside of my particular dilemma, because i think its such
useful functionality.

-Pogster

Okay, here's an amended macro in full, along the lines you have
suggested:

Sub Mark_duplicates_a()
'
' 04/10/2007, Pete Ashurst
' amended 17/10/2007
'
Dim my_top As Long
Dim my_bottom As Long
Dim colour As Integer
Application.ScreenUpdating = False
Columns("B").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.Value = "1"
Range(Selection, Selection.End(xlDown)).Select
Selection.DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Columns("A:C").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
my_top = 1
my_bottom = Cells(Rows.Count, "A").End(xlUp).Row
Do Until my_top = my_bottom
If Int(Cells(my_top, 1).Value * 10) / 10 =
Int(Abs(Cells(my_bottom, 1).Value) * 10) / 10 Then
Select Case Cells(my_top, 1).Value
Case Is 50000
colour = 4 'Bright Green
Case Is 150000
colour = 6 'Yellow
Case Is 250000
colour = 8 'Turquoise
Case Is 500000
colour = 39 'Lavendar
Case Else
colour = 15 'Grey
End Select
Range("A" & my_top).Interior.ColorIndex = colour
Cells(my_top, 2).Value = "Y"
Range("A" & my_bottom).Interior.ColorIndex = colour
Cells(my_bottom, 2).Value = "Y"
my_top = my_top + 1
my_bottom = my_bottom - 1
ElseIf Cells(my_top, 1).Value Abs(Cells(my_bottom, 1).Value)
Then
my_top = my_top + 1
Else
my_bottom = my_bottom - 1
End If
Loop
Columns("A:C").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
Columns("C").Select
Selection.Delete Shift:=xlToLeft
Range("C1").Select
Application.ScreenUpdating = True
End Sub

This inserts a new column B, so any other data on the sheet will be
moved to the right. Column B will contain "Y" wherever there is a
pairing - you could change this to "paired" or some-such by making the
obvious two changes mid-way in the macro.

The macro also applies colour banding for the ranges you suggested. It
should be fairly obvious how to introduce other ranges in the CASE
part of the macro (just keep the numbers in sequence), and you can
easily change colours if you don't like mine - here's some other
numbers you might like to play about with:

Red - 3, Aqua - 42, Orange - 46, Pink - 7, Tan - 40

Maybe marginally slower, but still less than 3 seconds on my test data
of nearly 2200 values.

Hope this helps.

Pete

On Oct 16, 3:30 pm, wrote:
Hey Pete,

Yes, absolutely, that would probably be useful. Thing is, management
is going to look at what ive done and decide what format they want it
in anyway. So i could group them by paired or unpaired and then they
could just go and undo it with some stupid autofilter, but i believe
it would look better and be easier to digest in such a sorted format.

In short: yes this would be useful !!!

As for the multicolor idea which i shot down, i can see how it would
be useful if say a seperate color was applied to ammount under 50k,
50k - 150k, 150k - 250k, 250 - 500k, 500k - 1M, etc. etc.

This way the colors could represent ranges of values.

Again, i do not need this functionality, but it would be interesting
if you wanted to implement it for others use.

Thanks again for keeping up with this, i hope your code can be put to
good use outside of my particular dilemma, because i think its such
useful functionality.

-Pogster

Pete,

Great mod! The color coding works great, though in my version of the
macro i removed your Select case as i did not need the color coding.
But the column B identifier is also very useful. Thank you for this
valuable addition to your already superb macro!

The speed decrease is completely trivial, as it is barely noticeable.

I will toy around with this macro and possibly add more comments to
the file itself as i figure out its full functionality.

You are so the man.

Thanks pete.

-Pogster