Those codes I posted won't work on a Mac. It incompatibility rather than error.
Sub jksljk()
Dim lra As Long, lre As Long
Dim rga, rge, ua(), ue(), uboth()
Dim ka As Long, ke As Long, kboth As Long
Dim c, x
Sheets("sheet1").Activate
lra = Range("A" & Rows.Count).End(3).Row - 1
lre = Range("E" & Rows.Count).End(3).Row - 1
rga = Range("A2").Resize(lra)
rge = Range("E2").Resize(lre)
ReDim ua(1 To lra, 1 To 1)
ReDim ue(1 To lre, 1 To 1)
ReDim uboth(1 To lra + lre, 1 To 1)
Application.ScreenUpdating = False
Sheets("sheet2").Activate
With Range("A1").Resize(lra)
.Value = rga
.Sort .Cells(1), 1, Header:=xlNo
x = vbNullString
For Each c In .Cells.Value
If c <> x Then
ka = ka + 1
ua(ka, 1) = c
x = c
End If
Next
End With
Sheets("sheet1").Range("G2").Resize(ka) = ua
Sheets("sheet1").Range("G1") = "Uniques in A"
With Range("E1").Resize(lre)
.Value = rge
.Sort .Cells(1), 1, Header:=xlNo
x = vbNullString
For Each c In .Cells.Value
If c <> x Then
ke = ke + 1
ue(ke, 1) = c
x = c
End If
Next
End With
Sheets("sheet1").Range("H2").Resize(ke) = ue
Sheets("sheet1").Range("H1") = "Uniques in E"
With Range("A1").Resize(lra + ke)
.Cells(lra + 1, 1).Resize(ke).Value = ue
.Sort .Cells(1), 1, Header:=xlNo
x = vbNullString
For Each c In .Cells.Value
If c <> x Then
kboth = kboth + 1
uboth(kboth, 1) = c
x = c
End If
Next
End With
Cells.Resize(, 5).ClearContents
Sheets("sheet1").Range("I2").Resize(kboth) = uboth
Sheets("sheet1").Range("I1") = "Uniques over both"
Sheets("sheet1").Activate
Application.ScreenUpdating = True
End SubWhat do you mean by the red bit above? 4 is present in both A and B, but you say it should go in C ?????
I don't know what kind of Excel is on Macs these days. When I had one I used Parallels or VMFusion. Bootcamp wasn't usually worth the trouble.
Sub scode()
Dim a, b
Dim ua(), ub()
Dim qa() As Boolean, qb() As Boolean
Dim rwa As Long, rwb As Long
Dim ka As Long, kb As Long
Dim i As Long, j As Long
rwa = Range("A" & Rows.Count).End(3).Row
rwb = Range("B" & Rows.Count).End(3).Row
ReDim ua(1 To rwa, 1 To 1), ub(1 To rwb, 1 To 1)
ReDim qa(rwa), qb(rwb)
a = Range("A1").Resize(rwa)
b = Range("B1").Resize(rwb)
For i = 1 To rwa - 1
If qa(i) = False Then
For j = i + 1 To rwa
If qa(j) = False Then
If a(i, 1) = a(j, 1) Then qa(j) = True
End If
Next j
End If
Next i
For i = 1 To rwb - 1
If qb(i) = False Then
For j = i + 1 To rwb
If qb(j) = False Then
If b(i, 1) = b(j, 1) Then qb(j) = True
End If
Next j
End If
Next i
For i = 1 To rwa
If qa(i) = False Then
For j = 1 To rwb
If qb(j) = False Then
If a(i, 1) = b(j, 1) Then
qa(i) = True
qb(j) = True
End If
End If
Next j
End If
Next i
For i = 1 To rwa
If qa(i) = False Then ka = ka + 1: ua(ka, 1) = a(i, 1)
Next i
For j = 1 To rwb
If qb(j) = False Then kb = kb + 1: ub(kb, 1) = b(j, 1)
Next j
If ka > 0 Then Range("D1").Resize(ka) = ua
If kb > 0 Then Range("E1").Resize(kb) = ub
End Sub
