It would have been relatively straightforward if each row in a block of data went into the next available column for a record. But taking records 1 & 5 as an example. How could Excel decide that with the first record the first item goes into column 3 but with the 5th record it goes into column 1?

Similarly with record 2, How could excel work out that the third item Franklin, TN 37064-2504 should go in column 6 and not column 5 which you show as blank.

Excel needs rules. It's not a human brain which is much more powerful and able to use fuzzy logic.