Consulting

Results 1 to 11 of 11

Thread: Formula to find single date from multiple dates

  1. #1
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    181
    Location

    Formula to find single date from multiple dates

    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
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    343
    Location
    Where do you want this list to be output?

    In VBA, add a general module and paste this 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
    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.
    Last edited by June7; 03-06-2023 at 04:07 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    181
    Location
    That works but is there not a formula that could do the same?

    Thank you for your help

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,445
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    343
    Location
    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.
    Last edited by June7; 03-06-2023 at 07:39 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,724
    Location
    1. VBA

    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
    2. Worksheet for newer versions of Excel
    Attached Images Attached Images
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    343
    Location
    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)}
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    181
    Location
    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.

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,724
    Location
    Quote Originally Posted by June7 View Post
    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)}
    Dynamic arrays were added in later versions

    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/


    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


    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.
    Attached Files Attached Files
    Last edited by Paul_Hossler; 03-07-2023 at 08:31 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    343
    Location
    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)
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Quote Originally Posted by June7 View Post
    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 you
    Last edited by Aussiebear; 11-28-2023 at 12:50 AM. Reason: Removed the spam link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •