Moving data from a row to column based on dup

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

Sub MoveData()
    Dim LR As Long
    Dim i As Long
    Dim j As Long: j = 4
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 3 To LR
        If Cells(i - 1, 1) <> Cells(i - 2, 1) Then
            Cells(i, 2).Resize(, 2).Copy Cells(i - 1, j)
            Cells(i + 1, 2).Resize(, 2).Copy Cells(i - 1, j + 2)
        End If
    Next i
    'Deletes all blank rows which do not have names leaving just one unique company name
    'Range("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlShiftUp
End Sub

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.