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

S

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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
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
Click to expand...

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

[TABLE="width: 0"]
<tbody>[TR]
[TD]01-01-2019[/TD]
[TD][/TD]
[TD]02-01-2019[/TD]
[TD][/TD]
[TD]03-01-2019[/TD]
[/TR]
[TR]
[TD]03-01-2019[/TD]
[TD][/TD]
[TD]03-01-2019[/TD]
[TD][/TD]
[TD]05-01-2019[/TD]
[/TR]
[TR]
[TD]04-01-2019[/TD]
[TD][/TD]
[TD]06-01-2019[/TD]
[TD][/TD]
[TD]07-01-2019[/TD]
[/TR]
[TR]
[TD]08-01-2019[/TD]
[TD][/TD]
[TD]08-01-2019[/TD]
[TD][/TD]
[TD]08-01-2019[/TD]
[/TR]
[TR]
[TD]07-01-2019[/TD]
[TD][/TD]
[TD]09-01-2019[/TD]
[TD][/TD]
[TD]10-01-2019[/TD]
[/TR]
</tbody>[/TABLE]


in Sheet2
[TABLE="width: 0"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Row No[/TD]
[/TR]
[TR]
[TD]01-01-2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]03-01-2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]03-01-2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]04-01-2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]08-01-2019[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]07-01-2019[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]07-01-2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]02-01-2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]06-01-2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]09-01-2019[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]05-01-2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10-01-2019[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 0"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Row No[/TD]
[/TR]
[TR]
[TD]01-01-2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02-01-2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]03-01-2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]03-01-2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]04-01-2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]05-01-2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]06-01-2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]08-01-2019[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]07-01-2019[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]07-01-2019[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]09-01-2019[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]10-01-2019[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
[TABLE="width: 163"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]03-01-2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]03-01-2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]04-01-2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]05-01-2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]06-01-2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]07-01-2019[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]07-01-2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]08-01-2019[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 163"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]03-01-2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]03-01-2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]04-01-2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]05-01-2019[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]06-01-2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]07-01-2019[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]07-01-2019[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]08-01-2019[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]09-01-2019[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10-01-2019[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
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
You must log in or register to reply here.

Similar threads

N
  • Solved
Help with formula please, if two values match calculate the difference between 2 dates
2
Replies
11
Views
432
Newbie73
N
A
  • Solved
Conditional Formatting for rows of empty cells corresponding with dates between
Replies
4
Views
467
avril18
A
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
256
jsand
J
F
  • Solved
Colour all blank cells in a row if one cell in row has text.
Replies
3
Views
426
Joe4
Joe4
J
  • Question Question
Formula to find date and return column number
Replies
2
Views
441
jc352
J

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

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