HowdoI sort master list of volunteers by job?

Microsoft Excel 2000
January 27, 2011 at 17:00:59
Specs: Windows XP
Thank you for any help you can give me, I really appreciate it!
I have an excel file that is updated whenever we have a new volunteer sign up for our event. I need to organize the data so I can see at each area who is working, if they are part of a group, and their contact information.
So, currently list reads like

A
App_id
####1
####2
####etc

B
App_firsname
This
That
Whathaveyou

C
App_lastname
The
Same
Thing

Column J has phone numbers
Column I has email addresses
Column O has the job they request
Column Q tells me if they are ‘Individual’ or ‘Group’

So, I have headings in row 1, and I need to make sure that I don’t import duplicate people when I go to update.

I would really like to work this out soon, there are going to be hundreds of people I will have to contact to find specific shifts they want to work, but I need to make sure that this system is working first, and there are too many people to just leave it to autosort each time.

I hope I made sense, if I need to provide any other information please let me know. I really do appreciate any help
Thank You!


See More: HowdoI sort master list of volunteers by job?

Report •

#1
January 28, 2011 at 10:11:15
Your subject line says:

How do I sort master list of volunteers by job?

but the only thing you appear to ask for in your post is:

I need to make sure that I don’t import duplicate people when I go to update.

What exactly is it that you are looking for help with?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 28, 2011 at 10:39:09
Thank you for helping ^_^!
Sorry for not being clear; so I have a CSV file that gets frequently updated with one individual volunteer per row.
Using the job they volunteer for (Grand Parade, Information Booth, Ennichi, Daijayama) I want to copy the row to a tab (or create a new tab if there is not one for that category yet) so I have a current list of contact information, for each job
(*bonus props and much mahalo if you can get newly imported information to highlight for follow-up action*)
I tried making something of my own, but instead of only moving the new data to the job tab it would copy all the info and resulted in multiple duplicates :(

I just want to have a clean list so I can keep track of how many volunteers spaces we need to fill per job. (Later I will be using the lists to create a shift schedule.)

Again, thank you very much for your help in this~!


Report •

#3
January 31, 2011 at 12:50:44
I'm a little confused by your post.

A csv file in Excel can only support a single sheet, so I'm not sure how you would move a row to a "new tab".

If it were a "regular" Excel workbook (.xls, .xlsm, etc.) there are multiple way to get it done.

What do you really have?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 31, 2011 at 13:25:56
Thank you for getting back to me, I’m sorry what I want to do is so tricky to describe, this is kind of the process that is happening:

Someone goes to our website to sign up
Their name and info get automatically entered into a spreadsheet online (1 person per row)
On the site I have the option to ‘export csv’

I save the updated .csv file to my desktop, (I can open it and ‘save as’ .xls at this point)

From here I’m perplexed at what action to take next.
I want to be able to have:
a master list of all volunteers
and
separate lists that have volunteers grouped by job

This way as the festival gets closer; I can refer to the job and have all the contact information I need to contact the volunteers with.


Report •

#5
January 31, 2011 at 16:51:47
The code to do what you want is fairly simple once the sheet is set up with a column for the jobs. Basically the code would simply loop through the column of jobs and copy each row to the tab named for that job, or actually a tab of any name that you want a specific job (or jobs) to be copied to.

If you don't care if the "split workbook" gets recreated every time from the newest file you download, then it's pretty simple. You simply run the code against the downloaded file (saved as .xls) and create all the tabs and copy all the contact data.

If you are making changes to the split workbook and then only wanting to update it with new contacts, then it's a bit more complicated because:

1 - You have to get the new website data (which I assume comes with all the old website data every time you do the download) into the workbook.

2 - You have to check each entry to see if it already exists. Not a big problem, just some extra steps.

If that's the case, you'll need to do some manual work to get the data into the split workbook, like copy the "master sheet" with the newly downloaded data into the workbook and delete the old master list. Then the code could run against the new master list, checking and copying as required.

Does that sound like something that might work?

If so, what column will the "jobs" be in?

What do the job names look like? More specifically, do you want a tab for named for each job and will Excel accept them as tab names, i.e. no slashes or any other characters that Excel won't accept?

In other words, it's time for some details.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 31, 2011 at 17:12:31
Yes! This sounds right!
The jobs are listed in column O
The first row is labels, (v_jobs for column O) and the jobs look like: Information Desk, Daijayama, Ennichi, Grand Parade, and no odd characters to worry about.
Yes, a tab for each job would be ideal!

However, having to check for duplicate entries will be a problem, as there will be hundreds of names to go through. If there is a way to highlight duplicates, or something, that could help.

Thank you so much!
I can't tell you how much this will help :)


Report •

#7
January 31, 2011 at 19:06:26
re: "However, having to check for duplicate entries will be a problem, as there will be hundreds of names to go through. If there is a way to highlight duplicates, or something, that could help."

Checking for duplicates is not a problem for VBA since a simple Find routine can be written.

However, I'm still not sure what your plan is. Keep in mind that I can't see your project from where I'm sitting, so you have to be specific.

Are you planning to update the sheet with each download, just adding the new data or is it OK to recreate all the tabs each time since (I assume) each download will contain both the old and new data.

In other words, if Hopoelehua and Alekanekeloe sign up on Day 1 and you download the file, it will contain 2 names. If Kanae and Puakai sign up on Day 2, will the downloaded file contain 4 names or just the 2 new ones?

If we split the downloaded file into 2 sheets on Day 1, what are your plans for the file you download on Day 2? If it contains all 4 names, can we just split that file and toss the old one? If not, how are you planning to get the new data into the split workbook?

If we have to search for duplicates, I'll need to know what columns the names are in. Is there a column for first names and another for last names?

If Kawena Pukui signs up, could there be a different Kawena Pukui that signs up also? How do you want to handle that? Could Kawena Pukui sign up for 2 jobs? How do you want to handle that?

As a starting example, this code will create a tab for each unique value in Column O of Sheet 1 and copy Row 1 from Sheet 1 to each tab as Column Labels. It will then copy each row from Sheet 1, based on the value in Column O, to the corresponding tab.

How you want to handle everything that comes after that is where things could get complicated. You'll have to let me know.

Option Explicit
Sub JobTabs()
Dim wsSheet As Worksheet
Dim lastJob, jTab, nxtJrw As Integer
Dim jobName As String
'Determine Last Row in Jobs List
 lastJob = Sheets(1).Range("O" & Rows.Count).End(xlUp).Row
'Loop Through Jobs List, Create Sheet and
'Copy Row 1 (Labels) If Sheet Doesn't Exist
  For jTab = 2 To lastJob
   jobName = Sheets(1).Range("O" & jTab).Value
    On Error Resume Next
      Set wsSheet = Sheets(jobName)
    On Error GoTo 0
     If wsSheet Is Nothing Then
      Sheets.Add after:=Sheets(Sheets.Count)
       ActiveSheet.Name = jobName
       Sheets(1).Rows("1:1").Copy _
        Destination:=Sheets(jobName).Range("A1")
     End If
  Next
'Copy Rows To Next Open Row On Corresponding Job Tab
  For jTab = 2 To lastJob
   jobName = Sheets(1).Range("O" & jTab).Value
    nxtJrw = Sheets(jobName).Range("O" & Rows.Count).End(xlUp).Row + 1
       Range("A" & jTab).EntireRow.Copy _
        Destination:=Sheets(jobName).Range("A" & nxtJrw)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 1, 2011 at 11:23:22
OK, I will try to give you as much info as I can.

Thank you for your patience :)
I sincerely appreciate it!!!

