Excel 2007 : Transpose-Salesperson Saleperson

[Twista]

Hi,

I have a spreadsheet download from our company system and after I text delimit it, it comes into the following format, which does not work for me. I have over 9000 lines


Salesperson Saleperson Email Customer Name1 Customer Name2 Customer Name3 Customer Email1 Customer Email2 Customer Email3
Joe Joe@domain.com Harry Biggie Helen Harry@products.com Biggie@products.com Helen@products.com
Mary Mary@domain.com Chris Babara Wendy Chris@products.com Babara@products.com Wendy@products.com
Jane Jane@domain.com Mike Karen Janet Mike@products.com Karen@products.com Janet@products.com
John John@domain.com lennon Paul Caroline lennon@products.com Paul@products.com Caroline@products.com


I Need it to be transposed into this,

Salesperson Saleperson Email Customer Name1 Customer Email1
Joe Joe@domain.com Harry Harry@products.com
Joe Joe@domain.com Biggie Biggie@products.com
Joe Joe@domain.com Helen Helen@products.com
Mary Mary@domain.com Chris Chris@products.com
Mary Mary@domain.com Babara Babara@products.com
Mary Mary@domain.com Wendy Wendy@products.com
Jane Jane@domain.com Mike Mike@products.com
Jane Jane@domain.com Karen Karen@products.com
Jane Jane@domain.com Janet Janet@products.com
John John@domain.com lennon lennon@products.com
John John@domain.com Paul Paul@products.com
John John@domain.com Caroline Caroline@products.com

Can anyone please help me.

[JBeaucaire]

TO be exact, we need to see the data as you see it on your spreadsheet.

Click GO ADVANCED and use the paperclip icon to post up your workbook. Mockup you desired results on another sheet in the same workbook.

[Twista]

Hi

Cannot seem to get permission to upload company data. Will create a spreadsheet with similar problem and another sheet with desired result.

Thanks for the help.

[Twista]

Hi,

Here is the spreadsheet

[JBeaucaire]

Assuming your real data is just as clean and neat as the sample, this is actually a pretty simply copy/paste/sort job.

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[Twista]

Thanks, works perfectly. You guys are the greatest

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[Twista]

Hello Jerry,

Hiccup, as per my mock example I only gave you 3 customer names and 3 customer emails per saleperson.

In actual fact, it varies from salepersons. Some saleperson's can have up to 200 customers horizontally that need to be transposed.

Your macro is choosing the first thre customer and email address and assigning it to the saleperson / the saleperson is being assigned to the first three customers (on that row).

But this is my fault as I only showed you three customers. How can I modify this script to kinda check the entire row and transpose whatever is on that row and assign it the the salesperson name and email.

hope I managed to explain this properly.

[JBeaucaire]

Assuming your real data is just as clean and neat as the sample, this is actually a pretty simply copy/paste/sort job.

See, I suspected all was not as shown, it's simple to dumb down an example workbook so much we waste time.

So amend your workbook sample and show more data. Sounds like it isn't really that orderly, the first 3 columns being customer names and the next 3 matching emails.

Show me a better example of how bad the data actually is, represent the situation fully this time.