Getting Row number of Each Date dispalyed (Between fromDate and Todate) in Few columns of a range ?

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
205
Hi

Will it be possible to get the Row number of Each Date dispalyed (Between fromDate and Todate) in Few columns of a range ?
My dates are displayed in column A, C, E,
but in columns A, C, E there could be similar dates,

Condition 1 if similar dates in same row of column A C and E then to get the Row No displayed once.
Condition 2 if similar dates in different rows then Row nos to be displayed.
Condition 3 if diffirent dates then Row nos to be displayed.

The row nos with each date to be displayed in new sheet . ie List of Dates and its respective Row numbers to be displayed between Two Dates on new sheet


Thanks SamDsouza
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Try this for dates and rows on sheet2.

I'm not sure what this means !!!
ie List of Dates and its respective Row numbers to be displayed between Two Dates on new sheet

NB:- if your required results is different please show an example of expected results.
Code:
[COLOR="Navy"]Sub[/COLOR] MG26Feb48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col [COLOR="Navy"]As[/COLOR] Variant, Dt [COLOR="Navy"]As[/COLOR] Variant
Col = Array(1, 3, 5)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dt [COLOR="Navy"]In[/COLOR] Col
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(1, Dt), Cells(Rows.Count, Dt).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] IsDate(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                .Add (Dn.Value), "# " & Dn.Row & " #"
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]If[/COLOR] InStr(.Item(Dn.Value), "# " & Dn.Row & " #") = 0 [COLOR="Navy"]Then[/COLOR]
                    .Item(Dn.Value) = .Item(Dn.Value) & "," & "# " & Dn.Row & " #"
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Dt