After saving the .cvs file as a .xls I saved and ran the macro you wrote,
when I went to play it
the macro created a tab titled "Information Desk" filled the first row with
(A)app_id *(B)app_firstname *(C)app_lastname *(D)app_address *(E)app_city*(F) app_state *(G)app_zip *(H)app_emailaddress*(I)app_phone1 *(J)app_phone2 *(K)app_age*(L) app_gender*(M) app_english *(N) app_japanese* (O) v_jobs *(P)comments*(ETC.) ind_or_grp* coordinator_emailaddress *coordinator_group_total* coordinator_name *coordinator_phone1 *coordinator_phone2 *parent_address* parent_agree *parent_city* parent_emailaddress* parent_firstname *parent_lastname *parent_phone1* parent_phone2* parent_zip *school* school_other *coordinator_id*

all the categories went properly into the first row, and the first 2 people
to sign up for the Information Desk fill in, then macro created an error message of
"subscript out of range" and stopped :(

Are you planning to update the sheet with each download, 
just adding the new data or is it OK to recreate all the tabs 
each time since (I assume) each download will contain both the old and new data.

Recreating the tabs each time would be fine because,
you are correct, each download will contain both the old and new data.

If we have to search for duplicates, I'll need to know what 
columns the names are in. 
Is there a column for first names and another for last names? 

Yes, but, again, you are correct in that there may be people with
the same name or people signing up for multiple jobs

The first column contains "app_id " which is their registration number,
they look like this
app_id
11001
11002
11003
11004
11005
11006
11007
11008
11009
11010
...etc.
so it would be most advantageous to use this as the method to check for duplicates.
When they sign up for multiple jobs it will create
a new "app_id" for that person since they must repeat the sign-up process
for each job

In other words, if Hopoelehua and Alekanekeloe 
sign up on Day 1 and you download the file, it will contain 2 names. 
If Kanae and Puakai sign up on Day 2, will the downloaded file 
contain 4 names or just the 2 new ones?

I would need the new file to contain the 4 names total.

 If it contains all 4 names, can we just split that file 
and toss the old one? If not, how are you planning to get 
the new data into the split workbook?

We can absolutely toss the old one as long as the new one contains
all 4 names from day 1 and 2

thank you so much for all your help! you have been wonderful :)


