Conditional, moving cells

Okay, I'm not good at excel but my boss wants me to figure out a problem for him.

So, I have a spreadsheet. Its about 48000 records

Most of the records are duplicates, but only because they have multiple dependents and multiple jobs.

So basically its like this. I'm using commas to seperate cells.

SSN, Name, Employer, Dependent SSN
1245, Jane, Walmart, 4321
1245, Jane, Walmart, 1234
1245, Jane, Walmart, 3214
1246, John, Costco, 1122
1246, John, HomeDepot, 1122

Like that so same person but employer or Dependent SSN changes
We want to take Employers/Dependent and add it to the previous line.
so it would look like
SSN, Name, Emp1, Emp2, Emp3, Dep1, Dep2, Dep3
1245, Jane, Walmart, 4321, 1234, 3214
1246, John, Costco, HomeDepot, 1122

To make things more complicated we have information like employer address that needs to be moved with the Employer name, and Dependent Relationship that needs to be pulled with the Dep SSN.

Thanks in advance-- Josh

Hi MiddleMonster. Welcome to the forum.

Rather than pick at this a little at a time, you should upload a sample data sheet that shows all the hurdles that need to be overcome, all at once. Use a real sheet and shorten it to a 100 rows or so and change the names to "sanitize" it.

Then mockup exactly what the results should be based on the sample data so we can compare directly.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

This is doable and I'll look at it of course. But take a note from an impartial 3rd party...your "Original" is SO EASY to read...the "How I want it to be" is a complete pain to read.

I wouldn't wish the new format on anyone else.

Ok, here's a macro that will reformat your sheet:

Option Explicit

Sub ReformatData()
'JBeaucaire  (11/16/2009)
Dim LR As Long, i As Long
Application.ScreenUpdating = False

If Range("A1") <> "Firm EFIN" Then
    MsgBox "Please bring up the data sheet before activating the macro."
    Exit Sub
End If

Range([A1], [A1].SpecialCells(xlLastCell)).Sort Key1:=Range("C2"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
Columns("G:H").Insert Shift:=xlToRight
Columns("J:K").Insert Shift:=xlToRight
Columns("M:N").Insert Shift:=xlToRight
Columns("P:Q").Insert Shift:=xlToRight

LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = LR To 3 Step -1
        If Cells(i, "C") = Cells(i - 1, "C") Then
            If Application.WorksheetFunction.CountIf(Range(Cells(i, "F"), Cells(i, "H")), Cells(i - 1, "F")) = 0 Then
                Range(Cells(i, "F"), Cells(i, "M")).Copy
                Cells(i - 1, "G").PasteSpecial xlPasteValues, SkipBlanks:=True
                Rows(i).Delete xlShiftUp
            Else
                Rows(i - 1).Delete xlShiftUp
            End If
        End If
    Next i

Range("G1,J1,M1,P1").FormulaR1C1 = "=RC[-1]&"" 2"""
Range("H1,K1,N1,Q1").FormulaR1C1 = "=RC[-2]&"" 3"""
Rows(1).Value = Rows(1).Value
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

===========
How 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 your sheet

The macro is installed and ready to use.
==========

Press Alt-F8 and select the ReformatData macro from the popup window.

Aviatecar,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.