From vertical to horizental issue.

[Challebjoern]

Please Login or Register  to view this content.

I got about 1000 of theese, some are more empty cells between then above. I know how to sort/transpose them from verticaly to horizental but I want them to be lie this instead. See below.

Please Login or Register  to view this content.

And so on... Any easy solution my friends?

[shukla.ankur281190]

Assuming your data is in A column.

Try

C2=IF(COLUMNS($C$1:C1)>=4,"",INDEX($A:$A,COLUMNS($A1:A1)-1+(ROWS(A$1:A1)-1)*4+1)) and drag towards the cells and down.

Check the attached file.

[AliGW]

@Ankur - you have not provided the layout that the OP requested.

[shukla.ankur281190]

What does mean by OP>>

[AliGW]

OP = original poster.

This is what he (she) requested:

Please Login or Register  to view this content.

[Challebjoern]

Assuming your data is in A column.

Try

C2=IF(COLUMNS($C$1:C1)>=4,"",INDEX($A:$A,COLUMNS($A1:A1)-1+(ROWS(A$1:A1)-1)*4+1)) and drag towards the cells and down.

Check the attached file.

Looked awesome and seemed to work. But once I paste in my Colum A there it doesn't sort it like it did with your text.

Below I attached the document I'm trying to fix.

Carlberg.xlsx

[AliGW]

I think you are going to need a VBA solution for this. It might be worth editing the title of your original post to flag VBA so that the VBA specialists will see it and help, hopefully.

[shukla.ankur281190]

As I have looked at your data you need do some work on that first.

Try.

Select the whole A column then press CTRL+G you will get a window name Go to, click on special, now you will have window name go to special -> click on blanks and the ok. This step will select your whole blank cells now now go navigation tab and Home delete .

All the blank cells will be delete now only "." mentioned cells need to delete. Go to filter and select . and del them

try

In B1=IF(ROWS($A$1:A1)>=4,"",INDEX($A:$A,(COLUMNS($A1:A1)-1)*3+1+ROWS(A$1:A1)-1)) and drag towards the cell.

Check the attached file.

[Challebjoern]

I think you are going to need a VBA solution for this. It might be worth editing the title of your original post to flag VBA so that the VBA specialists will see it and help, hopefully.

If we look at the document now. What Shukla made worked perfectly for the first 3 rows in collum A. I just feel that it would be possible to make it continue do like that every third collumn down. See attached document again. I removed the empty cells and dots and so on. So every third cells are one "job".

From vertical to horizental issue v01.xlsx

[Challebjoern]

As I have looked at your data you need do some work on that first.

Try.

Select the whole A column then press CTRL+G you will get a window name Go to, click on special, now you will have window name go to special -> click on blanks and the ok. This step will select your whole blank cells now now go navigation tab and Home delete .

All the blank cells will be delete now only "." mentioned cells need to delete. Go to filter and select . and del them

try

In B1=IF(ROWS($A$1:A1)>=4,"",INDEX($A:$A,(COLUMNS($A1:A1)-1)*3+1+ROWS(A$1:A1)-1)) and drag towards the cell.

Check the attached file.

Haha, exactly like I thought. and it worked perfectly! +++++++ Thanks Shukla.!

[shukla.ankur281190]

Thanks for feedback and happy to help you.

Please mark tread as solved if your problem is solved.

Please add reputation by clicking star * icon your left corner on your screen on those post which helps you.