Count Unique Entries
 

I have a sheet sorted on columns C & D. Starting with "C1:D1", I need code
to store each row to myArray that has data in columns C & D matching "C1:D1"
so I can process the rows, then find the next unique combinatioin of "C1:D1",
store all of those rows to an myArray for processing. As I mentioned the
sheet is already sorted so that may make things a little easier.

-JL-

Count Unique Entries
 

Possibly this code will be helpful:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"JL" wrote:

I have a sheet sorted on columns C & D. Starting with "C1:D1", I need code
to store each row to myArray that has data in columns C & D matching "C1:D1"
so I can process the rows, then find the next unique combinatioin of "C1:D1",
store all of those rows to an myArray for processing. As I mentioned the
sheet is already sorted so that may make things a little easier.

-JL-

Count Unique Entries
 

The part I am really having a problem with is counting the quantity in each
unique combination of columns C & D so I can create the array to store the
rows in. IOW, there may be one row with "Apples" and "Oranges" in columns C
& D, 5 rows with "Apples" and "Pears" in C & D... I need to count each
combination one at a time so I can create the array, perform my processing,
then count the number in the next unique combination...

JL

"Tom Ogilvy" wrote:

Possibly this code will be helpful:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"JL" wrote:

I have a sheet sorted on columns C & D. Starting with "C1:D1", I need code
to store each row to myArray that has data in columns C & D matching "C1:D1"
so I can process the rows, then find the next unique combinatioin of "C1:D1",
store all of those rows to an myArray for processing. As I mentioned the
sheet is already sorted so that may make things a little easier.

-JL-

Count Unique Entries
 

Sub abcdef()
Dim s As String, cell As Range
Dim rng As Range, colKeys As Variant
Dim itm, cnt As Long
Dim i As Long
Dim v As Variant
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
Set rng = Range(Cells(1, "C"), _
Cells(Rows.Count, "C").End(xlUp))
For Each cell In rng
s = Trim(cell.Text) & "_" & Trim(cell.Offset(0, 1).Text)
cnt = 1
On Error Resume Next
nodupes.Add s, cnt
v = Err.Number
On Error GoTo 0
If v < 0 Then
nodupes.Item(s) = nodupes.Item(s) + 1
Err.Clear
End If
Next
colKeys = nodupes.Keys
For i = LBound(colKeys) To UBound(colKeys)
Debug.Print colKeys(i), nodupes.Item(colKeys(i))
Next
End Sub

will give you a list of uniques and count of rows.

Output is placed in the immediate window just for demo purposes.

--
Regards,
Tom Ogilvy


"JL" wrote:

The part I am really having a problem with is counting the quantity in each
unique combination of columns C & D so I can create the array to store the
rows in. IOW, there may be one row with "Apples" and "Oranges" in columns C
& D, 5 rows with "Apples" and "Pears" in C & D... I need to count each
combination one at a time so I can create the array, perform my processing,
then count the number in the next unique combination...

JL

"Tom Ogilvy" wrote:

Possibly this code will be helpful:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"JL" wrote:

I have a sheet sorted on columns C & D. Starting with "C1:D1", I need code
to store each row to myArray that has data in columns C & D matching "C1:D1"
so I can process the rows, then find the next unique combinatioin of "C1:D1",
store all of those rows to an myArray for processing. As I mentioned the
sheet is already sorted so that may make things a little easier.

-JL-

Count Unique Entries
 

JL, Here give this code a shot, maybe it'l get you going in the right
direction. If it needs to be changed, posted me back...
enjoy, Rick (FBKS, AK)


Sub testit()
Dim cRow As Integer, x As Integer
Dim Count As Integer
Dim CompStr As String
Dim myArray As Variant

cRow = Range("C1").End(xlDown).Row
CompStr = Cells(1, "C") & "," & Cells(1, "D")
For x = 2 To cRow
If CompStr = Cells(x, "C") & "," & Cells(x, "D") Then
Count = Count + 1
End If
Next x

ReDim myArray(1 To Count)

End Sub



"JL" wrote in message
...
The part I am really having a problem with is counting the quantity in
each
unique combination of columns C & D so I can create the array to store the
rows in. IOW, there may be one row with "Apples" and "Oranges" in columns
C
& D, 5 rows with "Apples" and "Pears" in C & D... I need to count each
combination one at a time so I can create the array, perform my
processing,
then count the number in the next unique combination...

JL

"Tom Ogilvy" wrote:

Possibly this code will be helpful:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"JL" wrote:

I have a sheet sorted on columns C & D. Starting with "C1:D1", I need
code
to store each row to myArray that has data in columns C & D matching
"C1:D1"
so I can process the rows, then find the next unique combinatioin of
"C1:D1",
store all of those rows to an myArray for processing. As I mentioned
the
sheet is already sorted so that may make things a little easier.

-JL-

Count Unique Entries
 

Thank you Tom. I'm not seeing any output but am sure I'm just missing
something obvious. I've been reassigned today and am going to have to
continue on with this tomorrow.

"Tom Ogilvy" wrote:

