Help reformatting block data into standard columns

I have a workbook with 26 worksheets full of address information in block format in 2 columns. Column A has the labels and column B has the data, looking something like the below:

Column A Column B
1.Name Acme Corp
Address 123 Main St
Phone # 555.867.5309

2.Name Widgets, Etc...
Address 1600 Pennsylvania Ave
Phone# 555.555.1212

I have about 100 addresses on each worksheet in the above format. I'd like to get all this information onto one page with column headers for name, address, phone, etc...

I can get the names into a list fairly easily using vlookup, because they have unique identifiers, 1.name, 2.name, 3.name, etc...but the rest of the labels in column 1 are not unique. Any thoughts on how to approach reformatting this workbook?

Thanks,
Dylan

I can write a quick macro to do it, are those your only three fields? If you post ALL the needed matching fields this would be a quick macro.

Is field 1 really 1.Name, then 2.Name, 3.Name, etc.?

As i said this is a pretty quick macro. You implied the data was in a set form, and your post hinted there might be a blank space between each set of 3 rows making up a data set. This macro assumes the data starts on row ONE on each sheet.

Please Login or Register  to view this content.

Create a blank sheet called "Master", put the Name, Address, Phone headers in row 1, then run this macro.

If there is NOT a blank row between each set of data, then change the RED part to "Step 3"

JBeaucaire, thank you very much for your prompt response. I was unfortunately a bit incomplete in my original post in an attempt to simplify what I was trying to do. The data is mostly in a set form, but all fields are not available for all companies, and when they are not there the row is omitted. So the first company might be 12 rows worth of data, and the second company will have 10 rows of data. Also, the address is spread out on 3,4,or 5 rows, but only the first address row will have a label, so it will look like this

Column A Column B
Address 1234 1st St
New York, NY 10001
USA

I don't understand macros very well, but I think that what yours is doing is assuming that there would always be just the 3 pieces of data?

Edited to upload sample file

And your sample layout of what you want your master sheet to be? Layout the exact columnar format as I will create a macro to fill it in based on your design.

You can add a second sheet to the one you uploaded if you wish...name it something like Contacts.

This should do it. I removed the one column that collated nothing but dashes, so there are 7 columns total now.

Please Login or Register  to view this content.

I had never used the InStr() function myself, so your issue and the mildly varying nature of your data and match strings made that the only way to make it work.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].



(Also, use the "scales" icon in our posts to leave Reputation Feedback, it is appreciated)