Assign list of names to a sheet of customers

[russellcarless]

Hi All

I have a list of 2000 customers and i want to assign a name of a person to contact them from a list of 49 people I have on another tab. I would like to evenly provide the 49 people a set of customers to call from the 2000. Ideally I would like to assign a name from the 49 to each entry in the 2000 list.

 

[Joe4]

Welcome to the Board!

Let's say that you have two sheets, named "Customers" and "Contacts"

On the "Customers" sheet, you have the name of all your customers. The first row is a header/title row, and your data begins on row 2. Column A is the name of your customer, and column B is where you want to list the Contact.

On the "Contacts" sheet, you have the name of all your contacts. The first row is a header/title row, and your data begins on row 2. Column A is the name of your Contact.

In order to populate column B on the Customer sheet with the "Contact" name, place this formula in cell B2 and copy down for all your Customer rows:

=OFFSET(Contacts!$A$2,MOD((ROW()-2),49),0)

This will go through and list all your Contacts in order, and when it gets to the last one (in row 50), it will repeat the pattern.

A simple example/preview. Here is what the Contacts sheets looks like for the first bunch of rows:

And here is what the Customer sheet looks like for the first bunch of rows, with our formula in column B:

As you get down to the end of the first 49 records, you can see the pattern repeat, like this:

 

[russellcarless]

Welcome to the Board!

Let's say that you have two sheets, named "Customers" and "Contacts"

On the "Customers" sheet, you have the name of all your customers. The first row is a header/title row, and your data begins on row 2. Column A is the name of your customer, and column B is where you want to list the Contact.

On the "Contacts" sheet, you have the name of all your contacts. The first row is a header/title row, and your data begins on row 2. Column A is the name of your Contact.

In order to populate column B on the Customer sheet with the "Contact" name, place this formula in cell B2 and copy down for all your Customer rows:

=OFFSET(Contacts!$A$2,MOD((ROW()-2),49),0)

This will go through and list all your Contacts in order, and when it gets to the last one (in row 50), it will repeat the pattern.

A simple example/preview. Here is what the Contacts sheets looks like for the first bunch of rows:
View attachment 120406

And here is what the Customer sheet looks like for the first bunch of rows, with our formula in column B:
View attachment 120407

As you get down to the end of the first 49 records, you can see the pattern repeat, like this:
View attachment 120408

Hey Joe, superb, thanks for your help and speed to reply too. Worked perfectly!

 

[Joe4]

You are welcome.
Glad I was able to help!