VBA Find Last Point

I

ivandgreat

Board Regular
Joined
Jun 20, 2012
Messages
95
Dears,

I have a table with Start and End, i would like to have a vba that will look up the Last value based on col Start and End.


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A001[/TD]
[TD]A002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A004[/TD]
[TD]A005[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[/TR]
</tbody>[/TABLE]

Output table,

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]m[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A001[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[TD]A003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A004[/TD]
[TD]A005[/TD]
[TD]A007[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[TD]A007[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[/TR]
</tbody>[/TABLE]


br,
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Maybe this !!
Results in column "D"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG01May26
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] oRes        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: Dic.Item(Dn.Value) = Dn.Offset(, 1): [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 1)
        Txt = Dn.Value
        [COLOR="Navy"]Do[/COLOR] Until Not Dic.exists(Txt)
            oRes = Dic.Item(Txt)
            Txt = oRes
        [COLOR="Navy"]Loop[/COLOR]
            Dn.Offset(, 1) = Txt
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Maybe this !!
Results in column "D"
Code: 
[COLOR=Navy]Sub[/COLOR] MG01May26
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dic         [COLOR=Navy]As[/COLOR] Object
[COLOR=Navy]Dim[/COLOR] oRes        [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] Txt         [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    [COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng: Dic.Item(Dn.Value) = Dn.Offset(, 1): [COLOR=Navy]Next[/COLOR]
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Offset(, 1)
        Txt = Dn.Value
        [COLOR=Navy]Do[/COLOR] Until Not Dic.exists(Txt)
            oRes = Dic.Item(Txt)
            Txt = oRes
        [COLOR=Navy]Loop[/COLOR]
            Dn.Offset(, 1) = Txt
    [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Hi Mick,

What should i add to make the table as per below, list down all rows from the value it Start to the last value it Ends. Then add col (StartPt) from where it was part of.

The source table below,
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A001[/TD]
[TD]A002[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A004[/TD]
[TD]A005[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[/TR]
</tbody>[/TABLE]

Output Table
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]StartPt[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A001[/TD]
[TD]A002[/TD]
[TD]A001[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[TD]A001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[TD]A002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A004[/TD]
[TD]A005[/TD]
[TD]A004[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[TD]A004[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[TD]A004[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[TD]A005[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[TD]A005[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[TD]A006[/TD]
[/TR]
</tbody>[/TABLE]


br,
ivan
 
Upvote 0
Try this:-
Results start "D1"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG10May56
[COLOR="Navy"]Dim[/COLOR] rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] oRes        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] txt         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRay()
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] p           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] st          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ed          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lp          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sTpT        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
        Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng: Dic.Item(Dn.Value) = Dn.Offset(, 1): [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] n = 2 To rng.Count + 1
       sTpT = Range("B" & n)
       txt = Range("C" & n).Value
        p = c
        c = c + 1
        ReDim Preserve nRay(1 To 4, 1 To c)
        nRay(1, c) = c
        nRay(2, c) = Range("B" & n)
        nRay(3, c) = txt
        nRay(4, c) = sTpT
        
        [COLOR="Navy"]Do[/COLOR] Until Not Dic.Exists(txt)
            oRes = Dic.Item(txt)
            c = c + 1
             ReDim Preserve nRay(1 To 4, 1 To c)
            nRay(1, c) = c
            nRay(2, c) = txt
            nRay(3, c) = oRes
            nRay(4, c) = sTpT
            txt = oRes
            n = n + 1
        [COLOR="Navy"]Loop[/COLOR]
    
     
     [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] c - p > 1
        st = p + 1: Ed = c
        p = c
        [COLOR="Navy"]For[/COLOR] Lp = st To Ed - 1
            c = c + 1
            [COLOR="Navy"]If[/COLOR] Lp = st [COLOR="Navy"]Then[/COLOR] sTpT = nRay(3, Lp)
            ReDim Preserve nRay(1 To 4, 1 To c)
            nRay(1, c) = c
            nRay(2, c) = nRay(3, Lp)
            nRay(3, c) = nRay(3, Lp + 1)
            nRay(4, c) = sTpT
        [COLOR="Navy"]Next[/COLOR] Lp
     [COLOR="Navy"]Loop[/COLOR]
    
[COLOR="Navy"]Next[/COLOR] n
Range("D1").Resize(, 4) = Array("Item", "Start", "End", "StartPt")
Range("D2").Resize(c, 4) = Application.Transpose(nRay
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks for the code Mick.

I tried it when Col B is sorted, but when it isn't sorted it gives different results which some data is duplicated which shouldn't be anymore since it was already created.

br,
 
Upvote 0
I think you result is a line short at the bottom.
This is my logic :-
Where do you differ ??
Results
[TABLE="width: 340"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 6997" width=197><TBODY>[TR]
[TD="class: xl23, width: 64, bgcolor: transparent"]Item[/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"]Start[/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"]End[/TD]
[TD="class: xl23, width: 64, bgcolor: transparent"]StartPt[/TD]
[TD="class: xl23, width: 197, bgcolor: transparent"]Where the lines are from !! [/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: fuchsia, align: right"]1[/TD]
[TD="class: xl24, bgcolor: fuchsia"]A001[/TD]
[TD="class: xl24, bgcolor: fuchsia"]A002[/TD]
[TD="class: xl24, bgcolor: fuchsia"]A001[/TD]
[TD="class: xl23, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: fuchsia, align: right"]2[/TD]
[TD="class: xl24, bgcolor: fuchsia"]A002[/TD]
[TD="class: xl24, bgcolor: fuchsia"]A003[/TD]
[TD="class: xl24, bgcolor: fuchsia"]A001[/TD]
[TD="class: xl23, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl25, bgcolor: #ff99cc, align: right"]3[/TD]
[TD="class: xl25, bgcolor: #ff99cc"]A002[/TD]
[TD="class: xl25, bgcolor: #ff99cc"]A003[/TD]
[TD="class: xl25, bgcolor: #ff99cc"]A002[/TD]
[TD="class: xl25, bgcolor: #ff99cc"]1 Line from 2 lines above[/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: lime, align: right"]4[/TD]
[TD="class: xl26, bgcolor: lime"]A004[/TD]
[TD="class: xl26, bgcolor: lime"]A005[/TD]
[TD="class: xl26, bgcolor: lime"]A004[/TD]
[TD="class: xl23, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: lime, align: right"]5[/TD]
[TD="class: xl26, bgcolor: lime"]A005[/TD]
[TD="class: xl26, bgcolor: lime"]A006[/TD]
[TD="class: xl26, bgcolor: lime"]A004[/TD]
[TD="class: xl23, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: lime, align: right"]6[/TD]
[TD="class: xl26, bgcolor: lime"]A006[/TD]
[TD="class: xl26, bgcolor: lime"]A007[/TD]
[TD="class: xl26, bgcolor: lime"]A004[/TD]
[TD="class: xl23, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl27, bgcolor: #ccffcc, align: right"]7[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]A005[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]A006[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]A005[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]2 lines from 3 lines above[/TD]
[/TR]
[TR]
[TD="class: xl27, bgcolor: #ccffcc, align: right"]8[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]A006[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]A007[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]A005[/TD]
[TD="class: xl27, bgcolor: #ccffcc"]2 lines from 3 lines above[/TD]
[/TR]
[TR]
[TD="class: xl28, bgcolor: #ccffff, align: right"]9[/TD]
[TD="class: xl28, bgcolor: #ccffff"]A006[/TD]
[TD="class: xl28, bgcolor: #ccffff"]A007[/TD]
[TD="class: xl28, bgcolor: #ccffff"]A006[/TD]
[TD="class: xl28, bgcolor: #ccffff"]1 line from 2 lines above[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Hi MickG,

Yes we have the same output, but when i have unsorted table,

unsorted in col B,
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A001[/TD]
[TD]A002[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A004[/TD]
[TD]A05[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[/TR]
</tbody>[/TABLE]

And output below,

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]StartPt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[TD]A002[/TD]
[TD]Dupl@item4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A001[/TD]
[TD]A002[/TD]
[TD]A001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[TD]A001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A002[/TD]
[TD]A003[/TD]
[TD]A002[/TD]
[TD]Dupl@item1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]A004[/TD]
[TD]A005[/TD]
[TD]A004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[TD]A004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[TD]A004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A005[/TD]
[TD]A006[/TD]
[TD]A005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[TD]A005[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]A006[/TD]
[TD]A007[/TD]
[TD]A006[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Also, I have a table for 500 rows which are sorted but it seems some part also duplicated as per above table.

Can i send you the sample file. Thanks a lot.
 
Upvote 0
The logic for my results is basically from looking at your before and after Data.
Question:- Apart from producing some duplicates is my logic correct ???
If so, I can always delete any duplicates found, after running the first bit of code.
Would that do ???
If you want to send the file Try :- Box.com
 
Upvote 0
Yes, your logic is correct.

you could delete any duplicate but not the part of the logic.

Also, if I wanted to have the other column to be part of the table where can I edit it?

thanks for ur usual support.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
summing new value over the previous value based on more than item
2
Replies
12
Views
560
Sahak
S
D
  • Solved
Remove Absolute References
Replies
3
Views
85
HighAndWilder
H
I
  • Solved
Edit working code to look for value after a date & before a date
Replies
6
Views
112
Alex Blakenburg
A
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
433
Akuini
Akuini
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
297
mumps
mumps

Forum statistics

Threads
1,223,782
Messages
6,174,513
Members
452,568
Latest member
CVW

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