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]

I have used a much smaller range here but you should be able to expand to your range.

Both formulas copied down as far as you might ever need.

<b>List & Count</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:24px;" /><col style="width:80px;" /><col style="width:50px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Date</td><td style="font-size:10pt; text-align:right; ">No</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:10pt; text-align:right; ">3-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">6-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3-Nov-19</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">4-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">4-Nov-19</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="font-size:10pt; text-align:right; ">4-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">3-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">6-Nov-19</td><td style="font-size:10pt; text-align:right; ">2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">6-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">4-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">4-Nov-19</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H25</td><td >=IF(SUM<span style=' color:008000; '>(I$24:I24)</span>=COUNT<span style=' color:008000; '>($A$25:$F$30)</span>,"",SMALL<span style=' color:008000; '>($A$25:$F$30,SUM<span style=' color:#0000ff; '>(I$24:I24)</span>+1)</span>)</td></tr><tr><td >I25</td><td >=IF(H25="","",COUNTIF<span style=' color:008000; '>($A$25:$F$30,H25)</span>)</td></tr></table></td></tr></table>

 

[masud8956]

@Peter_SSs,

Thank you very much for such a beautiful solution!

Worked Perfectly!!

 

[Peter_SSs]

@Peter_SSs,

Thank you very much for such a beautiful solution!

Worked Perfectly!!

You're welcome. Thanks for the follow-up.

 

[masud8956]

@Peter_SSs,

Sorry. A little problem here.

Solution worked Perfectly in the sample when I tried!!

But somehow when I am pasting this to the original data I am getting a result like this:

https://www.dropbox.com/s/0usfh3i8i8i8zxg/Screenshot_1.jpg?dl=0

Where do you think I may have done a mistake?

 

[Peter_SSs]

Could it be that your dates in A25:F30 acually have times attached to them as well, though not displayed?

If so, do you actually need those times since they are not displayed & could your code that inserts the date stamps only insert the data rather than date/time?

If that doesn't resolve the issue, any chance you could upload the sample workbook itself rather than just an image so I can have a look and see what is happening?

If you do have times and need to keep them, we should be able to make other adjustments in the formulas.

 

[masud8956]

Could it be that your dates in A25:F30 acually have times attached to them as well, though not displayed?

If so, do you actually need those times since they are not displayed & could your code that inserts the date stamps only insert the data rather than date/time?

You are right. The formula I used involves date/time. In any usable format I just need the date though, not the time.

The formula I used:

=IF(Sheet1!A2<>"",IF(A2="",NOW(),A2),"")

I am adding the sample. Please see if there is a way around this.

https://www.dropbox.com/s/jhtt87f7ojitvul/Sample_1.xlsx?dl=0

 

[Peter_SSs]

You are right. The formula I used involves date/time. In any usable format I just need the date though, not the time.

The formula I used:

=IF(Sheet1!A2<>"",IF(A2="",NOW(),A2),"")

If you are using a formula to enter the date then whenever the sheet recalculates, all those dates will become the current date!
For example, when I downloaded and opened your file, every date on Sheet 2 shows 4-Nov-19

If you want the dates in Sheet2 to reflect the actual date that the data was entered into Sheet1, you will need to use vba. I suggest that you do a search on the forum (or Google) for "Excel vba Date Stamp"

 

[masud8956]

Thanks @Peter_SSs !

I understand the need for macro now. Found the following solution after search:

Private Sub Worksheet_Change(ByVal Target As Range)    
   
   If Target.Column = 5 Then
        Application.EnableEvents = False
        Cells(Target.Row, 86).Value = Date
        Application.EnableEvents = True
    
    End If

End Sub

This seems to work. But I have 2 problems now.

1. This macro only allows date to be stamped at Column 86 when there is any data inserted/changed at Column 5. I need to be able to get the same for other columns too. e.g. date should be stamped to Column 87 when data inserted in Column 6, at 88 for 7 and so on.

2. When I delete the data from Column 5,6,7 etc I want date stamps at column 86,87,88 etc to be removed from respective cells.

I cannot figure out what modification I need to the code.

 

[masud8956]

Sorry, Forgot to mention that the sheet will be password protected