Deleted and reposted as it’s own topic to avoid getting in the way of Razgon’s post
If you clean the source data up a bit such that each ‘block’ in the column has 4 entries (even if one of them is blank for the smaller towns) and there is consistent spacing between them, you could convert it into columns fairly easily with the ‘offset’ formula.
You could probably automate the data cleanup by doing a bit of VBA to delete rows if there are multiple consecutive blanks, and insert a row if there are only three consecutive non-blanks.
Thanks Profanicus - I don’t know the offset formual, but given how much work I do with cleaning up excel sheet we get from costumers, a course or two would perhaps be an idea - I just wasnt aware VB was such a good tool in excel.
You can pretty much program anything you can in regular VB. Even stuff you probably shouldn’t, since you can easily access stuff outside Excel.
It’s really handy - you can get a good headstart just by recording macros in Excel, and editing those in the VBA editor.
Here’s how to do Offset. Say this is your data in column A, starting at row 10:
A10: phone 1
A11: client 1
A12: address a 1
A13: address b 1
A14:
A15: phone 2
A16: client 2
A17: address a 2
A18: address b 2
A19:
A20: phone 3
A21: client 3
A22: address a 3
A23: address b 3
Then start the offset formula in cell B1:
=OFFSET($A$10,(ROW()-1)*5+COLUMN()-2,0)
and copy it to C1, D1, & E1. Then copy it down for as many entries as you need. It should transpose the data into columns.
If the data starts in a different cell, change the $A$10 bit of the formula to point to it.