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

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

<tbody>
</tbody>

	Date	Row No	Amt
Abcd	03-01-2019	1	500
Abcd	03-01-2019	2	300
Lmnp	04-01-2019	3	100
Xyz	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
Dfert	09-01-2019	5	400
Dfert	10-01-2019	5	200
			3650

<tbody>
</tbody>

When using sumifs ultimately to reach the below goal

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

<tbody>
</tbody>

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