Copy partial row to new sheet if cell not empty.

[Pugman]

I'm not exactly new to macros, but this problem has me stumped.

I have a file with Company, Country and numerous Employee columns. There are 1 to 50 employees per row. I need to copy the Company, Country and one unique Employee at a time to a new worksheet. The following picture should help in explaining:

There is always an entry in Employee_1.

Any help would be greatly appreciated.

 

[SkipVought]

Hi,

You can use this technique to normalize your data.

NORMALIZE Your Table using the PivotTable Wizard faq68-5287

You must concatenate the text in the first two columns for this to work.

 

[Pugman]

Thanks SkipVought,

I tried and tried, but being a complete noob to PivotTable was my problem. I got close, but no joy.

I did find a macro that came close enough to what I needed. I'll post it in case you're interested.

Sub NormalizeData()
Dim Rng As Range
Dim WS As Worksheet

On Error Resume Next
Set Rng = Application.InputBox(Prompt:="Select a range to normalize data" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0

If Rng Is Nothing Then
Else
    Application.ScreenUpdating = False
    Set WS = Sheets.Add
    I = 0
    For r = 1 To Rng.Rows.Count - 1
        For C = 1 To Rng.Columns.Count - 1
            WS.Range("A1").Offset(I, 0) = Rng.Offset(0, C).Value
            WS.Range("A1").Offset(I, 1) = Rng.Offset(r, 0).Value
            WS.Range("A1").Offset(I, 2) = Rng.Offset(r, C).Value
            I = I + 1
        Next C
    Next r
    WS.Range("A:C").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End If
End Sub

Thanks again.

Jim