Simple in mosts eyes

[itjej195]

Hello all I am new to vb scripting and have a project that needs to get done soon. I already have a few things on my plate and was wondering if you can point me in the right direction.
I have a layout that looks like this:

Cust # Cust1F Cust1L Cust2F Cust2L Cust3F Cust43L
592147 steve jones jan doe john smith
233015 tammy stevens jake
412015 ed jones bob tim allen


... in this pattern till Cust5F and Cust5L. The data needs to be shortened 3 colums like below. So i need a vb that will copy info to like below to another sheet that if there is no info in Cust2F Cust3F Cust4F Cust5F that will not create an additional row.

Cust # Cust1F Cust1L
592147 steve jones
592147 jan doe
592147 john smith
233015 tammy stevens
233015 jake
412015 ed jones
412015 bob
412015 tim allen

any help would be appreciated sorry I am new at vb.

 

[SkipVought]

Hi,

Take a look at NORMALIZE Your Table using the PivotTable Wizard faq68-5287.

You'll have to do that for each grouping, 1, 2, 3

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[itjej195]

I am a bit more confused trying to get that normalization to work I keep getting odd items and formats in my sheet. Isnt there a way to do it with a vb script. That way I can start to understand (reverse engineer) how vb is looking at info and processing it.

 

[SkipVought]

You're absolutly correct.

copy the table 2 times for a total of 3 tables

In the FIRST table, delete 2 & 3

In the SECOND table delete 1 & 3

In the THIRD table delete 1 & 2

Now combine all three into ONE.

Marco record if need be.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[vladk]

itjej195,
This code works for me. I put your 5 pair names plus Cust # headers in Sheet1 in first row starting column A. Data goes strting row 2. Sheet2 gets results.

Private Sub CommandButton1_Click()
Dim lngFirstCustRow As Long
Dim lngLastCustRow As Long
Dim lngCustRow As Long
Dim intName As Integer
Dim oRange As Range
Dim intNextRow As Integer
Dim strCust As String

lngFirstCustRow = 2
lngLastCustRow = 4

Set oRange = Worksheets("Sheet2").Range("A2")
Worksheets("Sheet1").Range("A2").Select

For lngCustRow = lngFirstCustRow To lngLastCustRow
oRange.Value = Selection.Value 'first Cust #
strCust = oRange.Value
intNextRow = 0
With Worksheets("Sheet1")
For intName = 1 To 5

If Selection.Cells.Offset(0, 1).Value <> vbNullString Or Selection.Cells.Offset(0, 2).Value <> vbNullString Then
intNextRow = intNextRow + 1
oRange.Cells.Offset(0 + intNextRow - 1, 0).Value = strCust
oRange.Cells.Offset(0 + intNextRow - 1, 1).Value = Selection.Cells.Offset(0, 1).Value
oRange.Cells.Offset(0 + intNextRow - 1, 2).Value = Selection.Cells.Offset(0, 2).Value
End If
Selection.Cells.Offset(0, 2).Select
Next intName

End With

Set oRange = oRange.Cells.Offset(0 + intNextRow - 1, 0)
Selection.Cells.Offset(1, -10).Select
Next lngCustRow

End Sub

vladk