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
163
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:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
163
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:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
163

ADVERTISEMENT

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
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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:

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
163

ADVERTISEMENT

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:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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]
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
163
Thanks MickG . Just the Perfect One Indeed Great :)

Thank you so much
SamD
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,989
Members
409,553
Latest member
alscno

This Week's Hot Topics

Top