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

[SamDsouza]

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

 

[NimishK]

Please ignore above post 10 as structure not presented well
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
[TABLE="width: 0"]
<tbody>[TR]
[TD] A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]Abcd
[/TD]
[TD]01-01-2019
[/TD]
[TD]300
[/TD]
[TD]02-01-2019
[/TD]
[TD]400
[/TD]
[TD]03-01-2019
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]Abcd
[/TD]
[TD]03-01-2019
[/TD]
[TD]200
[/TD]
[TD]03-01-2019
[/TD]
[TD]100
[/TD]
[TD]05-01-2019
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]Lmnp
[/TD]
[TD]04-01-2019
[/TD]
[TD]100
[/TD]
[TD]06-01-2019
[/TD]
[TD]600
[/TD]
[TD]07-01-2019
[/TD]
[TD]700
[/TD]
[/TR]
[TR]
[TD]Excl
[/TD]
[TD]08-01-2019
[/TD]
[TD]150
[/TD]
[TD]08-01-2019
[/TD]
[TD]0
[/TD]
[TD]08-01-2019
[/TD]
[TD]250
[/TD]
[/TR]
[TR]
[TD]Dfert
[/TD]
[TD]07-01-2019
[/TD]
[TD]300
[/TD]
[TD]09-01-2019
[/TD]
[TD]400
[/TD]
[TD]10-01-2019
[/TD]
[TD]200
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 0"]
<tbody>[TR]
[TD]
[/TD]
[TD]Date
[/TD]
[TD]Row No
[/TD]
[TD]Amt
[/TD]
[/TR]
[TR]
[TD]Abcd
[/TD]
[TD]03-01-2019
[/TD]
[TD]1
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]Abcd
[/TD]
[TD]03-01-2019
[/TD]
[TD]2
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]Lmnp
[/TD]
[TD]04-01-2019
[/TD]
[TD]3
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]Xyz
[/TD]
[TD]05-01-2019
[/TD]
[TD]2
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]Lmnp
[/TD]
[TD]06-01-2019
[/TD]
[TD]3
[/TD]
[TD]600
[/TD]
[/TR]
[TR]
[TD]Lmnp
[/TD]
[TD]07-01-2019
[/TD]
[TD]3
[/TD]
[TD]700
[/TD]
[/TR]
[TR]
[TD]Dfert
[/TD]
[TD]07-01-2019
[/TD]
[TD]5
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]Excl
[/TD]
[TD]08-01-2019
[/TD]
[TD]4
[/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD]Dfert
[/TD]
[TD]09-01-2019
[/TD]
[TD]5
[/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD]Dfert
[/TD]
[TD]10-01-2019
[/TD]
[TD]5
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3650
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
When using sumifs ultimately to reach the below goal
[TABLE="width: 0"]
<tbody>[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name
[/TD]
[TD] Date
[/TD]
[TD] Row No
[/TD]
[TD]Amt
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abcd
[/TD]
[TD]03-01-2019
[/TD]
[TD]1
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abcd
[/TD]
[TD]03-01-2019
[/TD]
[TD]2
[/TD]
[TD]300
[/TD]
[TD="colspan: 2"]<---(200+100)
[/TD]
[/TR]
[TR]
[TD]Lmnp
[/TD]
[TD]04-01-2019
[/TD]
[TD]3
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abcd
[/TD]
[TD]05-01-2019
[/TD]
[TD]2
[/TD]
[TD]150
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lmnp
[/TD]
[TD]06-01-2019
[/TD]
[TD]3
[/TD]
[TD]600
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lmnp
[/TD]
[TD]07-01-2019
[/TD]
[TD]3
[/TD]
[TD]700
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dfert
[/TD]
[TD]07-01-2019
[/TD]
[TD]5
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Excl
[/TD]
[TD]08-01-2019
[/TD]
[TD]4
[/TD]
[TD]400
[/TD]
[TD="colspan: 2"]<---(150+0+250)
[/TD]
[/TR]
[TR]
[TD]Dfert
[/TD]
[TD]09-01-2019
[/TD]
[TD]5
[/TD]
[TD]400
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dfert
[/TD]
[TD]10-01-2019
[/TD]
[TD]5
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks NimishK

 

[NimishK]

I've been struggling on the Date MIS for quite long.
to Achive the First Part of post #11 ie to get the Names of Column A from sheet1 against each date
i added the syntax below in Red. But still this does not satisfy the result. I think if the 1st part achieved as then on the same basis sumifs will be achieved.
This was the thread that had almost resolved my issues but partly.
Some Great insights will be highly appreciated

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


        End If
    Next Dn
   Next Dt
   
   Dim k As Variant, nDt As Date, Ldt As Date, p As Variant, c As Long
   
   c = 1
   With Sheets("Sheet2")
    If txtFromDate.Value <> "" And txtToDate.Value <> "" Then
    .Columns("A:C").ClearContents   'B"
    For Each k In Dic.Keys
     If Dic.exists(CDate(txtFromDate.Value)) And Dic.exists(CDate(txtToDate.Value)) Then
        If k >= CDate(txtFromDate.Value) And k <= CDate(txtToDate.Value) Then
          For Each p In Dic(k)
               c = c + 1
               .Cells(c, "a") = k
               .Cells(c, "b") = p
[B][COLOR=#ff0000]               .Cells(c, "c") = Worksheets("sheet1").Range("A" & p).Value[/COLOR][/B]
           Next p
        End If
        ElseIf Dic.exists(CDate(txtFromDate.Text)) Then
            If k >= CDate(txtFromDate.Text) Then
                For Each p In Dic(k)
                    c = c + 1
                    .Cells(c, "a") = k
                    .Cells(c, "b") = p
[B][COLOR=#ff0000]                    .Cells(c, "c") = Worksheets("sheet1").Range("A" & p).Value[/COLOR][/B]
                Next p
            End If
        End If
 
 Next k
    .Range("A1:B1").Value = Array("Date", "Row No")
    '.Cells(2, 1).Resize(c, 2).Sort .Cells(2, 1)
    .Cells(2, 1).Resize(c, 2).Sort key1:=.Cells(2, 1), Key2:=.Cells(2, 2)
End If
End With
End Sub

Thankx NimishK