Rearrange data from column arrangement to row arrangement

Hi all,

I have a small list of people with up to 10 phone numbers to load in a automated dialer. Currently the file has a name (client) with up to 10 phones per client but i need to convert it to a list with an individual client per phone number, meaning they want the client to be repeated 10 times with only one phone number next to it.. basically to divide it to an individual client per phone number.

tried doing it with a VLOOKUP and also an IF THEN IF. but i cant figure it out.

any adviseView attachment Samnple.xlsx

Assuming the data is in A2:M20 of Sheet1, then in sheet2, A2 you can use this formula to get the client numbers repeated 10 times each:

=IF(ROWS($A$2:$A2)>COUNTA(Sheet1!$A$2:$A$20)*10,"",IFERROR(INDEX(Sheet1!$A$2:$A$20,MOD(INT((ROW()-ROW(Sheet1!$A$2))/10),COUNTA(Sheet1!$A$2:$A$20))+1),""))

copied down as far as you need.

To get the associated phone numbers, in B2 enter:

=IF(A2="","",INDEX(Sheet1!$D$2:$M$20,MATCH(A2,Sheet1!$A$2:$A$20,0),COUNTIF(A$2:A2,A2)))

copied down same distance.

You can you VLOOKUP or INDEX/MATCH to get the corresponding names if desired.

thank you

is it possible to index multiple columns in the formula. to add the rest of the data?

Can you elaborate on what you mean?

=IF(ROWS($A$2:$A2)>COUNTA(Sheet1!$A$2:$A$461)*13,"",IFERROR(INDEX(Sheet1!$A$2:$A$461,MOD(INT((ROW()-ROW(Sheet1!$A$2))/13),COUNTA(Sheet1!$A$2:$A$461))+1),""))

I have data from column A:F the above formula allows me to just do it on A1:A461... i try to drag the formula all the way to column F451 but doesnt allow me.. any thoughts

The formula is meant to work on one column at a time.

If you want to go from A2:A461 to B2:B461 to C2:C461, etc, then just remove the $ from before all the A's in the formula and copy across.

thank you very much it worked perfect