MrExcel Publishing
Your One Stop for Excel Tips & Solutions

There must be an easier way!!


Posted by Jason on December 27, 2001 4:48 PM

I have a coloum of numbers [coloumn a](mostly the numbers are 10 digits long but can be between 8 and 12) this coloumn contains up to 4000 numbers, some of these numbers are often repeated so in 4000 numbers there may only be 2000 individual numbers, what i require is a macro tool that can check down the coloumn and count the individual numbers (not count the multiples) then with the count of the individual numbers divide that number by the amount of consultants[coloumn b],, say there is 750 actual numbers and 400 individual numbers and 4 consultants each consultant gets there name placed against 100 individual numbers but all 750 numbers would have a consultant allocated to them [if a number was repeated 10 times the consultants name would be placed next to each instance but it would only count as 1 out of the 100 because the number was the same. (there can be 2 to 15 consultants number differs the consultant is just a first and last name ie Jason Robins)

at the moment the process of allocating consultants to these numbers is a long long long process the number i have in coloumn a is not a phone number its is a CIDN
(customer id number) this number this number appears next to all of one customers different telephone numbers

ie the below would count as one individual customer for a consultant

name ph number cidn
joe smith 5551234 123456789
joe smith 5551235 123456789
joe smith 5551236 123456789
joe smith 5551237 123456789


what i am hoping to be able to do is copy the CIDN coloum into a new document then write down the consultants i wish to work on those records in the next coloumn
run the macro and have an even allocation of customers for consultants then copy the CIDN and NEW consultant coloum into the original document.

if this makes limited sense in some places i apologise im am kindoff at a witts end it is a horrid manual job and any help with a macro would be incredibly helpful

to anyone who got this far in the post thanks very much for your time.


Posted by Tom Dickinson on December 28, 2001 9:26 AM

Step 1: copy the numbers into column B of a new sheet.
Step 2: In column A, put in numbers in chronological order (1,2,3,etc) You can do this easily by putting in the frist 2 numbers, then highlighting both cells, then move the cursor to the lower right until it becomes crosshairs, then left click (and hold) and drag as far down as necessary.
Step 3: Highlight Columns A and B and do a sort, indexing on column B.
Step 4: List all your consultants in column D
Step 5: Paste the following into a macro and run it.

Sub Consultants()
Dim Cntr1, Cntr2, Cnt1 As Integer
Cnt1 = range("D1").End(xlDown).Row
Cntr1 = 2
Cntr2 = 1
range("C1") = range("D1")
Do While range("B" & Cntr1) <> Empty
If range("B" & Cntr1) <> range("B" & Cntr1 - 1) Then
If Cntr2 = Cnt1 Then
Cntr2 = 1
Else
Cntr2 = Cntr2 + 1
End If
End If
range("C" & Cntr1) = range("D" & Cntr2)
Cntr1 = Cntr1 + 1
Loop
End Sub

Step 6: Sort columns A, B, and C, indexing on column A.

Step 7: Copy columns B and C back to your original spread sheeet