Need Help w/ Excel Macro To Manipulate Mailing List... Most Of The Way There, I Think....

[SeanEboy]

So, I have a mailing list of 22k + entries.. However, these are names of faculty at only ~ 1800 institutions. I am trying to "go green" if you will, by NOT sending 20 catalogs to (1) school if there is indeed 20 faculty, as that is just not necessary. I have built a matrix that I would like to abide by in terms of catalogs to be sent.
Basically:
If there is 1 teacher, I want 1 catalog to be sent
If there are 2 teachers, send 2 catalogs
If 3, send 2
If 4, send 2
If 5, send 3
If 6, send 3
If 7, send 4
etc, etc.

I have a macro someone had made me previously, on this forum (link below), which I have also posted again in to see if that same person is still around to help.. : http://discussions.virtualdr.com/sho...d.php?t=244006

A sample of this years file can be found here:

https://docs.google.com/spreadsheet/...lQ5MDdnc2ZMc2c

If anyone is confident they can take care of this.. I will gladly compensate via paypal.

[kpark91]

Hi, is the sample spreadsheet posted in the link still valid?
and the most number of catalogs you want to send is, I assumed, 4?

[SeanEboy]

Thanks for the response Kpark - I edited the post to include a sample sheet of this years list, 100 entries... As for the full matrix, ideally it would great if I can modify it to find the "sweet spot" in terms of quantities. We are printing 17,000 catalogs, and quite frankly I would like to have some left over... So, if possible, let's start with this:
If - then
1-1
2-1
3-1
4-2
5-2
6-3
7-3
8-4
9-4
10-5
11-5
12-6
13-6
14-6
15-6
16-7
17-7
18-8
19-8
20-8

[abousetta]

Hi SeanEboy,

Could you walk through the worksheet? For example UNIVERSITY OF PUERTO RICO has 21 entries and all are in the NURSING DEPT. So you want to know how many catologs to send them?

Is this correct?

abousetta
.

[SeanEboy]

Yes, and I want the university name (INST column I believe) to determine how many catalogs are sent. I also would like those that were NOT sent a catalog, to be on another list for future use. The names are not important, it's the amount of hits per a school that are. For example, I don't want 21 catalogs showing up at Puerto Rico... I just want the amount of listings determined by the matrix, which would be 8.

[abousetta]

Is this what you are after?

Please Login or Register  to view this content.

P.S. Check your matrix, you used 6 catalogs 4 times instead of twice

[SeanEboy]

Although I would like that as well.. I need an excel sheet listing the individual listings to send to, that I can pass along to the plant.

[abousetta]

Do you mean the institution's address (MSTREET, MCITY, MSTATE, MZIPCODE)?

Who should be the contact person at the institution? The first name on the list for each university?

[kpark91]

Please keep in mind that you must SORT the mailing list first.

Please Login or Register  to view this content.

In Sheet1 there is your database
In Sheet2 that is where result is going to be printed to
Sheet 3 has a table of your 'special cases'

Please see attached.
Untitled spreadsheet.xls

The code above has been modified to make it a little faster.
Just copy + replace the code in the file with above. :P

[SeanEboy]

Awesome Kpark... Now, where would I find the entries that were omitted? Also, the list should already be sorted, no? All the schools are listed in alphabetical order already, shall I sort again?

[kpark91]

If you are sure it's sorted alphabetically then it's fine. It should be sorted by column D (Institution column), btw. Nothing else.
and I'm looking to add entries that were omitted.

[SeanEboy]

Ok, so I'm a total newb in terms of macros and excel... So, I downloaded your untitled spreadsheet, I pasted my full database (22k entries) into sheet1, then go to developer tab, and click macros, then click kpark, and run.. Yet, it still gives me the same amount of listings as the original sheet1...? Is there something I am doing wrong?

[kpark91]

My bad... There was Sheets("Sheet3") missing
Try this code

Please Login or Register  to view this content.

Sheet1 is where your data goes (from column A to H)
Sheet2 is where your results will appear
Sheet3 is where you put your 'special cases' which you can modify
Sheet4 is where all the omitted data appears.

Please see attached.
Untitled spreadsheet.xls

[SeanEboy]

Ok, so I copied and pasted this code over the old one.. Now I'm getting "Run-time error '9': Subscript out of range... I ran the debugger, which had the red text below highlighted in yellow....

Sub kpark()
Dim Msg As Integer
Msg = MsgBox("Have you sorted the mailing list?", vbYesNo)
If Msg = 7 Then
MsgBox ("Please sort the list.")
Exit Sub
End If

Application.ScreenUpdating = False
Dim LR1 As Long, i1 As Long, i2 As Long, i4 As Long, count As Long, catNum As Long

count = 1
catNum = 1
i2 = 3
i4 = 2
LR1 = Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
Sheets("Sheet1").Range("A2:H2").Copy Sheets("Sheet2").Range("A2")

For i1 = 3 To LR1
If Sheets("Sheet1").Range("D" & i1).Value = Sheets("Sheet1").Range("D" & i1 - 1).Value Then
count = count + 1
If Sheets("Sheet3").Range("B" & count + 1).Value > catNum Then
catNum = catNum + 1
Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet2").Range("A" & i2)
i2 = i2 + 1
Else
Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet4").Range("A" & i4)
i4 = i4 + 1
End If
Else
count = 1
catNum = 1
Sheets("Sheet1").Range("A" & i1 & ":H" & i1).Copy Sheets("Sheet2").Range("A" & i2)
i2 = i2 + 1
End If

Next i1

Application.ScreenUpdating = True

End Sub

[kpark91]

Add a worksheet named "Sheet4"
or just simply download the newly uploaded workbook in my previous post.

[SeanEboy]

Great, thanks a lot... It appears that solved it for sure... Now, if I want to modify the amounts sent, I simply edit sheet 3, correct?

[kpark91]

That is correct. But don't move the table cuz then you would have to change the code.
You can just add numbers to the bottom of the table with increments of 1 in column A of sheet3.

[SeanEboy]

Great thanks, want to PM me your email..? I'll buy you drinks and dinner for the help! ;c)

[abousetta]

Have a look at the attachment. Pivot table and Index/Match.

abousetta