Formula for assigning distinct name to first instance in a list of duplicate rows.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NickNameGoesHere - 14 Mar 2008 18:32 GMT

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 to this Message

Max - 16 Mar 2008 09:01 GMT

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)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I have a spreadsheet of 3000 contact names
> I am trying to assign these in the most "fair" fashion to account
[quoted text clipped - 33 lines]
> Thanks,
> Robert

Reply to this Message