[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant

Sheets("Sheet2").Range("A1:B1").Value = Array("Date", "Row No")
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Sp = Split(.Item(K), ",")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
        c = c + 1
        Sheets("Sheet2").Cells(c, 1) = K
        Sheets("Sheet2").Cells(c, 2) = Split(Sp(n), " ")(1)
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG Thanks

representing Sheet 1 and its outcome in Sheet 2
Data in sheet1 as follows

01-01-201902-01-201903-01-2019
03-01-201903-01-201905-01-2019
04-01-201906-01-201907-01-2019
08-01-201908-01-201908-01-2019
07-01-201909-01-201910-01-2019

<tbody>
</tbody>


in Sheet2
DateRow No
01-01-20191
03-01-20192
03-01-20191
04-01-20193
08-01-20194
07-01-20195
07-01-20193
02-01-20191
06-01-20193
09-01-20195
05-01-20192
10-01-20195

<tbody>
</tbody>

It seems the result is not in hierarchy . Pl check the date 02-01-2019 its coming at 8th position, date 05-01-2019 coming at 11th position.
I'm not sure what this means !!!

ie List of Dates and its respective Row numbers to be displayed between Two Dates on new sheet

it should have been like
DateRow No
01-01-20191
02-01-20191
03-01-20191
03-01-20192
04-01-20193
05-01-20192
06-01-20193
08-01-20194
07-01-20193
07-01-20195
09-01-20195
10-01-20195

<tbody>
</tbody>

As the above is difficult to absorb For what reason With CreateObject("scripting.dictionary").CompareMode = vbTextCompare

Also what .keys means

SamDsouza
 
Last edited:
Upvote 0
If you mean you would like the data sorted by date, then add the below to your code, although the "8/1/2019" in the results, seems to be out of place !!!
If your sort logic is something else please explain further.
Code:
With Sheets("Sheet2")
   .Cells(2, 1).Resize(c, 2).Sort .Cells(2, 1)
End With
 
Upvote 0
Thanks MickG . Sorting did the trick.
From your solution. Will it be possible to get the input of From Date and input of to dates and get the Row nos. of the dates between FromDate and ToDate.
ie if the Dates exists in sheet1 FromDate and ToDate to get its row nos

eg if i add textboxes as FromDate.text and ToDate.text and type the dates respectively
for eg in FromDate.Text i type 03-01-2019 and in ToDate.Text 08-01-2019 (Result below in sheet2 )
then Result in sheet2
03-01-20192
03-01-20191
04-01-20193
05-01-20192
06-01-20193
07-01-20195
07-01-20193
08-01-20194

<colgroup><col><col></colgroup><tbody>
</tbody>
and if date dont exists it should not type those dates and row nos ie if i type in ToDate.Text 13-1-2019 because dates 11-1-2019, 12-1-2019 and 13-1-2019 dont exists in sheet1
then result as follows
03-01-20192
03-01-20191
04-01-20193
05-01-20192
06-01-20193
07-01-20195
07-01-20193
08-01-20194
09-01-20195
10-01-20195

<colgroup><col><col></colgroup><tbody>
</tbody>
Thankx SamD
 
Upvote 0
Try this:-
With 3 columns of dates on sheet1(starting row2), and (ActiveX) Textbox1 and Textbox2 on sheet2, with results starting Sheet2 "A2".

Code:
[COLOR="Navy"]Sub[/COLOR] MG28Feb18
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, Col [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Dt [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Q
  Col = Array(1, 3, 5)
   [COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
        Dic.CompareMode = 1
  [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dt [COLOR="Navy"]In[/COLOR] Col
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(1, Dt), Cells(Rows.Count, Dt).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Row) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Row), Nothing

        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
   [COLOR="Navy"]Next[/COLOR] Dt
   
   [COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant, nDt [COLOR="Navy"]As[/COLOR] Date, Ldt [COLOR="Navy"]As[/COLOR] Date, p [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   
   c = 1
   [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]If[/COLOR] .TextBox1.Value <> "" And .TextBox2.Value <> "" [COLOR="Navy"]Then[/COLOR]
    .Columns("A:B").ClearContents
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
     [COLOR="Navy"]If[/COLOR] Dic.exists(CDate(.TextBox1.Value)) And Dic.exists(CDate(.TextBox2.Value)) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] k >= CDate(.TextBox1.Value) And k <= CDate(.TextBox2.Value) [COLOR="Navy"]Then[/COLOR]
          [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
               c = c + 1
               .Cells(c, "a") = k
               .Cells(c, "b") = p
           [COLOR="Navy"]Next[/COLOR] p
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]ElseIf[/COLOR] Dic.exists(CDate(.TextBox1.Text)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] k >= CDate(.TextBox1.Text) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
                    c = c + 1
                    .Cells(c, "a") = k
                    .Cells(c, "b") = p
                [COLOR="Navy"]Next[/COLOR] p
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
 
 [COLOR="Navy"]Next[/COLOR] k
    .Range("A1:B1").Value = Array("Date", "Row No")
    .Cells(2, 1).Resize(c, 2).Sort .Cells(2, 1)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Thanks MickG. Sorry for late reply. Yes Perfect. FYI I've used the userform textboxes and not worksheets ActiveX TxtBoxes. Works Perfectly.
Just wanted to know how can i add another Sort for Row Nos ie from smallest to largest

ie first sorting by Date and the by Row nos from Smallest to Largest

as presented below From Date 3-1-19 to 12-1-19
Date Row No
03-01-2019 1
03-01-2019 2
05-01-2019 2
04-01-2019 3 ----->the Date placement is different as this should have been before 05-01-2019 2
06-01-2019 3
07-01-2019 3
08-01-2019 4
07-01-2019 5 ----->the Date placement is different as this should have been before 08-01-2019 4
09-01-2019 5
10-01-2019 5

Thanks SamD
 
Last edited:
Upvote 0
Try changing line in Red below:-
Code:
.Range("A1:B1").Value = Array("Date", "Row No")
    .Cells(2, 1).Resize(c, 2).Sort[COLOR="#FF0000"][B] key1:=.Cells(2, 1), Key2:=.Cells(2, 2)
[/B][/COLOR]
 
Upvote 0
Hi MickG this Seems to be intresting.
just modified SamD's Structure retaining only the Same dates except its Date shifted to new column added Name and Amt Columns
A B C D E F G
abcd 01-01-2019 300 02-01-2019 400 03-01-2019 500
abcd 03-01-2019 200 03-01-2019 100 05-01-2019 150
lmnp 04-01-2019 100 06-01-2019 600 07-01-2019 700
excl 08-01-2019 150 08-01-2019 0 08-01-2019 250
dfert 07-01-2019 300 09-01-2019 400 10-01-2019 200


Was wondering will it be possible to achieve the Below Result as i have Amounts in column C, E and G
and Names in column A and to use sumifs


Name Date Row No Amt
abcd 03-01-2019 1 500
abcd 03-01-2019 2 300 <---(200+100)
lmnp 04-01-2019 3 100
abcd 05-01-2019 2 150
lmnp 06-01-2019 3 600
lmnp 07-01-2019 3 700
dfert 07-01-2019 5 300
excl 08-01-2019 4 400 <---(150+0+250)
dfert 09-01-2019 5 400
dfert 10-01-2019 5 200
3650


When using sumifs ultimately to reach the below goal
Name Date Row No Amt
abcd 03-01-2019 1 800
abcd 05-01-2019 2 150
lmnp 04-01-2019 3 100
lmnp 06-01-2019 3 600
lmnp 07-01-2019 3 700
dfert 07-01-2019 5 300
excl 08-01-2019 4 400 <---(150+0+250)
dfert 09-01-2019 5 400
dfert 10-01-2019 5 200
3650
Thanks
NimishK
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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