Extract all unique values from a range (multiple columns and rows) to another location while counting the number of entries

[masud8956]

Hi,

I have 2 identical ranges (A25:BT44) in two different sheets (1 & 2). Users are allowed to enter data in any cell of the range A25:BT44 of Sheet1. The purpose of the same range in Sheet2 is to stamp dates of data entry. So I have the dates of each data entry in the corresponding cells of the same range in Sheet2. The range in both the sheets will have random blank cells. The range in Sheet2 looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]BT[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]03 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]06 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03 NOV 19[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]04 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"]04 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]03 NOV 19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]06 NOV 19[/TD]
[TD="align: center"][/TD]
[TD]04 Nov 19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]....[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD]

04 NOV 19​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]04 NOV 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What formula can I use to get a list of all unique dates along with total number of entries of the same day in that specific range? I expect a result as following:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]No of Entries[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]03 Nov 19[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]04 Nov 19[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]06 Nov 19[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

After extracting the result I intend to use them as chart to display amount of job done per day vs a specific target.

I do not prefer VBA unless that is the only option.

TIA.

 

[Peter_SSs]

Have you now changed from wanting the date stamps on Sheet2 and want them on the same sheet as the original data entry or is that just because of the code you found? It would be easy to have them in the identical range in another sheet like your original setup.

 

[masud8956]

Have you now changed from wanting the date stamps on Sheet2 and want them on the same sheet as the original data entry or is that just because of the code you found? It would be easy to have them in the identical range in another sheet like your original setup.

Actually Yes! I have done it because of the code. I am not very confident on modifying VBA code. But if keeping the source and destination range identical in different sheets is easier then that is absolutely fine.

Here is what I am looking for:

1. My data range is E5:BX24 for Sheets 1 and 2 (had to change a bit from #1 ). In sheet1 users will enter data and I want the date stamped on the identical cells of sheet2.
2. I want the date stamp removed from E5:BX24 at sheet2 if user deletes entries at sheet1.
3. Both the sheets are password protected (same password). The wordbook will also be protected (same password). Sheet2 will be hidden as well. Macro will be password protected as well (same password).
4. I am using Excel 2016.

What code should I use for my requirements?

Thanks again!

 

[Peter_SSs]

In Sheet2, you can still have the formulas set up as before, but with the new range of E5:BX24

To get/clear the date stamps you can use this worksheet change code in Sheet1.
To implement ..
1. Right click the Sheet1 name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Edit the password for Sheet2 (twice).
4. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ws2 As Worksheet
  Dim myRange As Range, Changed As Range, cel As Range
  
  Set myRange = Range("E5:BX24")
  Set ws2 = Sheets("Sheet2")
  Set Changed = Intersect(myRange, Target)
  If Not Changed Is Nothing Then
    ws2.Unprotect Password:="yourpassword"
    For Each cel In Changed
      ws2.Range(cel.Address).Value = IIf(Len(cel.Value) > 0, Date, "")
    Next cel
    ws2.Protect Password:="yourpassword"
  End If
End Sub

 

[masud8956]

The code works perfectly!

Thank you again!!

Some how the code was giving error with

[COLOR=#333333]Set ws2 = Sheets("Sheet2")[/COLOR]

[Then I replaced "Sheet2" with the sheet name and it works just fine. I have rechecked....the destination sheet is actually Sheet2.]

 

[Peter_SSs]

[Then I replaced "Sheet2" with the sheet name and it works just fine. I have rechecked....the destination sheet is actually Sheet2.]

Does that mean Sheet2 was the sheets codename not its tab name?

In any case, is everything working properly now?

 

[masud8956]

The code was giving error. After debugging it pointed to:

[COLOR=#333333]Set ws2 = Sheets("Sheet2")[/COLOR]

Sheet2 name is "DATA". Then I changed the line as:

[COLOR=#333333]Set ws2 = Sheets("DATA")[/COLOR]

And it is working perfectly now.

 

[Peter_SSs]

And it is working perfectly now.

Cheers. Thanks for the clarification.