Results 1 to 8 of 8

Thread: Rearrange data from column arrangement to row arrangement

  1. #1

    Rearrange data from column arrangement to row arrangement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 adviseSamnple.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3
    thank you

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

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Can you elaborate on what you mean?


  6. #6
    =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

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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.


  8. #8
    thank you very much it worked perfect

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •