Moving data from a row to column based on dup

Hey everyone,

I have a question, I hope someone can answer. I have data I have to upload into a crm from a CSV file. It is contacts and companies, however the CRM will only take multiple contacts from a single company if they are on the same row. Right now the data is setup to have the contacts on seperate rows. Is there an easy way to do this using a *primary key* like company name to check for the duplicates and move the required data? I have attached an example of what I have.

From the example I would like to move all other contacts for Company A (or Company B) to the next available Firstname2, LastName2, etc, etc. Depending on how many contacts there are, they would simply move on the same line as the first version of the company name. I don't need column headers created as I can do that myself.

Hope someone has done this before and can help out... I have way too much data to do this by hand....

Originally Posted by jeffreybrown

Hi scooby103 & welcome to the Board,

Please take a few minutes and read the Forum Rules about cross posts.

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere.

It is prefered that you not cross-post at all, but if you do, please provied a link to the cross-post. http://www.mrexcel.com/forum/showthread.php?t=518374

Alright Jeffrey... will do. As they are not the same *forum* I only assumed that posting in multiple places would increase the likely hood of me getting a response. I will show the post to other sites going forward.

Now, with that being said, do you have a solution to the question?

Tks,

Originally Posted by jeffreybrown

So are you saying you would like Frank to move to D2, Johnson to E1, Tammy to F1, Tam to G1 and so on?

Essentially, but it needs to be based on the Company Name. So, if the same company exists grab the other contact information from the duplicate company and plug it into blank contact firstname2, etc. If the company doesn't exist don't do anything and move to next down the row.

Tks,

Originally Posted by jeffreybrown

I'll give it a go

Thanks Jeffrey - appreciated!!

Originally Posted by jeffreybrown

Here you go...sorry it took so long. I really stuggle when a macro involves the moving of cells, but I think this does it.

If you want to delete all of the blanks thereby leaving just the unique company names then remove the " ' " from the code below.

I duplicated your data on Sheet2 and Sheet3 if you want to run it without the delete and then with. Hope this is what you expected.

>> To run the macro from Excel, ALT + F8 Highlight macro and select Run

Please Login or Register  to view this content.

Hey Jeff,

Thanks! I'm trying to run the macro as you stated above but when I hit Alt F8 there is no macro to run. I'm using Excel 2010, could that be the issue?

How can I cut / paste your code above into Excel to run it?

Tks,

Originally Posted by jeffreybrown

Never mind

Hey Jeff,

It's the file on the posting Test_Data_Macro.xlsx

It has more rows / columns to show the other data necessary to be moved. Only non duplicate data needs to be moved, and then the other rows deleted (as you already created).

Originally Posted by jeffreybrown

I think creating more than four contacts could be possible, but before we move further I just need to confirm your desired outcome.

Could you adjust your sample data to contain two tabs. One with the original data (no extra columns) and then a tab with the final results in the format you desire?

Hey Jeff,

Again - thanks for your help, this is awesome.

I have attached the updated sheet with the 2 tabs. The first is the *raw* data and the 2nd would be the outcome I would like to see. Again, if it's possible to create more contact areas if a company should have more (I know one company I have has 23 contacts...) that would be awesome. If not, if we could have space for 5 that would be perfect.

Woops. Just re-read your request.

I have re-attached, but on the raw file I have removed the blank contact areas.

Please see attached.

Originally Posted by jeffreybrown

This seems to cover it.

Added a timer to the macro and tested with 3600 rows of data and it came out ok.

Note: Remember test out on test data before the real thing.

Hey Jeff,

Sorry for the late response, I was off for a bit.

Looks like it's 99% of the way there. I have given you a piece of the data. There are 2 spreadsheets. First has the test data (50 lines, multiple companies / employees) and the second has the outcome. It is moving the data but it appears it is not moving it to the outcome tab, only overwriting the data tab. Also, some of it doesn't appear to be going into the right columns (Contact 3 might have a phone number).

If you had the solution to these last few things you would be saving me an amazing amount of time on a weekly basis.

Thanks for all your hard work on this till now.

Cheers,