Iterations of duplicates

[drayneal]

Hi gurus, I've spent 2 days looking for anything similar and have yet to find it. I have a download of a change log in SAP. I need to find the first and last instance of a change to get the beginning number and the end number. To do this I'm trying to insert a sequential number to identify the duplicates for the same stock number. I've used several of the formulas I've come across but none totally work. I originally used an IF(B1=B2,F1+1,"") but the last instance comes out null. This is part of a string of VBA macros that takes text and turns it into a table and this is the last piece. Please help, thanks.

<table x:str="" style="border-collapse: collapse; width: 619px; height: 144px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 59pt;" width="79"> <col style="width: 53pt;" width="70"> <col style="width: 260pt;" width="346"> <col style="width: 44pt;" width="59"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 59pt;" height="17" width="79">
</td> <td style="width: 260pt;" width="346">
</td><td class="xl106" style="width: 44pt;" width="59">
</td> <td class="xl106" style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl107" style="height: 12.75pt;" x:num="39653" align="right" height="17">
</td> <td>
</td> <td class="xl106" x:num="">
</td> <td class="xl106" x:num="">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl107" style="height: 12.75pt;" x:num="39738" align="right" height="17">
</td> <td>
</td> <td class="xl106" x:num="">
</td> <td class="xl106" x:num="">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl107" style="height: 12.75pt;" x:num="39486" align="right" height="17">
</td> <td>
</td> <td class="xl106" x:num="">
</td> <td class="xl106" x:num="">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl107" style="height: 12.75pt;" x:num="39521" align="right" height="17">
</td> <td>
</td> <td class="xl106" x:num="">
</td> <td class="xl106" x:num="">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl107" style="height: 12.75pt;" x:num="39639" align="right" height="17">
</td> <td>
</td> <td class="xl106" x:num="">
</td> <td class="xl106" x:num="">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl107" style="height: 12.75pt;" x:num="39639" align="right" height="17">
</td> <td>
</td> <td class="xl106" x:num="">
</td> <td class="xl106" x:num="">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl107" style="height: 12.75pt;" x:num="39638" align="right" height="17">
</td> <td>
</td> <td class="xl106" x:num="">
</td> <td class="xl106" x:num="">
</td> </tr> </tbody></table>

 

[jbeaucaire]

Something like this?

Excel Workbook
	A	B	C	D	E	F
1	AAA			ABC	First Row	2
2	ABC	1			Last Row	12
3	DEF
4	GHI
5	JKL
6	ABC	2
7	MNO
8	PQR
9	ABC	3
10	STU
11	VWX
12	ABC	4
13	YZ1
Sheet2

Of course, you can create a macro version of the same approach, yes?

 

[drayneal]

Thanks for the quick reply. You have the right idea its just that I can't use a reference table. Here is what I would be looking for:

CHANGE
SKU COUNT OLD ROP NEW ROP
AAA ___1 ______7 ______24
ABC ___1 ______5 _______7
ABC ___2 ______7 _______10
ABC ___3 ______10______ 15
DEF ___1 ______2 _______4
GHI ___1 ______10_______2
JKL ___1 _______3 _______8

I sort the list first. What I'm trying to do is retrieve the first and last ROP (Reorder Point) for the SKU. I've tried array formulas but missed the mark.

Result would be:
SKU OLD ROP NEW ROP
AAA ___7______ 24
ABC ___5 ______15
DEF ___2 _______4
GHI ___10 ______2
JKL ____3 ______8

 

[bosco_yip]

CHANGE
A _____B ______C______D
SKU COUNT OLD ROP NEW ROP
AAA ___1 ______7 _______24
ABC ___1 ______5 _______7
ABC ___2 ______7 _______10
ABC ___3 ______10______ 15
DEF ___1 ______2 _______ 4
GHI ___1 ______10_______2
JKL ___ 1 ______3 _______ 8

F_____G______ H
SKU OLD ROP NEW ROP
AAA ___7______ 24
ABC ___5 ______15
DEF ___2 _______4
GHI ___10 ______2
JKL ____3 ______ 8

1] Assume your old data at A1:D8

2] New data at F1:H8

3] G2, formula fill down :

=IF($F2="","",INDEX(C$2:C$1000,MATCH($F2,$A$2:$A$1000,0)))

4] H2, formula fill down :

=IF($F2="","",LOOKUP(2,1/(A$2:A$8=F2),D$2:D$8))

Regards
Bosco

 

[MickG]

Try this:-
Results start Column "F".

