Need help setting up a workflow based off of last name (alphabet, sort)

18 views
Skip to first unread message

Joseph Flemming

unread,
Aug 13, 2019, 10:57:56 AM8/13/19
to NV Autocrat Add-on
I usually set up workflows for our office based off of categories that are assigned to certain case managers and I use a Vlookup to make sure the correct person gets assigned and that becomes the trigger for mail merges. I want to try something new with four case worker that will each handle a workload based off of a portion of the Alphabet based off of last name.

Person 1 gets A-E, Person 2 F-L, Person 3 M-S  and Person 4 T-Z. So I would create a column that would look to the sheet with the case managers, and their assigned Alphabet (last name) and their name would be filled in and the letter would go to them. Anyone have an idea how this could be set up? 

Joseph Schmidt

unread,
Aug 13, 2019, 11:15:25 AM8/13/19
to nv-autocr...@googlegroups.com
Since there are only 26 letters in the alphabet, why not have a table with 26 entries and two columns.  Letter and Person.  Vlookup on letter to find the person.

If you had a more complicated problem with a much larger list, I would consider using the option that tells Vlookup that the table is sorted.  With that option, you only need the breakpoints and not each entry.



On Tue, Aug 13, 2019 at 10:57 AM Joseph Flemming <jofl...@kean.edu> wrote:
I usually set up workflows for our office based off of categories that are assigned to certain case managers and I use a Vlookup to make sure the correct person gets assigned and that becomes the trigger for mail merges. I want to try something new with four case worker that will each handle a workload based off of a portion of the Alphabet based off of last name.

Person 1 gets A-E, Person 2 F-L, Person 3 M-S  and Person 4 T-Z. So I would create a column that would look to the sheet with the case managers, and their assigned Alphabet (last name) and their name would be filled in and the letter would go to them. Anyone have an idea how this could be set up? 

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/25179be5-c4d4-4c7b-a61f-269ed8a82201%40googlegroups.com.

Joseph Flemming

unread,
Aug 13, 2019, 11:28:11 AM8/13/19
to NV Autocrat Add-on
Mapping out the 26 letters is actually a easy solution, I should have been more concise, I will only be looking for the first letter of the last name to new entries.
Example:
John Smith fills out form, Heather is assigned  cases with the letters S, so On Form Response 1 sheet John Smith's name is submitted, and in column Case Manager it does a Vlookup searching for Heather on the Case Manager sheet (sheet 2). My question is how can I isolate just the first letter of the last name field. How would I express that?


On Tuesday, August 13, 2019 at 11:15:25 AM UTC-4, Joseph Schmidt wrote:
Since there are only 26 letters in the alphabet, why not have a table with 26 entries and two columns.  Letter and Person.  Vlookup on letter to find the person.

If you had a more complicated problem with a much larger list, I would consider using the option that tells Vlookup that the table is sorted.  With that option, you only need the breakpoints and not each entry.



On Tue, Aug 13, 2019 at 10:57 AM Joseph Flemming <jofl...@kean.edu> wrote:
I usually set up workflows for our office based off of categories that are assigned to certain case managers and I use a Vlookup to make sure the correct person gets assigned and that becomes the trigger for mail merges. I want to try something new with four case worker that will each handle a workload based off of a portion of the Alphabet based off of last name.

Person 1 gets A-E, Person 2 F-L, Person 3 M-S  and Person 4 T-Z. So I would create a column that would look to the sheet with the case managers, and their assigned Alphabet (last name) and their name would be filled in and the letter would go to them. Anyone have an idea how this could be set up? 

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

Chris Hobbs

unread,
Aug 13, 2019, 12:34:27 PM8/13/19
to nv-autocr...@googlegroups.com
Assuming the last name is stored by itself in cell A2, and the lookup is defined in Sheet2!A:B:

=vlookup(left(A2,1),Sheet2!A:B,2,FALSE)

If the first and last name are stored together in a cell (A2 stores "John Smith") it gets more complex. For most cases you could probably get away with this:

=vlookup(mid(A2,find(" ",A2)+1,1),Sheet2!A:B,2,FALSE)

