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
|
Similar Threads
