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