Hi,

I work in an environment where we use mailshots for our marketing. We
normally receive an Excel spreadsheet from our contacts supplier with +- 50
000 records in them. I then use this spreadsheet to group the contacts into
their applicable mailshot group according to their designation. I would like
to automate this process as I'm currently doing it manually.

I'm unfortunately not a programmer and need some help on this. Any ideas?

Thanks

tammy: Not sure what you mean by group the contacts. If you run a learn
macro while you are doing the manual operation then it would be easier to see
what you want to do.

I'm not sure if you are just doing some sorts or a lot of cuts and pastes.
Sample of the spreadsheet columns would also be useful. Look at other
postings to get ideas.

1) Before processing the spreadsheet go to tools Menu - Macros -Record new
macro. Press Ok in the Pop Up window.
2) Do you manual operations
3) Stop Recording in the Tools Menu - Macro - Stop Recording
4) Copy macro by going again into Tools Menu - Macro - Visual Basic Editor.
If code is not visible double click on Module - Module 1 in the VBA Project
window.
5) Paste macro on this web page.

"Tammy" wrote:

Hi,

I work in an environment where we use mailshots for our marketing. We
normally receive an Excel spreadsheet from our contacts supplier with +- 50
000 records in them. I then use this spreadsheet to group the contacts into
their applicable mailshot group according to their designation. I would like
to automate this process as I'm currently doing it manually.

I'm unfortunately not a programmer and need some help on this. Any ideas?

Thanks

Hi Joel,

We use an ACT! database to send out all our mailshots. Within the ACT!
database you can choose what groups you would like to send the mailshot to;
i.e there is multiple groups within the database; e.g. Financial, HR, CEOS,
ProjMan, Sales, Training, Management, etc. When I receive the Excel
spreadsheet, I need to catogorize all of the contacts/people into a group
depending on their job description; e.g. Manager will fall under Management,
Treasurer will fall under Financial, etc. After completion I then import the
Excel spreadsheet into the ACT! Database.

The only columns I work with in the Excel spreadsheet is "POSITION" (which
specifies the person's job description) and "GROUP" (which specifies the ACT!
Database group the user will belong to. I create this column manually to
specify the end-result). I normally AutoFilter the "POSITION" column and then
I manually check each filter result against the various "Group" criteria.

I need a tool that can check the POSITION column; compare it to the various
groups' criteria, select the group the person should belong to and enter the
result in the GROUPS column.

Hope this makes sense.


"Joel" wrote:

tammy: Not sure what you mean by group the contacts. If you run a learn
macro while you are doing the manual operation then it would be easier to see
what you want to do.

I'm not sure if you are just doing some sorts or a lot of cuts and pastes.
Sample of the spreadsheet columns would also be useful. Look at other
postings to get ideas.

1) Before processing the spreadsheet go to tools Menu - Macros -Record new
macro. Press Ok in the Pop Up window.
2) Do you manual operations
3) Stop Recording in the Tools Menu - Macro - Stop Recording
4) Copy macro by going again into Tools Menu - Macro - Visual Basic Editor.
If code is not visible double click on Module - Module 1 in the VBA Project
window.
5) Paste macro on this web page.

"Tammy" wrote:

Hi,

I work in an environment where we use mailshots for our marketing. We
normally receive an Excel spreadsheet from our contacts supplier with +- 50
000 records in them. I then use this spreadsheet to group the contacts into
their applicable mailshot group according to their designation. I would like
to automate this process as I'm currently doing it manually.

I'm unfortunately not a programmer and need some help on this. Any ideas?

Thanks

Tammy: You 1st need to generate a table with th position and group. Then use
this table to fill in the new worksheets automatically. I would start by
taking the position and group column from an old worksheet and put it in a
new worksheet to get the table. sort on these two columns and then get rid
of the duplicate rows. A script can be quickly wirten to get rid of
duplicates.

Put the Sorted position and groups into column a and b then run program
below. This will be the table to populate future worksheets. You can use
vlookup function to do the lookup.

Sub RemoveDuplicates()

RowCount = 1
Do While Not IsEmpty(Cells(RowCount, 1))

If StrComp(Cells(RowCount, 1), Cells(RowCount + 1, 1)) = 0 And _
StrComp(Cells(RowCount, 2), Cells(RowCount + 1, 2)) = 0 Then

Cells(RowCount, 1).EntireRow.Delete
Else
RowCount = RowCount + 1
End If

Loop

End Sub









"Tammy" wrote:

Hi Joel,

We use an ACT! database to send out all our mailshots. Within the ACT!
database you can choose what groups you would like to send the mailshot to;
i.e there is multiple groups within the database; e.g. Financial, HR, CEOS,
ProjMan, Sales, Training, Management, etc. When I receive the Excel
spreadsheet, I need to catogorize all of the contacts/people into a group
depending on their job description; e.g. Manager will fall under Management,
Treasurer will fall under Financial, etc. After completion I then import the
Excel spreadsheet into the ACT! Database.

The only columns I work with in the Excel spreadsheet is "POSITION" (which
specifies the person's job description) and "GROUP" (which specifies the ACT!
Database group the user will belong to. I create this column manually to
specify the end-result). I normally AutoFilter the "POSITION" column and then
I manually check each filter result against the various "Group" criteria.

I need a tool that can check the POSITION column; compare it to the various
groups' criteria, select the group the person should belong to and enter the
result in the GROUPS column.

Hope this makes sense.


"Joel" wrote:

tammy: Not sure what you mean by group the contacts. If you run a learn
macro while you are doing the manual operation then it would be easier to see
what you want to do.

I'm not sure if you are just doing some sorts or a lot of cuts and pastes.
Sample of the spreadsheet columns would also be useful. Look at other
postings to get ideas.

1) Before processing the spreadsheet go to tools Menu - Macros -Record new
macro. Press Ok in the Pop Up window.
2) Do you manual operations
3) Stop Recording in the Tools Menu - Macro - Stop Recording
4) Copy macro by going again into Tools Menu - Macro - Visual Basic Editor.
If code is not visible double click on Module - Module 1 in the VBA Project
window.
5) Paste macro on this web page.

"Tammy" wrote:

Hi,

I work in an environment where we use mailshots for our marketing. We
normally receive an Excel spreadsheet from our contacts supplier with +- 50
000 records in them. I then use this spreadsheet to group the contacts into
their applicable mailshot group according to their designation. I would like
to automate this process as I'm currently doing it manually.

I'm unfortunately not a programmer and need some help on this. Any ideas?

Thanks