Loop by Group Within Group

[fzl2007]

Can you show me how to convert sheet A to sheet B format?

In sheet B, I want to give a space for every Sales Person and do not
repeat their id / names. I also will need to give a space for each
region within each sales person. I appreciate your help.

sheet A
Sales Person ID Sales Person Name customer type region
1100813 John a1 1
1100813 John a2 1
1100813 John a3 1
1100813 John a4 1
1100813 John a5 1
1100813 John b1 2
1100813 John b2 2
1100813 John b3 2
1100813 John c1 3
1100813 John c2 3
1100813 John d1 4
1100813 John d2 4
1100813 John d3 4
1100813 John d4 4
1102312 Amy a1 1
1102312 Amy a2 1
1102312 Amy a3 1
1102312 Amy a4 1
1102312 Amy a5 1
1102312 Amy b1 2
1102312 Amy b2 2
1102312 Amy b3 2
1102312 Amy c1 3
1102312 Amy c2 3
1102312 Amy d1 4
1102312 Amy d2 4
1102312 Amy d3 4
1102312 Amy d4 4
1102367 Todd a1 1
1102367 Todd a2 1
1102367 Todd a3 1
1102367 Todd a4 1
1102367 Todd a5 1
1102367 Todd b1 2
1102367 Todd b2 2
1102367 Todd b3 2
1102367 Todd c1 3
1102367 Todd c2 3
1102367 Todd d1 4
1102367 Todd d2 4
1102367 Todd d3 4
1102367 Todd d4 4



sheet B

Sales Person ID Sales Person Name customer type region
1100813 John a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

1102312 Amy a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

1102367 Todd a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

....
....
....

 

[dan dungan]

Will a pivot table work for you?

 

[fzl2007]

Will a pivot table work for you?

No. It doesn't do exactly what I describe.

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

Set sh = Worksheets("Sheet2")

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 3 Step -1

.Rows(i).Copy sh.Range("A" & i)
If .Cells(i, "A").Value = Cells(i - 1, "A").Value Then

sh.Range("A" & i).Resize(, 2).Value = ""
If .Cells(i, "D").Value <> Cells(i - 1, "D").Value Then

sh.Rows(i).Insert
End If
Else

sh.Rows(i).Insert
End If
Next i
.Rows(1).Resize(2).Copy sh.Range("A1")
End With

End Sub

 

[Per Jessen]

Hi

Thy this macro:

Sub aaa()
Dim TargetSh As Worksheet
Dim DestSh As Worksheet
Dim DestRow As Long
Dim TargetID As Long
Dim LastRow As Long
Dim TargetRegion As Long

Set TargetSh = Worksheets("Sheet1")
Set DestSh = Worksheets("Sheet2")
DestRow = 3
LastRow = TargetSh.Range("A1").End(xlDown).Row
TargetID = TargetSh.Range("A2").Value
TargetRegion = TargetSh.Range("D2").Value

TargetSh.Range("A22").Copy DestSh.Range("A2")
For r = 3 To LastRow
If TargetID = TargetSh.Cells(r, 1).Value Then
If TargetRegion = TargetSh.Cells(r, 4) Then
TargetSh.Cells(r, 3).Resize(1, 2).Copy DestSh.Range("C" &
DestRow)
DestRow = DestRow + 1
Else
DestRow = DestRow + 1
TargetSh.Cells(r, 3).Resize(1, 2).Copy DestSh.Range("C" &
DestRow)
DestRow = DestRow + 1
TargetRegion = TargetSh.Cells(r, 4)
End If
Else
DestRow = DestRow + 1
TargetSh.Cells(r, 1).Resize(1, 4).Copy DestSh.Range("A" & DestRow)
TargetID = TargetSh.Cells(r, 1)
TargetRegion = TargetSh.Cells(r, 4)
DestRow = DestRow + 1
End If
Next
End Sub

Regards,
Per

 

[Shane Devenshire]

Hi,

One can do this with a pivot table:

1. Insert a column in the data source between the customer and type columns
and give it a title, I will call it X.
2 Enter the formula =LEFT(C2,1) and fill it down. Assumption is that data
starts in A1 with one row of titles.
3. Insert the pivot table.
4. Add the Name, Customer, X and Type into the Row area, add the Region to
the Values (Data) area.
5. Right-click the Name field and choose Field Setting, set Subtotals to
None and click Layout, check Insert blank line after each item and click OK
twice.
6. Repeat step 5 on column X.
7. Right-click the Customer field and choose Field Setting, set Subtotals to
None, click OK.
8. High the X column or
8a. Select the entire pivot table and choose Copy, and then choose Edit,
Paste Special Values.
9a. Delete the X column.

You can record a macro to do this also.

 

[Shane Devenshire]

And here is a copy of the results

Name customer type Total
1100813 John a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

1102312 Amy a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

1102367 Todd a1 1
a2 1
a3 1
a4 1
a5 1

b1 2
b2 2
b3 2

c1 3
c2 3

d1 4
d2 4
d3 4
d4 4

I negelected to tell you to turn off column totals - Pivot Table, Table
Options...
This was all done in 2003 but you can do the same in 2007 or 2010.