Convert a data in Rows to columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1788781%22%20slang%3D%22en-US%22%3EConvert%20a%20data%20in%20Rows%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788781%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20data%20list%20of%20user%20id%20and%20password%20of%20students%20in%20a%20single%20column%20with%20the%20user%20id%20of%20the%20student%20in%20one%20row%20and%20password%20below%20the%20user%20id.%20Now%2C%20I%20want%20to%20change%20the%20data%20as%20all%20user%20id's%20in%20one%20row%20and%20all%20passwords%20in%20another%20row.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1788781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788818%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20a%20data%20in%20Rows%20to%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788818%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F721016%22%20target%3D%22_blank%22%3E%40rahmanrjy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20B2%3A%3C%2FP%3E%0A%3CP%3E%3DINDEX(A%3AA%2C2*ROW()-2)%3C%2FP%3E%0A%3CP%3Eand%20in%20C2%3A%3C%2FP%3E%0A%3CP%3E%3DINDEX(A%3AA%2C2*ROW()-1)%3C%2FP%3E%0A%3CP%3ESelect%20B2%3AC2%2C%20then%20fill%20or%20copy%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have data list of user id and password of students in a single column with the user id of the student in one row and password below the user id. Now, I want to change the data as all user id's in one row and all passwords in another row. 

2 Replies
Highlighted

@rahmanpragati 

Enter the following formula in B2:

=INDEX(A:A,2*ROW()-2)

and in C2:

=INDEX(A:A,2*ROW()-1)

Select B2:C2, then fill or copy down.

Highlighted

@rahmanpragati 

 

Here is your example file with formula.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.