[COLOR="Navy"]Sub[/COLOR] MG15Jul46
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Q, V, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value, Array(Dn.Offset(, 2), Dn.Offset(, 3))
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.Value)
            Q(0) = IIf(Dn.Offset(, 2) < Q(0), Dn.Offset(, 2), Q(0))
            Q(1) = IIf(Dn.Offset(, 3) > Q(1), Dn.Offset(, 3), Q(1))
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] V [COLOR="Navy"]In[/COLOR] .keys
        c = c + 1
        Cells(c, "F") = V
        Cells(c, "G") = .Item(V)(0)
        Cells(c, "H") = .Item(V)(1)
    [COLOR="Navy"]Next[/COLOR] V
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[drayneal]

Thanks Mick, this is my first VBA project and you've offered some very sophisticated code, at least to me. The returns are inconsistent though. Sometimes I get the right combination but mostly not. There doesn't seem to be a pattern to the results. I'm on my work computer which I can't download any of the html editors to but when I get home I'll load a sample. Btw, and I should have said, the first column actually has dates in it based on when the change was made. I deleted it to run your code however, but it might make for a better reference. I will continue to try to see what changes I can make to your code. Thanks again.

 

[drayneal]

Okay, here is the data and result from the test set. Below it is a sample of the actual data. Don't know why the XXX.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Material	Duplicate	Old ROP	New ROP		Material	Old ROP	New ROP
2	2040	0	2,000	1,500		2040	XXX	1500
3	2141	0	5,000	XXX		2141	5000	3000
4	2143	XXX	500	0		2143	500	0
5	2272	XXX	XXX	8		XXX	XXX	8
6	2308	0	3	2		2308	3	2
7	27243	XXX	5	2		27243	2	6
8	27243	1	2	XXX		27473	5	2
9	27473	0	XXX	2		125701	1	10
10	125701	0	XXX	10		126003	6	4
11	126003	0	6	4		129291	10	0
12	129291	XXX	10	0		XXX	12000	16000
13	19101243	1	17,175	12,000				XXX
14	19101243	0	12,000	16,000
Wharton (2)

Excel Workbook
	A	B	C	D	E
1	Date	Material	Duplicate	Old ROP	New ROP
2	9/2/2008	2040	0	2,000	1,500
3	8/12/2008	2141	0	5,000	3,000
4	3/24/2008	2143	0	500	0
5	3/9/2008	2272	0	4	8
6	8/12/2008	2308	0	3	2
7	2/6/2008	27243	1	5	2
8	2/6/2008	27243	1	2	6
9	2/6/2008	27473	0	5	2
10	10/9/2008	125701	0	1	10
11	5/5/2008	126003	0	6	4
12	1/29/2008	129291	0	10	0
13	1/15/2008	19101243	1	17,175	12,000
14	3/11/2008	19101243	0	12,000	16,000
Wharton (2)

 

[MickG]

Hi, Try this:-
Run the code below based on your original data ( Dates in Column "A") the results starting in column "F" are shown Below.
If this give you the results you want and you need to place them on another sheet or elsewhere let me know.

[COLOR="RoyalBlue"][B]Column(F) [/B][/COLOR] [COLOR="RoyalBlue"][B]Column(G) [/B][/COLOR] [COLOR="RoyalBlue"][B]Column(H) [/B][/COLOR]
Material   Old ROP    New ROP   
2040       2000       1500      
2141       5000       3000      
2143       500        0         
2272       4          8         
2308       3          2         
27243      2          6         
27473      5          2         
125701     1          10        
126003     6          4         
129291     10         0         
19101243   12000      16000

[COLOR="Navy"]Sub[/COLOR] MG16Jul34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Q, V, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value, Array(Dn.Offset(, 2), Dn.Offset(, 3))
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.Value)
            Q(0) = IIf(Dn.Offset(, 2) < Q(0), Dn.Offset(, 2), Q(0))
            Q(1) = IIf(Dn.Offset(, 3) > Q(1), Dn.Offset(, 3), Q(1))
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] V [COLOR="Navy"]In[/COLOR] .keys
        c = c + 1
        Cells(c, "F") = V
        Cells(c, "G") = .Item(V)(0)
        Cells(c, "H") = .Item(V)(1)
    [COLOR="Navy"]Next[/COLOR] V
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[drayneal]

Thanks bosco, I had a "duh" moment when I realized that I had to pull the values as single occurrences before pasting the formula. Worked great and I'll code it to do that and paste special the formulas and then delete the old columns.

Thanks to Mick as well, I'd be interested in getting a copy of your code with some comments on what its doing at each step if you don't mind. I've deconstructed some but my novice VBA skills let me down.

D_Ray