Printable View
I need a list of each date from column B in the attached sheet. From all the multiple dates I need a list of just one date from each date group.
Column B of attached contains multiples of the same date what I am needing is to have a list of each date form each date range.
Hope this makes sense.
Thank you any and all help
Example of desired result:
1/2/2023
2/3/2023
2/10/2023
2/17/2023
Where do you want this list to be output?
In VBA, add a general module and paste this code:
Or install Power Query add-in and learn it. I am trying it now for your requirement and it is challenging. I can get it to work but complex enough I don't want to try to describe to you here.Code:
Sub GetDates()
'code for Excel VBA
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text"""
rs.Open "SELECT DISTINCT [Data$].[Period End Date] FROM [Data$] WHERE NOT [Data$].[Period End Date] IS NULL", cn, adOpenStatic, adLockOptimistic, adCmdText
Worksheets("Data").Range("L2").CopyFromRecordset rs
rs.MoveFirst
Do While Not rs.EOF
Debug.Print rs(0)
rs.MoveNext
Loop
End Sub
That works but is there not a formula that could do the same?
Thank you for your help
Easiest way would be to copy the date column to a blank column, then do Data>Data Tools>Remove Duplicates (don't expand the selection), and that should get you your result.
Okay, Bob, that worked. If this is a one-time occurrence, manual exercise is fine. If this is going to be a recurring event, I would probably use VBA recordset or PowerQuery.
I don't know if there is a formula to accomplish this.
1. VBA
2. Worksheet for newer versions of ExcelCode:Option Explicit
Sub Macro1()
Worksheets("Data").Range("B:B").Copy Worksheets("Sheet1").Range("A1")
Application.CutCopyMode = False
Application.DisplayAlerts = False
Worksheets("Sheet1").Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlYes
Application.DisplayAlerts = True
End Sub
Okay, Paul, simpler VBA. Nice!
For the formula, you have to put it into as many cells as you expect there would be distinct date values, even in the header row. It's magic! How does it know to return different values? In Excel 2010 it shows as an array formula:
{=_xlfn.UNIQUE(Data!$B1:$B725,FALSE)}
Thank you all for your help!
I will work to integrate them into my sheet and I think Paul's solution is the simplest.
Again, thank you all for your input all ideas could work.
Dynamic arrays were added in later versionsQuote:
Originally Posted by June7
https://exceljet.net/glossary/dynami...ill%20range%22.
https://exceljet.net/dynamic-array-formulas-in-excel
https://a4accounting.com.au/new-form...mbol-in-excel/
Quote:
The # symbol follows the reference and it makes it incredibly easy to refer to spilled ranges. You use the # after the reference to the cell in the top left corner of the spilled range.
The @ symbol precedes the reference and it forces Excel to treat the reference as it would have pre-dynamic arrays.
Attached a workbook I was using to investigate
Quote:
As of January 2023, many more new functions have now been released to take advantage of the dynamic array engine. The complete list of new functions is: ARRAYTOTEXT, BYCOL, BYROW, CHOOSECOLS, CHOOSEROWS, DROP, EXPAND, FILTER, HSTACK, ISOMITTED, LAMBDA, LET, MAKEARRAY, MAP, RANDARRAY, REDUCE, SCAN, SEQUENCE, SORT, SORTBY, STOCKHISTORY, TAKE, TEXTAFTER, TEXTBEFORE, TEXTSPLIT, TOCOL, TOROW, UNIQUE, VALUETOTEXT, VSTACK, WRAPCOLS, WRAPROWS, XLOOKUP, and XMATCH.
Correction, in Access 2010 it showed the same formula in each cell but doesn't seem to work. Here is method for older versions.
Get a list of unique values in Excel & extract unique rows (ablebits.com)
This fix is so much better, thank youQuote:
Originally Posted by June7