Hi All,

I have several rows of data which I need to compile into a summary type
format.

Source data

Index Unit Patch Folder No.
1 D0X D2X 1
2 D0X D2X 1
3 D0X D2X 1
4 D0X D2X 1
5 D0X D2X 1
6 D0X D2X 1
7 D0X D2X 1
8 D0X D2X 1
9 D0X D3X 1
10 D0X D3X 1
11 D0X D3X 1
12 D0X D5X 1
13 D0X D5X 1
14 D0X D6X 1
15 D0X D6X 1
16 D0X D6X 1
17 D0X D8X 1
18 D0X D8X 1
19 D0X D8X 1
20 D0X D8X 1
21 D0X D8X 1
22 D0X D8X 1
23 D0X D8X 1
24 D0X D8X 1
25 D0X D8X 1
26 D0X D8X 1
27 D0X D9X 1
28 D0X D9X 1
29 D0X D9X 1
30 D0X D9X 1
31 D0X D9X 2
32 D0X D9X 2
33 D0X D9X 2
34 D0X D9X 2
35 D0X D9X 2


What I need to do is match the Unit to the folder Number and return the min
and max index numbers, all the patches contained within that range and the
folder number with the Max folder number after that (eg. 1 of 6)

Output

Index Start Index End Unit Patch Folder No.
1 30 D0X D2X, D3X, D5X, D6X, D8X, D9X 1 OF 6
31 45 D0X D9X, DAX 2 OF 6
46 49 D0X DAX 3 OF 6
50 79 D0X DDX, DEX, DFX 4 OF 6
80 92 D0X DFX, DGX 5 OF 6
93 107 D0X DGX, DHX, DJX, DKX 6 OF 6


When the code has completed the output needs to be as shown above

I think that I need to use an array to do this but I have no idea where to
start. Can anyone help me please.

Any and all help would be greatly appreciated.

Regards

Mick

Sorry the data layout is not easy to understand

Source data

Index Unit Patch Folder No.
1 D0X D2X 1
2 D0X D2X 1
3 D0X D2X 1
4 D0X D2X 1
5 D0X D2X 1
6 D0X D2X 1
7 D0X D2X 1
8 D0X D2X 1
9 D0X D3X 1
10 D0X D3X 1
11 D0X D3X 1
12 D0X D5X 1
13 D0X D5X 1
14 D0X D6X 1
15 D0X D6X 1
16 D0X D6X 1
17 D0X D8X 1
18 D0X D8X 1
19 D0X D8X 1
20 D0X D8X 1
21 D0X D8X 1
22 D0X D8X 1
23 D0X D8X 1
24 D0X D8X 1
25 D0X D8X 1
26 D0X D8X 1
27 D0X D9X 1
28 D0X D9X 1
29 D0X D9X 1
30 D0X D9X 1
31 D0X D9X 2
32 D0X D9X 2
33 D0X D9X 2
34 D0X D9X 2
35 D0X D9X 2


What I need to do is match the Unit to the folder Number and return the min
and max index numbers, all the patches contained within that range and the
folder number with the Max folder number after that (eg. 1 of 6)

Output

Index Start Index End Unit Patch
Folder No.
1 30 D0X D2X, D3X, D5X, D6X, D8X,
D9X 1 OF 6
31 45 D0X D9X, DAX
2 OF 6
46 49 D0X DAX
3 OF 6
50 79 D0X DDX, DEX, DFX
4 OF 6
80 92 D0X DFX, DGX
5 OF 6
93 107 D0X DGX, DHX, DJX, DKX
6 OF 6

Any and all help would be greatly appreciated.

Regards

Mick

I did the macro witthout using an Array. I did it by putting a Z as the
number of folers and then going back and replacing the Z with the folder
count. The macro assumes the data is sorted like your example.

the code also depends on the patch being unique. th ecode searches a string
to see if the patch already exists. Problems could occur becasue two patches
look similar such as patch1 & patch10. Doing a search for patch1 will be
true for both patch1 & patch10. The results would be missing patches. I
don't think this will happen looking at you sample data.

Sub test()

With Sheets("Sheet1")
Patch = ""
Sh1RowCount = 2
Sh2RowCount = 1
Folder = 0
NewUnit = True
Do While .Range("A" & Sh1RowCount) < ""
If NewUnit = True Then
StartIndex = .Range("A" & Sh1RowCount)
Unit = .Range("B" & Sh1RowCount)
NewUnit = False
End If
'check if Patch already exists
If InStr(Patch, .Range("C" & Sh1RowCount)) = 0 Then
If Len(Patch) < 0 Then
Patch = Patch & ", " & .Range("C" & Sh1RowCount)
Else
Patch = .Range("C" & Sh1RowCount)
End If
End If
If .Range("D" & Sh1RowCount) < _
.Range("D" & (Sh1RowCount + 1)) Then

EndIndex = .Range("A" & Sh1RowCount)
Folder = .Range("D" & Sh1RowCount)
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = StartIndex
.Range("B" & Sh2RowCount) = EndIndex
.Range("C" & Sh2RowCount) = Patch
.Range("D" & Sh2RowCount) = Folder & " of Z"
End With
Sh2RowCount = Sh2RowCount + 1
NewUnit = True

End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With

'Now go back to sheet 2 and replace ? with folder

With Sheets("Sheet2")
.Range("D1:D" & (Sh2RowCount - 1)).Replace _
what:="Z", replacement:=Trim(Str(Folder))
End With
End Sub

"Symbiosis" wrote:

Hi All,

I have several rows of data which I need to compile into a summary type
format.

Source data

Index Unit Patch Folder No.
1 D0X D2X 1
2 D0X D2X 1
3 D0X D2X 1
4 D0X D2X 1
5 D0X D2X 1
6 D0X D2X 1
7 D0X D2X 1
8 D0X D2X 1
9 D0X D3X 1
10 D0X D3X 1
11 D0X D3X 1
12 D0X D5X 1
13 D0X D5X 1
14 D0X D6X 1
15 D0X D6X 1
16 D0X D6X 1
17 D0X D8X 1
18 D0X D8X 1
19 D0X D8X 1
20 D0X D8X 1
21 D0X D8X 1
22 D0X D8X 1
23 D0X D8X 1
24 D0X D8X 1
25 D0X D8X 1
26 D0X D8X 1
27 D0X D9X 1
28 D0X D9X 1
29 D0X D9X 1
30 D0X D9X 1
31 D0X D9X 2
32 D0X D9X 2
33 D0X D9X 2
34 D0X D9X 2
35 D0X D9X 2


What I need to do is match the Unit to the folder Number and return the min
and max index numbers, all the patches contained within that range and the
folder number with the Max folder number after that (eg. 1 of 6)

Output

Index Start Index End Unit Patch Folder No.
1 30 D0X D2X, D3X, D5X, D6X, D8X, D9X 1 OF 6
31 45 D0X D9X, DAX 2 OF 6
46 49 D0X DAX 3 OF 6
50 79 D0X DDX, DEX, DFX 4 OF 6
80 92 D0X DFX, DGX 5 OF 6
93 107 D0X DGX, DHX, DJX, DKX 6 OF 6


When the code has completed the output needs to be as shown above

I think that I need to use an array to do this but I have no idea where to
start. Can anyone help me please.

Any and all help would be greatly appreciated.

Regards

Mick