How to Organize Scraped Data

[anartey]

Hello Friends,

I have recently scraped data from several webpages into individual tabs in excel. Unfortunately the data is messy in the individual work sheets in work book. I would like to sort or create a mechanism to pick and organize information from each of the individual worksheets. Hope you know of a work around this problem.

[dflak]

It would help if you could attach a sample workbook. We have no idea how messy "messy" is.

[anartey]

Oh thanks dflak ! Here's my data. I want a way to categorize the data automatically.

[dflak]

Well, the good news is that the data seems to be in the same format on all the sheets.

What would you like the final result to look like? Can you provide a sample? Do you want a single sheet for each data sheet or do you want them consolidated into one sheet?

You will probably have to set up a table that says something like: put this cell in the source to this place on the target sheet.

[anartey]

Oh you're fast ! I would love to see projects from the different work sheets as rows and information in respective worksheets in columns.

[dflak]

Give me a sample of what you want one of these sheets to look like.

[anartey]

I've color coded the matching information on both work sheets.

[dflak]

I don't see any color coding - all I see is the original data.

[anartey]

My apologies. It's attached now.

[dflak]

This wasn't as easy as I thought it would be. The pages do not have all the same data in exactly the same places. I had to take it in two parts. The first 5 columns of data weren't too bad. I could search for a key word in the source column and parse out the value associated with the keyword and put it in the appropriate cell. The table in the tables tab sets that up.

The other three columns are keyed off finding the key string "Vintage Year." I loop through the number of years and get the data on those rows.

Finally, I have a clean up part of the code to fill in the data duplicated in the first 5 columns.

You had a hidden total column. I eliminated this column. If you need this number, then you can get it using a formula or better still, a pivot table. The data on the demand tab is perfect for a pivot table.

I am not sure I got all the mappings correct. I took some guesses.

[anartey]

Wow ! You're a genius !

[dflak]

1% inspiration, 99% perspiration - At least you got me to look up the find command

[anartey]

Hello dflak,

One more favour, i'd like to include a couple more sections to the demand tab. I've highlighted sections in yellow in tab "1". Can you help ? Attached.

Regards,

[anartey]

Actually, i figured it out. So no need to solve my recent request. Thanks for the great comments with VBA dflak !