Advance Transpose Question in Open Office/Google Docs

[Bigjay45]

I have a sheet with values:

cat | cat description | cat.jpeg
rat | rat description | rat.jpeg
dog| dog description| dog.jpeg

How do i get it to show like this on the speed sheet:

cat
cat description
cat.jpeg
rat
rat description
rat.jpeg
dog
dog description
dog.jpeg

Thank you for your help.

 

[Joe4]

Re: Advance Transpose Question

Welcome to the Board!

Are the values originally in one cell, separated by pipe symbols, or do they currently span three columns?

 

[Bigjay45]

Re: Advance Transpose Question

Welcome to the Board!

Are the values originally in one cell, separated by pipe symbols, or do they currently span three columns?

Hello.

span across 3 columns

Thank you

 

[Joe4]

Re: Advance Transpose Question

Sorry one more question I forgot to ask:
Do you want it to overwrite the current data, or put the results on a new sheet?

 

[sandy666]

Re: Advance Transpose Question

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]cat[/td][td=bgcolor:#DDEBF7]cat description[/td][td=bgcolor:#DDEBF7]cat.jpeg[/td][td][/td][td=bgcolor:#E2EFDA]cat[/td][/tr]

[tr=bgcolor:#FFFFFF][td]rat[/td][td]rat description[/td][td]rat.jpeg[/td][td][/td][td]cat description[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]dog[/td][td=bgcolor:#DDEBF7]dog description[/td][td=bgcolor:#DDEBF7]dog.jpeg[/td][td][/td][td=bgcolor:#E2EFDA]cat.jpeg[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]rat[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]rat description[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]rat.jpeg[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]dog[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]dog description[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]dog.jpeg[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Transposed Table" = Table.ToColumns(Table.Transpose(Source)),
    #"Converted to Table" = Table.FromList(#"Transposed Table", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")
in
    #"Expanded Column1"[/SIZE]

 

[Bigjay45]

Re: Advance Transpose Question

Sorry one more question I forgot to ask:
Do you want it to overwrite the current data, or put the results on a new sheet?

the results can show just like sandy666 posted in the thread.

 

[Joe4]

Re: Advance Transpose Question

If you really only are have three columns, assuming that your data starts in cell A1, place this formula anywhere in row 1 and copy down as far as you need:

=OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))

 

[Bigjay45]

Re: Advance Transpose Question

If you really only are have three columns, assuming that your data starts in cell A1, place this formula anywhere in row 1 and copy down as far as you need:

=OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))

thanks for the formula. I got a 508 error. Im using open office. But the formula should work even if it is open office.

 

[Bigjay45]

Re: Advance Transpose Question

If you really only are have three columns, assuming that your data starts in cell A1, place this formula anywhere in row 1 and copy down as far as you need:

=OFFSET($A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))

thank you it worked in goolge docs

 

[Joe4]

Re: Advance Transpose Question

Im using open office

Please be sure to mention this in future questions. While there are similarities between Excel, Open Office, and Google Docs, they are NOT the same program, and there are differences. So it is important that anyone wishing to help understand exactly what environment you are trying to solve it in.

Also, I have moved the question to the "General Excel Discussion & Other Questions" forum. That is the forum that you want to post any questions that are not truly Excel.