This would break if the submitter did something clever like entering "John D. Smith", which would return "D". Better to ask for last name separately if you have the choice.

Chris

To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/d7e6afe8-8876-4bcf-9350-2c1d6716b47d%40googlegroups.com.


--
Chris Hobbs
Dublin Unified School District
Director of Fiscal Services
Education That Inspires Lifelong Learning 

Joseph Schmidt

unread,
Aug 13, 2019, 12:55:15 PM8/13/19
to nv-autocr...@googlegroups.com
Use the Left function.

=arrayformula(if(row(A:A)=1,"person",vlookup(left(A:A,1),Sheet1!A2:B4,2,false)))  in row one will search for the first letter in column A.

I had to see if the Left function worked well with arrayformula and vlookup.  I like to put arrayformula in row one and to also remove any empty rows.

I'm sorry I missed the problem on the first request.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/d7e6afe8-8876-4bcf-9350-2c1d6716b47d%40googlegroups.com.

Joseph Flemming

unread,
Aug 13, 2019, 1:05:05 PM8/13/19
to NV Autocrat Add-on
Next question, can I use this in Row 2, as Row one is the Headers and I have all my formulas in row 2 for FormMule, Autocrat, Copy Down (I love NV can't you tell!!) to do their work.


On Tuesday, August 13, 2019 at 12:55:15 PM UTC-4, Joseph Schmidt wrote:
Use the Left function.

=arrayformula(if(row(A:A)=1,"person",vlookup(left(A:A,1),Sheet1!A2:B4,2,false)))  in row one will search for the first letter in column A.

I had to see if the Left function worked well with arrayformula and vlookup.  I like to put arrayformula in row one and to also remove any empty rows.

I'm sorry I missed the problem on the first request.
 


On Tue, Aug 13, 2019 at 11:28 AM Joseph Flemming <jofl...@kean.edu> wrote:
Mapping out the 26 letters is actually a easy solution, I should have been more concise, I will only be looking for the first letter of the last name to new entries.
Example:
John Smith fills out form, Heather is assigned  cases with the letters S, so On Form Response 1 sheet John Smith's name is submitted, and in column Case Manager it does a Vlookup searching for Heather on the Case Manager sheet (sheet 2). My question is how can I isolate just the first letter of the last name field. How would I express that?

On Tuesday, August 13, 2019 at 11:15:25 AM UTC-4, Joseph Schmidt wrote:
Since there are only 26 letters in the alphabet, why not have a table with 26 entries and two columns.  Letter and Person.  Vlookup on letter to find the person.

If you had a more complicated problem with a much larger list, I would consider using the option that tells Vlookup that the table is sorted.  With that option, you only need the breakpoints and not each entry.



On Tue, Aug 13, 2019 at 10:57 AM Joseph Flemming <jofl...@kean.edu> wrote:
I usually set up workflows for our office based off of categories that are assigned to certain case managers and I use a Vlookup to make sure the correct person gets assigned and that becomes the trigger for mail merges. I want to try something new with four case worker that will each handle a workload based off of a portion of the Alphabet based off of last name.

Person 1 gets A-E, Person 2 F-L, Person 3 M-S  and Person 4 T-Z. So I would create a column that would look to the sheet with the case managers, and their assigned Alphabet (last name) and their name would be filled in and the letter would go to them. Anyone have an idea how this could be set up? 

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

Joseph Schmidt

unread,
Aug 13, 2019, 1:13:20 PM8/13/19
to nv-autocr...@googlegroups.com
I prefer to not use CopyDown unless I have a need for the Replace feature or I can't get the arrayformula to work.

=vlookup(left(A:A,1),Sheet1!A$2:B$4,2,false)  would be the formula without arrayformula.

I like NV and the folks who ask questions at the forums.  
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/a8434351-b564-454b-ac87-c916f22020c0%40googlegroups.com.

Joseph Flemming

unread,
Aug 13, 2019, 3:13:08 PM8/13/19
to NV Autocrat Add-on
Thanks Joe! This helps a lot. I'm going to have to look into Arrays.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add-on+unsub...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages