Iterations of duplicates

drayneal

drayneal

New Member
Joined
Feb 4, 2008
Messages
10
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>
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Something like this?

Excel Workbook
ABCDEF
1AAA ABCFirst Row2
2ABC1Last Row12
3DEF
4GHI
5JKL
6ABC2
7MNO
8PQR
9ABC3
10STU
11VWX
12ABC4
13YZ1
Sheet2


Of course, you can create a macro version of the same approach, yes?
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
Try this:-
Results start Column "F".
Code: 
[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
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGH
1MaterialDuplicateOld ROPNew ROPMaterialOld ROPNew ROP
2204002,0001,5002040XXX1500
3214105,000XXX214150003000
42143XXX500021435000
52272XXXXXX8XXXXXX8
62308032230832
727243XXX522724326
82724312XXX2747352
9274730XXX2125701110
101257010XXX1012600364
11126003064129291100
12129291XXX100XXX1200016000
1319101243117,17512,000XXX
1419101243012,00016,000
Wharton (2)


Excel Workbook
ABCDE
1DateMaterialDuplicateOld ROPNew ROP
29/2/2008204002,0001,500
38/12/2008214105,0003,000
43/24/2008214305000
53/9/20082272048
68/12/20082308032
72/6/200827243152
82/6/200827243126
92/6/200827473052
1010/9/20081257010110
115/5/2008126003064
121/29/20081292910100
131/15/200819101243117,17512,000
143/11/200819101243012,00016,000
Wharton (2)
 
Upvote 0
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.
Code: 
[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
Code: 
[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
 
Upvote 0
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

:biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
740
smozgur
smozgur
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
M
  • Question Question
Drop down select
Replies
1
Views
2K
madu
M
A
  • Question Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A

Forum statistics

Threads
1,225,371
Messages
6,184,585
Members
453,244
Latest member
Todd Luet

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top