I have problem i got excel and the information are saved like this way Ex: WDC M

Question

I have problem i got excel and the information are saved like this way

Ex:

        WDC

        Mike james

        001XXXXXXXXXX

      ADRESS : XXXXXXXXXX

        ZIP CODE

        NC

        Adam Mcbird

001XXXXXXXXXX

    ADRESS : XXXXXXXXX

        ZIP CODE

        TX

        Amma Miller

        001XXXXXXXXXXX

        ADRESS : XXXXXXXXXX

        ZIP CODE

and i want it to be arrange like this way :

and all information are different from the other and i have almost 5000 name in this excel and i have 3 other the same problem

please pleaase help me ASAP

this is my Email just in case : *** Email address is removed for privacy ***

Best regards!!!

STATE NAME PHONE ADRESS ZIP CODE WDC Mike James 001XXXXXXXXX XXXXXXXXXXX XXXXX NC Adam Mcbird 001XXXXXXXX XXXXXXX XXXXX TX Amma Miller 001XXXXXXXXXX XXXXXXXXXXXXXXXX XXXXX

Explanation / Answer

If every block of data consists of those 5 cells with one blank cell between each block, then we can do it using formulas.

Suppose the state for the first block is in Sheet1!A1
And we want the rearranged table in Sheet2 starting at A1

Then entering these 2 rows starting in Sheet2!A1

you could then select the second row and copy to the next 5000 rows

STATE NAME PHONE ADRESS ZIP CODE =OFFSET(Sheet1!$A$1,(ROW()-ROW(A2))*6,0) =OFFSET(Sheet1!$A$1,(ROW()-ROW(A2))*6+1,0) =OFFSET(Sheet1!$A$1,(ROW()-ROW(A2))*6+2,0) =OFFSET(Sheet1!$A$1,(ROW()-ROW(A2))*6+3,0) =OFFSET(Sheet1!$A$1,(ROW()-ROW(A2))*6+4,0)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---