Copy data to the next row until the values in another row are repeated

[jilkay]

I'm creating an import file for some test data. I have a list of certificates that are required for a particular job - typically 8 or 10 certificates for each position. I need to create an import file that contains a row for each certificate. The row includes the name of the certificate holder.


I have a list of 100 test users in a spreadsheet and the list of required certificates. I've been manually copying the name of the first test user beside the set of required certificates, then repeating the process on subsequent rows with a new name. This is a lot of copy and paste. Is there a way to pick a random name from the list of test users and repeat it until the list of required certificates comes around again? This is how the data should look:

[pre]

George   Harris    Certificate 1
George   Harris    Certificate 2
George   Harris    Certificate 3
Angel    Garcia    Certificate 1
Angel    Garcia    Certificate 2
Angel    Garcia    Certificate 3
Susan    Dennis    Certificate 1
Susan    Dennis    Certificate 2
Susan    Dennis    Certificate 3

[/pre]
Is there a way to do this in an Excel formula? Thanks.


- Jim

 

[Faseeh]

Hi jilkay,


Welcome to the forums & Please see this file:


http://dl.dropbox.com/u/60644346/Repeat.xlsx


If you search the forum properly you will find ample examples regarding your question.


Faseeh

 

[Luke M]

With a defined name or CertList for all of your required docs, formula for column 1 and 2:

=OFFSET(A$1,INT((ROW($A1)-1)/ROWS(CertList))+1,)


Formula to get Certificate Name:

=INDEX(CertList,MOD(ROW($A1)-1,ROWS(CertList))+1)

Copy all 3 formulas as needed downward to generate your list. Note that I assumed your original list of Names is in col A and B. If not, just need to change the first argument of the OFFSET formula to be the cell right above beginning of list.

 

[jilkay]

Faseeh, this worked very well. Thank you for the prompt response!


I've used your formulas in my spreadsheet and it does pretty much what I want it to do. https://dl.dropbox.com/u/46514639/2013-03-05%20ID%20Card%20Data%20%28Test%29.xlsx


There are two things I'd like to improve and would appreciate any suggestions you or others may have:


1. Is it possible to pick the name (First_name, Last_name) randomly from the list of test users, while ensuring there are no duplicates?


2. Currently the formulas have to be manually modified to suit the number of certificates required for a particular test case. Is there a way to add or remove one or more certificates in the "Certificates" tab without modifying the formulas on Sheet 1?


Thanks again.