Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for assigning distinct name to first instance in a list ofduplicate rows.
I have a spreadsheet of 3000 contact names
I am trying to assign these in the most "fair" fashion to account resps. My thought was to create a column called "account_rep" I then put account_rep_name1 account_rep_name2 account_rept_name3 In, colum1,Row 1 account_rep_name1 colum1,row 2 account_rep_name2 colum2,row 3 account_rept_name3 colum1,row 4 account_rep_name1 colum1,row 5 account_rep_name2 colum2,row 6 account_rept_name3 the pattern continues respectively through the list to row 3000. However, what has happened is that there may be more than one account rep assigned to an account. For example in the list I have. account_rep_name1 Suzie Queen ABC Company account_rep_name2 Suzie Queen ABC Company account_rept_name3 Suzie Queen ABC Company so, I want to assign unique rep to the account the first time his name hits the account down the list. What is the best way to do this? Thanks, Robert |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for assigning distinct name to first instance in a list of duplicate rows.
Ah, I see you multi-posted in .misc as well. Pl don't.
I might approach it this way: 1. Use advanced filter uniques to drill out the list of unique contacts in a new col 2. In a col next to the unique contacts, assign the reps using something like this in the top cell, copied down to the last row of unique contacts: =INDEX({"rep1";"rep2";"rep3"},MOD(ROWS($1:1)-1,3)+1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "NickNameGoesHere" wrote in message ... I have a spreadsheet of 3000 contact names I am trying to assign these in the most "fair" fashion to account resps. My thought was to create a column called "account_rep" I then put account_rep_name1 account_rep_name2 account_rept_name3 In, colum1,Row 1 account_rep_name1 colum1,row 2 account_rep_name2 colum2,row 3 account_rept_name3 colum1,row 4 account_rep_name1 colum1,row 5 account_rep_name2 colum2,row 6 account_rept_name3 the pattern continues respectively through the list to row 3000. However, what has happened is that there may be more than one account rep assigned to an account. For example in the list I have. account_rep_name1 Suzie Queen ABC Company account_rep_name2 Suzie Queen ABC Company account_rept_name3 Suzie Queen ABC Company so, I want to assign unique rep to the account the first time his name hits the account down the list. What is the best way to do this? Thanks, Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy to Clipboard - Distinct List | Excel Discussion (Misc queries) | |||
Distinct list formula | Excel Discussion (Misc queries) | |||
Get distinct rows from different worksheets into another worksheet | Excel Discussion (Misc queries) | |||
Sum Distinct Rows in PivotTable | Charts and Charting in Excel | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) |