concatenate, shift columns, cleanup! Please offer advice!

S

steveinpdx

New Member
Joined
Sep 26, 2013
Messages
1
Hello Gurus

Have an excel listing of prospective clients done by another company, so I cannot ask for a new file. All data (name, address, city/state/zip, phone, email & website) are done for each store in rows, one after the other. Then that's repeated in 3 columns, essentially a customer 1 in column 1, customer 2 in column 2, customer 3 in column 3, then customer 4 returns to column 1 under customer 1! Crazy!

All the data is there, it's just unusable without massive cut & paste. How can I: take what is in varied # of rows (if there is no email or phone, then the next data field fills it, so NOT standard...) and pivot that into columns? Luckily the city/state zip, although in one row can be concatenated once all in 1 column. Getting to that point is the challenge. One other little nuance...the website URL is in a protected field, where the link to URL needs a click to travel to it.

I'd love to upload a visual of my desired before & after. if that's not possible, here's a paste of what I have,and below that what I want! Any & all help welcomed. And for the person who can solve this, some free sunglasses for your favorite college team from my company! :)

Current:
[TABLE="width: 540"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[/TR]
[TR]
[TD]AB Surfboard Repair[/TD]
[TD]Georges Water Sports[/TD]
[TD]S.C. Boardroom[/TD]
[/TR]
[TR]
[TD]14879 Chestnut St.[/TD]
[TD]1301 Montlimar Dr[/TD]
[TD]415 Avenida Pico[/TD]
[/TR]
[TR]
[TD]Westminster, CA 92683[/TD]
[TD]Mobile , AL[/TD]
[TD]San Clemente, CA 92672[/TD]
[/TR]
[TR]
[TD]Phone: (424) 221-1069[/TD]
[TD]Phone: 251-344-6666[/TD]
[TD]Phone: 949-366-0199[/TD]
[/TR]
[TR]
[TD]Email: absurfboardrepair@yahoo.com[/TD]
[TD]Email: enall53@bellsouth.com[/TD]
[TD]Safari Surf and Sport[/TD]
[/TR]
[TR]
[TD]Surf Shop Website[/TD]
[TD]Surf Shop Website[/TD]
[TD]20936 Devonshire st.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Chatsworth, CA 91311[/TD]
[/TR]
[TR]
[TD]ABC Surf[/TD]
[TD]Glenn Walton Custom Surfboards ~ VIVAPINA[/TD]
[TD]Phone: 818) 349-9283[/TD]
[/TR]
[TR]
[TD]2233 W. Balboa Blvd. Suite 110[/TD]
[TD]73 Edgewood Road[/TD]
[TD]Email: safarisurf@sbcglobal.net[/TD]
[/TR]
[TR]
[TD]Newport Beach, 92663[/TD]
[TD]West Islip, NY 11795[/TD]
[TD]Safari Town Surf Shop[/TD]
[/TR]
[TR]
[TD]Phone: 949-200-7598[/TD]
[TD]Phone: 631-943-7227[/TD]
[TD]3026 N.e. Hwy 101[/TD]
[/TR]
[TR]
[TD]Email: surf@abcsurfshop.com[/TD]
[TD]Email: vivapina@aol.com[/TD]
[TD]Lincoln city , OR 97367[/TD]
[/TR]
[TR]
[TD]Surf Shop Website[/TD]
[TD]Surf Shop Website[/TD]
[TD]Phone: 541-996-6335[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Email: safaritown@gmail.com[/TD]
[/TR]
[TR]
[TD]Action Surf Shop[/TD]
[TD]Glide Surf Co[/TD]
[TD]Surf Shop Website[/TD]
[/TR]
[TR]
[TD]Po Box 627[/TD]
[TD]3620 State Hwy 35 N[/TD]
[TD]Sakal Surfboards[/TD]
[/TR]
[TR]
[TD]Newport, NC 28570[/TD]
[TD]Normandy Beach, NJ 08739[/TD]
[TD]201 Main St.[/TD]
[/TR]
[TR]
[TD]Phone: 252-240-1818[/TD]
[TD]Phone: 732-250-6398[/TD]
[TD]Huntington Beach, CA 92649[/TD]
[/TR]
[TR]
[TD]Email: BobbyWebb@actionsurf.com[/TD]
[TD]Email: Glidesurfco@gmail.com[/TD]
[TD]Phone: (714) 536-0505[/TD]
[/TR]
[TR]
[TD]Surf Shop Website[/TD]
[TD]Surf Shop Website[/TD]
[TD]Email: info@sakalsurfboards.com[/TD]
[/TR]
</tbody>[/TABLE]


Desired

[TABLE="width: 1318"]
<tbody>[TR]
[TD]Store Name[/TD]
[TD]Address 1[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Phone[/TD]
[TD]Email[/TD]
[TD]Website[/TD]
[/TR]
[TR]
[TD]AB Surfboard Repair[/TD]
[TD]14879 Chestnut St.[/TD]
[TD]Westminster[/TD]
[TD]CA[/TD]
[TD]92683[/TD]
[TD](424) 221-1069[/TD]
[TD]absurfboardrepair@yahoo.com[/TD]
[TD]AB Surfboard Repair | "Excellent Repairs, Righteous Prices"[/TD]
[/TR]
[TR]
[TD]Georges Water Sports[/TD]
[TD]1301 Montlimar Dr[/TD]
[TD]Mobile[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]251-344-6666[/TD]
[TD]enall53@bellsouth.com[/TD]
[TD]Georges Water Sports, Mobile and Gulf Coast AL for Tide Water Boats, Bentley Pontoons, Mercury Outboards,Yamaha Outboards, Mercury outboard parts, Yamaha outboard parts, sales, service[/TD]
[/TR]
[TR]
[TD]S.C. Boardroom[/TD]
[TD]415 Avenida Pico[/TD]
[TD]San Clemente[/TD]
[TD]CA[/TD]
[TD]92672[/TD]
[TD]949-366-0199[/TD]
[TD][/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]Safari Surf and Sport[/TD]
[TD]20936 Devonshire st.[/TD]
[TD]Chatsworth[/TD]
[TD]CA[/TD]
[TD]91311[/TD]
[TD]818) 349-9283[/TD]
[TD]safarisurf@sbcglobal.net[/TD]
[TD]none[/TD]
[/TR]
[TR]
[TD]ABC Surf[/TD]
[TD]2233 W. Balboa Blvd. Suite 110[/TD]
[TD]Newport Beach[/TD]
[TD]CA[/TD]
[TD]92663[/TD]
[TD]949-200-7598[/TD]
[TD]surf@abcsurfshop.com[/TD]
[TD]ABC Surf[/TD]
[/TR]
[TR]
[TD]Glenn Walton Custom Surfboards ~ VIVAPINA[/TD]
[TD]73 Edgewood Road[/TD]
[TD]West Islip[/TD]
[TD]NY[/TD]
[TD]11795[/TD]
[TD]631-943-7227[/TD]
[TD]vivapina@aol.com[/TD]
[TD]Glenn Walton Surfboards[/TD]
[/TR]
[TR]
[TD]Safari Town Surf Shop[/TD]
[TD]3026 N.e. Hwy 101[/TD]
[TD]Lincoln city[/TD]
[TD]OR[/TD]
[TD]97367[/TD]
[TD]541-996-6335[/TD]
[TD]safaritown@gmail.com[/TD]
[TD]Surf & Skate Shop-GoPro Hero 3 Cameras, Xcel Wetsuits[/TD]
[/TR]
[TR]
[TD]Action Surf Shop[/TD]
[TD]Po Box 627[/TD]
[TD]Newport Beach[/TD]
[TD]NC[/TD]
[TD]28570[/TD]
[TD]252-240-1818[/TD]
[TD]BobbyWebb@actionsurf.com[/TD]
[TD]Action Surf - Home[/TD]
[/TR]
[TR]
[TD]Glide Surf Co[/TD]
[TD]3620 State Hwy 35 N[/TD]
[TD]Normandy Beach[/TD]
[TD]NJ[/TD]
[TD]08739[/TD]
[TD]732-250-6398[/TD]
[TD]Glidesurfco@gmail.com[/TD]
[TD]Glide Surf Co. | Purveyors of Alternative Surf Craft & Quality Goods[/TD]
[/TR]
[TR]
[TD]Sakal Surfboards[/TD]
[TD]201 Main St.[/TD]
[TD]Huntington Beach[/TD]
[TD]CA[/TD]
[TD]92649[/TD]
[TD](714) 536-0505[/TD]
[TD]info@sakalsurfboards.com[/TD]
[TD]none[/TD]
[/TR]
</tbody>[/TABLE]


thanks in advance!:)

