Hello guys,

I have the following problem:
- 2 columns with amounts;
- the specific amounts can appear more than once;
- I have to compare both columns and mark (f. ex. with "yellow" filling) the
amounts that appear in both of them... (seems easy until now)
- ... but you have to consider every number on its own, i.e. if:

column A: 5, 2, 2, 1, 8
column B: 5, 5, 2, 1, 1, 1

then in column A we will color 5, 2, 1, (it means that we will leave
unfilled: 2, 8) - it doesn't matter which "2" is colored and which not
in column B we will color 5, 2, 1 (and leave unfilled: 5, 1, 1) - it doesn't
matter which "1" (or "5") is colored and which is not...

Any ideas how to achieve this?
Let me know if it's not sufficiently clear...

P.S. If it's easier, we could also make a VBA that will delete both
instances (one from column A and one from column B) once it found the match,
but the final result should be the same, i. e. (in our exemple) it should
leave "2" and "8" in column A and "5", "1" and "1" in column B.

Many thanks for your help,

if you read the two columns into an array each. The arrays should be two
dimensional so that you can store both the value and the address.

Then loop through the shortest array and for each element search the
second array for the same value. If a match is found :
- colour the cells (you have the addresse in the second element)
- and either remove the element from the second array (or set the
values to an impossible value)
- goto the next item in the first array


Improvements could be made be either sorting the columns first or by
using a collection so that the numbers are held in sorted order.


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=514323

Thanks Tony,
This looks very promising... but I'm afraid that you overestimate my excel
skills.:-).
I'm still quite confused, but I'll try to dig in to these 2D arrays (haven't
heard about it until now)...


"tony h" wrote in
message ...

if you read the two columns into an array each. The arrays should be two
dimensional so that you can store both the value and the address.

Then loop through the shortest array and for each element search the
second array for the same value. If a match is found :
- colour the cells (you have the addresse in the second element)
- and either remove the element from the second array (or set the
values to an impossible value)
- goto the next item in the first array


Improvements could be made be either sorting the columns first or by
using a collection so that the numbers are held in sorted order.


--
tony h
------------------------------------------------------------------------
tony h's Profile:
http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=514323

just picked this up before my all day meeting but it might get yo
started


This should populate the arrays

let me know how you get on and I will (probably get to pick up on thi
this evening.)

Cheers - unless someone else has

Option Explicit

Sub a()


Dim ary1(100, 2) As Variant
Dim ary2(100, 2) As Variant
Dim i As Integer
Dim rng As Range


Set rng = ActiveSheet.Range("A1")
For i = 1 To 100
' PUT THE VALUES FROM COLUMN a INTO FIRST ARRAY
ary1(i, 0) = rng.Value
ary1(i, 1) = rng.Address

' PUT VALUES FROM COLUMN b INTO 2ND ARRAY
ary2(i, 0) = rng.Offset(, 1).Value
ary2(i, 1) = rng.Offset(, 1).Address

Next i

End Su

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=51432

If you tried my last post you will find that I hadn't tested it - oops!
Anyway I amout of my meeting so you can try this.

It colours matched cells in columns A and B. The loops are to 101 but
there are various ways of extending this permanently or dynamically

Have fun

Option Explicit
Sub a()


Dim ary1(0 To 100, 0 To 1) As Variant
Dim ary2(0 To 100, 0 To 1) As Variant
Dim i As Integer
Dim wks As Worksheet
Dim rng As Range
Dim i2 As Integer

Set wks = ActiveSheet
Set rng = wks.Range("A1")
For i = 0 To 100
' PUT THE VALUES FROM COLUMN a INTO FIRST ARRAY
ary1(i, 0) = rng.Offset(i).Value
ary1(i, 1) = rng.Offset(i).Address

' PUT VALUES FROM COLUMN b INTO 2ND ARRAY
ary2(i, 0) = rng.Offset(i, 1).Value
ary2(i, 1) = rng.Offset(i, 1).Address

Next i

'Stop
For i = 0 To 100
If ary1(i, 1) = "" Then Exit For
For i2 = 0 To 100
If ary2(i2, 1) = "" Then Exit For

If ary1(i, 0) < "" And ary1(i, 0) = ary2(i2, 0) And ary1(i, 1)
< "-" And ary2(i2, 1) < "-" Then
wks.Range(ary1(i, 1)).Interior.ColorIndex = 6
wks.Range(ary2(i2, 1)).Interior.ColorIndex = 6
ary1(i, 1) = "-"
ary2(i2, 1) = "-"
Exit For
End If
Next i2
Next i
MsgBox "done"
'Stop
End Sub


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=514323

Hi Tony h,
Your solution is magic! It saved me at least 3 hours of work for this time
only! (I don't count any future similar cases I'll be confronted with...)
Let me know if there is any way I can pay back my debt :-),
Mark

P.S. By the way, do you know why I don't see your answers if I pass through
MS Outlook, but I can find them through GoogleGroups (that's where I copied
your solution from)?

==============

If you tried my last post you will find that I hadn't tested it - oops!
Anyway I amout of my meeting so you can try this.

It colours matched cells in columns A and B. The loops are to 101 but
there are various ways of extending this permanently or dynamically

Have fun

Option Explicit
Sub a()


Dim ary1(0 To 100, 0 To 1) As Variant
Dim ary2(0 To 100, 0 To 1) As Variant
Dim i As Integer
Dim wks As Worksheet
Dim rng As Range
Dim i2 As Integer


Set wks = ActiveSheet
Set rng = wks.Range("A1")
For i = 0 To 100
' PUT THE VALUES FROM COLUMN a INTO FIRST ARRAY
ary1(i, 0) = rng.Offset(i).Value
ary1(i, 1) = rng.Offset(i).Address


' PUT VALUES FROM COLUMN b INTO 2ND ARRAY
ary2(i, 0) = rng.Offset(i, 1).Value
ary2(i, 1) = rng.Offset(i, 1).Address


Next i


'Stop
For i = 0 To 100
If ary1(i, 1) = "" Then Exit For
For i2 = 0 To 100
If ary2(i2, 1) = "" Then Exit For


If ary1(i, 0) < "" And ary1(i, 0) = ary2(i2, 0) And ary1(i, 1)
< "-" And ary2(i2, 1) < "-" Then
wks.Range(ary1(i, 1)).Interior.ColorIndex = 6
wks.Range(ary2(i2, 1)).Interior.ColorIndex = 6
ary1(i, 1) = "-"
ary2(i2, 1) = "-"
Exit For
End If
Next i2
Next i
MsgBox "done"
'Stop
End Sub


--
tony h