Sub abcdef()
Dim s As String, cell As Range
Dim rng As Range, colKeys As Variant
Dim itm, cnt As Long
Dim i As Long
Dim v As Variant
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
Set rng = Range(Cells(1, "C"), _
Cells(Rows.Count, "C").End(xlUp))
For Each cell In rng
s = Trim(cell.Text) & "_" & Trim(cell.Offset(0, 1).Text)
cnt = 1
On Error Resume Next
nodupes.Add s, cnt
v = Err.Number
On Error GoTo 0
If v < 0 Then
nodupes.Item(s) = nodupes.Item(s) + 1
Err.Clear
End If
Next
colKeys = nodupes.Keys
For i = LBound(colKeys) To UBound(colKeys)
Debug.Print colKeys(i), nodupes.Item(colKeys(i))
Next
End Sub

will give you a list of uniques and count of rows.

Output is placed in the immediate window just for demo purposes.

--
Regards,
Tom Ogilvy


"JL" wrote:

The part I am really having a problem with is counting the quantity in each
unique combination of columns C & D so I can create the array to store the
rows in. IOW, there may be one row with "Apples" and "Oranges" in columns C
& D, 5 rows with "Apples" and "Pears" in C & D... I need to count each
combination one at a time so I can create the array, perform my processing,
then count the number in the next unique combination...

JL

"Tom Ogilvy" wrote:

Possibly this code will be helpful:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"JL" wrote:

I have a sheet sorted on columns C & D. Starting with "C1:D1", I need code
to store each row to myArray that has data in columns C & D matching "C1:D1"
so I can process the rows, then find the next unique combinatioin of "C1:D1",
store all of those rows to an myArray for processing. As I mentioned the
sheet is already sorted so that may make things a little easier.

-JL-

Count Unique Entries
 

Thank you Rick. It'll be tomorrow now but I'll give it a go.
JL

"Rick Hansen" wrote:

JL, Here give this code a shot, maybe it'l get you going in the right
direction. If it needs to be changed, posted me back...
enjoy, Rick (FBKS, AK)


Sub testit()
Dim cRow As Integer, x As Integer
Dim Count As Integer
Dim CompStr As String
Dim myArray As Variant

cRow = Range("C1").End(xlDown).Row
CompStr = Cells(1, "C") & "," & Cells(1, "D")
For x = 2 To cRow
If CompStr = Cells(x, "C") & "," & Cells(x, "D") Then
Count = Count + 1
End If
Next x

ReDim myArray(1 To Count)

End Sub



"JL" wrote in message
...
The part I am really having a problem with is counting the quantity in
each
unique combination of columns C & D so I can create the array to store the
rows in. IOW, there may be one row with "Apples" and "Oranges" in columns
C
& D, 5 rows with "Apples" and "Pears" in C & D... I need to count each
combination one at a time so I can create the array, perform my
processing,
then count the number in the next unique combination...

JL

"Tom Ogilvy" wrote:

Possibly this code will be helpful:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"JL" wrote:

I have a sheet sorted on columns C & D. Starting with "C1:D1", I need
code
to store each row to myArray that has data in columns C & D matching
"C1:D1"
so I can process the rows, then find the next unique combinatioin of
"C1:D1",
store all of those rows to an myArray for processing. As I mentioned
the
sheet is already sorted so that may make things a little easier.

-JL-

Count Unique Entries
 

Is the immediate window in the VBE visible?

As I said:

Output is placed in the immediate window just for demo purposes.

--
Regards,
Tom Ogilvy



"JL" wrote in message
...
Thank you Tom. I'm not seeing any output but am sure I'm just missing
something obvious. I've been reassigned today and am going to have to
continue on with this tomorrow.

"Tom Ogilvy" wrote:

Sub abcdef()
Dim s As String, cell As Range
Dim rng As Range, colKeys As Variant
Dim itm, cnt As Long
Dim i As Long
Dim v As Variant
Dim nodupes As Object
Set nodupes = CreateObject("Scripting.Dictionary")
Set rng = Range(Cells(1, "C"), _
Cells(Rows.Count, "C").End(xlUp))
For Each cell In rng
s = Trim(cell.Text) & "_" & Trim(cell.Offset(0, 1).Text)
cnt = 1
On Error Resume Next
nodupes.Add s, cnt
v = Err.Number
On Error GoTo 0
If v < 0 Then
nodupes.Item(s) = nodupes.Item(s) + 1
Err.Clear
End If
Next
colKeys = nodupes.Keys
For i = LBound(colKeys) To UBound(colKeys)
Debug.Print colKeys(i), nodupes.Item(colKeys(i))
Next
End Sub

will give you a list of uniques and count of rows.

Output is placed in the immediate window just for demo purposes.

--
Regards,
Tom Ogilvy


"JL" wrote:

The part I am really having a problem with is counting the quantity in
each
unique combination of columns C & D so I can create the array to store
the
rows in. IOW, there may be one row with "Apples" and "Oranges" in
columns C
& D, 5 rows with "Apples" and "Pears" in C & D... I need to count
each
combination one at a time so I can create the array, perform my
processing,
then count the number in the next unique combination...

JL

"Tom Ogilvy" wrote:

Possibly this code will be helpful:

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"JL" wrote:

I have a sheet sorted on columns C & D. Starting with "C1:D1", I
need code
to store each row to myArray that has data in columns C & D
matching "C1:D1"
so I can process the rows, then find the next unique combinatioin
of "C1:D1",
store all of those rows to an myArray for processing. As I
mentioned the
sheet is already sorted so that may make things a little easier.

-JL-