Report •

#9
February 1, 2011 at 12:03:03
re: "so it would be most advantageous to use this as the method to check for duplicates"

Why are we searching for duplicates?

Where are we searching for duplicates?

What do you want to happen when you find a duplicate?

Try this version of the code to see if it eliminates the error. I am assuming that the number of entries in Column A equals the number of entries in Column O. I am using the number of entries in Column O to set the number of times the code loops through the "copying" section.

Option Explicit
Sub JobTabs()
Dim wsSheet As Worksheet
Dim lastJob, jTab, nxtJrw As Integer
Dim jobName As String
'Determine Last Row in Jobs List
 lastJob = Sheets(1).Range("O" & Rows.Count).End(xlUp).Row
'Loop Through Jobs List, Create Sheet and
'Copy Row 1 (Labels) If Sheet Doesn't Exist
  For jTab = 2 To lastJob
    Set wsSheet = Nothing
    jobName = Sheets(1).Range("O" & jTab).Value
    On Error Resume Next
      Set wsSheet = Sheets(jobName)
    On Error GoTo 0
     If wsSheet Is Nothing Then
      Sheets.Add after:=Sheets(Sheets.Count)
       ActiveSheet.Name = jobName
       Sheets(1).Rows("1:1").Copy _
        Destination:=Sheets(jobName).Range("A1")
     End If
  Next
'Copy Rows To Next Open Row On Corresponding Job Tab
  For jTab = 2 To lastJob
   jobName = Sheets(1).Range("O" & jTab).Value
    nxtJrw = Sheets(jobName).Range("O" & Rows.Count).End(xlUp).Row + 1
       Range("A" & jTab).EntireRow.Copy _
        Destination:=Sheets(jobName).Range("A" & nxtJrw)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 3, 2011 at 10:00:29
OH AWESOME!!!
it works great!
Thank you so much for your help :)

Report •

Ask Question