SOLVED

Transpose Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-3302352%22%20slang%3D%22en-US%22%3ETranspose%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302352%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20team%20I%20have%20my%20data%20as%20first%20photo%20but%20I%20want%20my%20data%20column%20wise%20like%20my%20second%26nbsp%3B%20photo.%20At%20the%20end%20of%20every%20address%20there%20is%20a%20blank%20cell.%20Transpose%20is%20not%20working%20for%20Multiple%20address.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22IMG-20220504-WA0002.jpg%22%20style%3D%22width%3A%20618px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369116i386A512E876DA561%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22IMG-20220504-WA0002.jpg%22%20alt%3D%22IMG-20220504-WA0002.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22IMG-20220504-WA0001.jpg%22%20style%3D%22width%3A%201280px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369117i5E301B974215301E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22IMG-20220504-WA0001.jpg%22%20alt%3D%22IMG-20220504-WA0001.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3302352%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3320529%22%20slang%3D%22en-US%22%3ERe%3A%20Transpose%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3320529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E..%20Thanks%20a%20lot..%20It%20worked..%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302375%22%20slang%3D%22en-US%22%3ERe%3A%20Transpose%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302375%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380769%22%20target%3D%22_blank%22%3E%40Skpani%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20that%20you%20want%20the%20%22transposed%22%20range%20to%20start%20in%20cell%20E5.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20that%20cell%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24A%3A%24A%2C6*(ROW(E5)-ROW(%24E%245))%2BCOLUMN(E5)-COLUMN(%24E%245)%2B5)%26amp%3B%22%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20start%20in%20another%20cell%2C%20change%20E5%20and%20%24E%245%20accordingly.%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20next%204%20cells%20to%20the%20right%2C%20then%20fill%20down.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1389.png%22%20style%3D%22width%3A%20727px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369127i0540B35202BAC97F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1389.png%22%20alt%3D%22S1389.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

Hi team I have my data as first photo but I want my data column wise like my second  photo. At the end of every address there is a blank cell. Transpose is not working for Multiple address. 

 

 

 

IMG-20220504-WA0002.jpg

IMG-20220504-WA0001.jpg

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@Skpani 

Let's say that you want the "transposed" range to start in cell E5.

Enter the following formula in that cell:

 

=INDEX($A:$A,6*(ROW(E5)-ROW($E$5))+COLUMN(E5)-COLUMN($E$5)+5)&""

 

If you want to start in another cell, change E5 and $E$5 accordingly.

Fill to the next 4 cells to the right, then fill down.

S1389.png

@Hans Vogelaar.. Thanks a lot.. It worked..