Moving Repeating Data from Rows to Columns (Not Transpose)

[mifac]

Not sure if this is the appropriate place to post this, but I don't know what category the solution is under!
Is it possible to use an automatic function or formula to move 4 repeating data sets into separate columns?

What I have: (All in one column, separate rows)

(Last Name)
(First Name)
(Number)
(Type)

(Last Name 2)
(First Name 2)
(Number 2)
(Type 2)

Over and over for 40000+ rows.

What I need:
(Last Name) (First Name) (Number) (Type)
(Last Name 2) (First Name 2) (Number 2) (Type 2)
(etc.)

Essentially, I need to extract every 5th row's cell and put it in a different column, for each of the 4 items, 40,000 times, and I really don't want to do that.
Any type of help is appreciated!

[JBeaucaire]

Assuming:

1) Data is in column A starting at A1 (no header)
2) Every 5th cell is blank
3) D1:G1 are the headers for you new table (First, Last, Number, Type)

Put this formula in D2, copy to the right and down until you start seeing zeros:

=INDEX($A:$A,ROW($A1)*5-4+COLUMN(A$1)-1)

[José Augusto]

Hi
If your data is in A2:A40000 use in B2 and copy right and down

Please Login or Register  to view this content.

[popipipo]

Try it this way

[mifac]

Thanks, JBeaucaire!

The formula worked perfectly, no problems!

[Spektrat]

Hi there.
I have a follow-up question on this to fully understand it:
How would this look like if it was no empty row every 5th?
What in the function fixes that? I tried to do it, but got an error every time.
=INDEX($A:$A,ROW($A1)*5-4+COLUMN(A$1)-1)

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread