help with formula to pull data based on a condition in together in another cell

[lilsnoop]

Hello, I have a column with dates and another column beside it. I need a formula in cell D25 that would look at column O16:O37 and any cell in that range that has an x or other character would bring the date in the adjacent cell (column P) together separated by a comma. Any help would be greatly appreciated.


Untitled.jpg

[lilsnoop]

After some research I found this formula to list the dates via the selected "x"'s in column O:

Please Login or Register  to view this content.

I think I'm going to have to use vba to concatenate the list, though I would prefer a formula based solution.

[Glenn Kennedy]

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with?

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[lilsnoop]

Thanks Glenn! I have Excel 2013 on home computer and Excel 2016 at work, which is what this problem is associated for. My profile has been updated to reflect Excel 2016.

[Glenn Kennedy]

Not quite modern enough!

Is VBA (a UDF to be exact) OK?

[Glenn Kennedy]

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Then use:

=concatall(IF(I4:I10="x",TEXT(J4:J10,"mm/dd/yyyy"),""),", ")

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[lilsnoop]

Thank you Glenn! Appreciate all your help!