Hi Howard,

Am Sat, 22 Feb 2014 10:31:47 +0100 schrieb Claus Busch:

Sub Test_CB()

or write the values from sheet1 without duplicates to sheet2 and
calculate the number of occurence:

Sub Test_CB2()
Dim LRow As Long
Dim myArr As Variant
Dim rngC As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
myArr = .Range("A1:B" & LRow)
End With

With Sheets("Sheet2")
.Range("A1").Resize(LRow, 2) = myArr
.Range("A1:B" & LRow).RemoveDuplicates _
Columns:=Array(1, 2), Header:=xlNo
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("B1: B" & LRow)
If Len(rngC) = 0 Then
rngC = WorksheetFunction.CountIf(Sheets("Sheet1") _
.Range("A1:A" & LRow), rngC.Offset(, -1))
End If
Next
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Sat, 22 Feb 2014 11:10:52 +0100 schrieb Claus Busch:

found an error. Try instead
Sub Test_CB2()

found an error. Try instead

Sub Test_CB2()
Dim LRow1 As Long, LRow2 As Long
Dim myArr As Variant
Dim rngC As Range

With Sheets("Sheet1")
LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
myArr = .Range("A1:B" & LRow1)
End With

With Sheets("Sheet2")
.Range("A1").Resize(LRow1, 2) = myArr
.Range("A1:B" & LRow1).RemoveDuplicates _
Columns:=Array(1, 2), Header:=xlNo
LRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("B1: B" & LRow2)
If Len(rngC) = 0 Then
rngC = WorksheetFunction.CountIf(Sheets("Sheet1") _
.Range("A1:A" & LRow1), rngC.Offset(, -1))
End If
Next
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Saturday, February 22, 2014 2:14:44 AM UTC-8, Claus Busch wrote:
Hi Howard,

found an error. Try instead

Sub Test_CB2()


Hi Claus,

The Test_CB is excellent!!
A work of art to me.

The Test_CB2 is puzzling to me. Not sure what the results should look like.

If I run the code on this data on sheet 1

P-1
P-2
P-3 x
P-5
P-6
P-7 x
P-8
P-9
P-10 x
P-1
P-2
P-3 x
P-5
P-6
P-7 x
P-8
P-9
P-10 x


I get this on sheet 2.

P-1
P-2
P-3 x
P-5
P-6
P-7 x
P-8
P-9
P-10 x
P-1
P-2
P-3 x
P-5
P-6
P-7 x
P-8
P-9
P-10 x
#N/A #N/A

And an error alert Invalid procedure call or argument

I did change all the A1's and B1's to A2's and B2's to avoid headers. Did the same with Test_CB and it works well.

Howard

Hi Howard,

Am Sat, 22 Feb 2014 06:47:12 -0800 (PST) schrieb L. Howard:

The Test_CB2 is puzzling to me. Not sure what the results should look like.

in the first answer Test_CB2 has an error
I tried your example with the second answer and the fixed error and I
get:
P-1 2
P-2 2
P-3 x
P-5 2
P-6 2
P-7 x
P-8 2
P-9 2
P-10 x



Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Saturday, February 22, 2014 6:53:48 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sat, 22 Feb 2014 06:47:12 -0800 (PST) schrieb L. Howard:



The Test_CB2 is puzzling to me. Not sure what the results should look like.



in the first answer Test_CB2 has an error

I tried your example with the second answer and the fixed error and I

get:

P-1 2

P-2 2

P-3 x

P-5 2

P-6 2

P-7 x

P-8 2

P-9 2

P-10 x







Regards

Claus B.

--

Hi Claus,

This would be great for a sheet 2 result, really it is the same as a sheet 1 isn't it?

Not sure why my book won't do that??

I tried your example with the second answer and the fixed error and I
get:
P-1 2
P-2 2
P-3 x
P-5 2
P-6 2
P-7 x
P-8 2
P-9 2
P-10 x


Howard

Hi Howard,

Am Sat, 22 Feb 2014 07:44:14 -0800 (PST) schrieb L. Howard:

This would be great for a sheet 2 result, really it is the same as a sheet 1 isn't it?

the result is the same as in sheet1.
Please have a look:
https://skydrive.live.com/#cid=9378A...121822A3%21326
for "UniqueValues"

There are 3 macros. The two you know and another one to keep all values
from A if in B is text.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Saturday, February 22, 2014 7:52:14 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Sat, 22 Feb 2014 07:44:14 -0800 (PST) schrieb L. Howard:



This would be great for a sheet 2 result, really it is the same as a sheet 1 isn't it?



the result is the same as in sheet1.

Please have a look:

https://skydrive.live.com/#cid=9378A...121822A3%21326

for "UniqueValues"



There are 3 macros. The two you know and another one to keep all values

from A if in B is text.





Regards

Claus B.

--

Hi Claus,

I do believe I got it figured out.

Test_CB2 doesn't like Option Base 1

All seems to be working fine now.

Not sure if Test_CB3 is needed, but I will indeed hang on to it.

Thanks Claus, your magic wand works once again. Nice indeed.

Regards,
Howard

Hi Howard,

Am Sat, 22 Feb 2014 06:47:12 -0800 (PST) schrieb L. Howard:

P-1
P-2
P-3 x
P-5
P-6
P-7 x
P-8
P-9
P-10 x
P-1
P-2
P-3 x
P-5
P-6
P-7 x
P-8
P-9
P-10 x

in your first posting you wrote you want to keep the values in A if B
has text.
How about your example? Do you want to keep P3 or P7 two times?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2