Reformatting Data Challenge!

[jerickson]

I need some help changing the format of data I have copied off the web into a usable spreadsheet. It's a list of wineries and it's in a box-like format. I need to convert this format into columns. It has hundreds of wineries listed and there are 3 rows between each new block of data. I've attached sample data. Sheet 1 shows the data as it is now and Sheet 2 shows how I would like to see it. Essentially I want to convert the box-like original format into a columnar format.

There are a couple of notes on Sheet 2.
1. I don't need to convert all of the fields into the columnar format
2. If it's possible to break the city state zip field into 3 separate fields, that would be great as well.

Any suggestions or help creating a macro would be much appreciated.

Thanks!

[Marcol]

See if this helps as a possible starting template.

1/. Import your data tables to a spare sheet.

2/. Select Columns A:D and then Edit > Clear > Formats

3/. Insert a row at the top of the data and in B2 put some header text, say "Data"

4/. Apply the auto-filter to Columns A:D and filter "Data" for Blanks.
Select the resulting empty rows, in this case 7 to 59, the row numbers should be a different colour from the rest, in my case blue.
Delete these rows and remove the filter.

You should now have a cleaned table of data, with no blank rows.

5/. Select and Copy Columns A:D to the template sheet A1.

6/. To extend the result table select the last row with formulae (F10:O10) and drag down.

With a bit of luck no VBa required ...

We can eliminate any unwanted data later, but best, first we see these fields ...

* I have excluded some fields from Sheet 1 on purpose.