Loop by Group Within Group

Prev: Password entry userform
Next: Redirecting ALT+1 keystrokes to run routine

From: fzl2007 on 26 Mar 2010 11:33

---

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

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

From: dan dungan on 26 Mar 2010 12:07

---

Will a pivot table work for you?

From: fzl2007 on 26 Mar 2010 12:10

---

On Mar 26, 11:07 am, dan dungan <stagerob...(a)yahoo.com> wrote:
> Will a pivot table work for you?


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

From: Bob Phillips on 26 Mar 2010 12:36

---

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

--

HTH

Bob

"fzl2007" <fzl2007(a)gmail.com> wrote in message
news:ccb28b6e-6c98-42e3-8cfd-64b354cc152a(a)k19g2000yqn.googlegroups.com...
> 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
>
> ...
> ...
> ...

From: Per Jessen on 26 Mar 2010 12:57

---

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("A2:D2").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

"fzl2007" <fzl2007(a)gmail.com> skrev i meddelelsen
news:ccb28b6e-6c98-42e3-8cfd-64b354cc152a(a)k19g2000yqn.googlegroups.com...
> 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
>
> ...
> ...
> ...

 |  Next  |  Last
Pages: 1 2
Prev: Password entry userform
Next: Redirecting ALT+1 keystrokes to run routine