Table imported from a Web page, manipulated and now I would like to organize the columns

[andymarten]

Hi all,

Firstly apologies if there is a glaringly obvious answer to this, however I have searched the Web and not found an answer, so I am hoping you guys can help.

I am trying to organize a spreadsheet for a holiday and I have scraped data from the web in order to do this. The table initially only had 3 columns (Campsite Name, Campsite Address and Facilities). The 'facilities' data (such as 'Beach Nearby, Bar onsite, Shop onsite etc) all appeared in a single row with random spacing. I used TRIM, CLEAR and SUBSTITUTE to get rid of all spaces, including ascii 160, I then used TEXT to COLUMNS to put the different facilities into their own columns. The problem I now have is that all columns have multiple different facilities in them. I then put in column headings of all the different facilities available, and that is where I have ground to a halt. I cannot find a way to move the correct 'facilities' under the correct columns, keeping the right 'facilities' for the right campsite.

I hope that makes sense. I have attached the first 20 rows of this workbook, there are 113 in total and I am hoping to do several sheets in the workbook for the different regions of France.

Can anyone help me please?

take care

Andy

[FlameRetired]

Please find output in the attached starting in row 24.

The formula for the first two columns is straight forward. In A24 filled down and across column B

Please Login or Register  to view this content.

In C24 filled down and across until you get blanks

Please Login or Register  to view this content.

Is this what you are looking for?

[andymarten]

Hi Dave,

thank you very much for your help. Sadly it doesn't quite look like what I am hoping for.

Each row should populate the correct column, effectively putting a blank in the columns that do not apply, so that they match the headings in row 1. I am not sure if it is actually possible to 'match' the data and then move into the correct column, leaving a blank in the columns that do not apply.

I have cut and pasted the data to put into the correct columns to show an example of what I was hoping for.

Thank you again

Andy

[andymarten]

Dave, thank you, I appreciate your time in trying to help.

After looking more closely and understanding it a bit better, what you have provided is what I am looking for but it does not seem to capture all of the information.

Kind regards

Andy

[FlameRetired]

I am confused:

..... but it does not seem to capture all of the information.

This is too vague. Please provide specific sample(s) of what information it is not capturing. Perhaps include a new upload relevant to those points.

[andymarten]

Hi, sorry.....I can't do excel but I can confuse people!

I'll try and explain it better. When I used your formula, only a couple of the 'facilities' were populated in their correct cells (see Fig 1.) The ones highlighted in red populated correctly, but the ones in orange were missed.

Row 2 Camping Ar Kleguer has the following ‘facilities’ in the campsite:
Bicycle Hire on Site
Bar Nearby
Bar on Site
Seaside Beach Onsite
Fishing on Site
Golf Nearby
Childrens Play Area
Riding Nearby
Sailing Nearby
Pool on Site
Pets Allowed
Shop Nearby
Shop on Site
Takeaway Food
Wifi Free
Wifi Partial Site

As listed in from C2 to R2
I am looking to have these populated under the correct headings in Row 1 and where they do not have a ‘facility’ named in Row 1 I would like there to be a blank.
So for Camping Ar Kleguer only the following cells would be populated: C2, E2, F2, H2, K2, N2, O2, P2, Q2, T2, V2, AA2, AB2, AC2, AE2, AH2. (See Fig 2.)

Having looked at my example sheets, I could do with getting the headings in Row 1 in the correct order, which I think I did rectify in the attachment to my first reply.

Thank you for taking time to help Dave.

Take care

Andy

[FlameRetired]

There are extra spaces in your source data as well as the headers. You need to clean up the headers and the data in C2:AH5 before you can count on a formula to work.

[FlameRetired]

In the new attached I started with the original file from Post #1.

I cleaned up all the data and headers.

The output headers in row 23 is a copy/paste of the source headers.

In A24 filled down and across column B

Please Login or Register  to view this content.

In C24:AH42

Please Login or Register  to view this content.

The outputs all line up with their respective (and correct) headers.

Please let me know if this does what you want.

[andymarten]

Dave,

you are a hero, thank you very much.....it just took a little more understanding from me. If I'd been a little bit sharper I wouldn't have wasted so much of your time

thank you, off to use your formula and do a lot of Trim'ing

Andy

[FlameRetired]

Not a waste.
This is how we all learn. No exceptions.

Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.