Steve
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
As you work down each column is there *anything* to indicate where one customer ends and the next one starts? Maybe a blank cell in that single column, even though the other columns have data? If so then it can probably be done by formula and certainly by code
 
Upvote 0
Looking at your posting there seems to be a empty cell between records.
Working on one column ar a time put this onto another sheet. The formula in column B should be copied across to column H, then copy A:H down as far as required

Excel Workbook
ABCDEF
1Current:
2Column 1
3AB Surfboard Repair
414879 Chestnut St.
5Westminster, CA 92683
6Phone: (424) 221-1069
7Email: absurfboardrepair@yahoo.com
8Surf Shop Website
9
10ABC Surf
112233 W. Balboa Blvd. Suite 110
12Newport Beach, 92663
13Phone: 949-200-7598
14Email: surf@abcsurfshop.com
15Surf Shop Website
16
17Action Surf Shop
18Po Box 627
19Newport, NC 28570
20Phone: 252-240-1818
21Email: BobbyWebb@actionsurf.com
22Surf Shop Website
23
24new record0
250
26AB Surfboard Repair14879 Chestnut St.Westminster, CA 92683Phone: (424) 221-1069Email: absurfboardrepair@yahoo.comSurf Shop Website
27ABC Surf2233 W. Balboa Blvd. Suite 110Newport Beach, 92663Phone: 949-200-7598Email: surf@abcsurfshop.comSurf Shop Website
28Action Surf ShopPo Box 627Newport, NC 28570Phone: 252-240-1818Email: BobbyWebb@actionsurf.comSurf Shop Website
Sheet5
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top