concat cells based on date range

[DamianWarS]

i have one column with a bunch of dates and another column with a bunch of values... what I want to do is find all the values that occur on a specific date and concatenate them together in one cell... for example:

Please Login or Register  to view this content.

I'm using a UDF so I can concatenate a range for example contact(A:A) or contact(A1:A10)... this works fine but I'm having a hard time figuring out how to select the range based on the date condition... any ideas?

[mike7952]

Might give this a try

Please Login or Register  to view this content.

[DamianWarS]

what I need is something I can use in a formula... I don't have much experience with vba but it doesn't look like I can use that in a formula... if you can please post an example.

in pseudo code basically what I want is:

Please Login or Register  to view this content.

obviously the above doesn't work but the idea is that I want to able to use a condition to a find all values in a column that match a date in a corresponding column and then concatenate them together in one cell. I know excel doesn't have native concatenating support when selecting an unknown range so a UDF is at least required to allow the concatenation to work but im just trying to figure out how to combine that with a corresponding date condition.

[ChemistB]

Excel does not have a simple non-VBA range to concatenate a range.

If you are using a UDF, that means you are using VBA (that's how a UDF works). What do you currently supply to the UDF in order for it to return your concatenated range?

An uploaded example of your spreadsheet with the UDF would help greatly! (Go Advanced>Manage Attachments)

[DamianWarS]

here is the UDF I am working with

Please Login or Register  to view this content.

the data is not that complex.... it is sensitive information so I can't upload the actual file but there is only two columns that are effected here for this issue which is the date column and the values column so here is an example of what you might see

Please Login or Register  to view this content.

and using the above information this is how I want it to be sorted but the original information left alone and the sorted information in a new sheet. The new sheet will act as a dynamic area where you just change a date and it then back fills the information using the data on sheet1 with the corresponding information.

Please Login or Register  to view this content.

[ChemistB]

I couldn't get your UDF to work with my formula so used a UDF from Harlan Grove ACONCAT(range, delimiter) and that seemed to work.
Add this code to a module for the UDF

Please Login or Register  to view this content.

Then use this formula in F2 dragged down.

=SUBSTITUTE((ACONCAT(IF($A$2:$A$14=E2,$B$2:$B$14,),", "))&", ","0, ","")

It's an array formula so you need to enter it with CONTRL SHFT ENTER instead of a simple ENTER (that'll put the {} around it if done properly)

I'm having trouble removing the last comma for some reason. Maybe someone else can figure that out. If it's important, put the formula inside LEFT(the formula, LEN(the formula)-1

[shg]

Please Login or Register  to view this content.

Note that the formula in E2 is array-entered.

Please Login or Register  to view this content.

[tigeravatar]

DaminWarS,

Attached is an example workbook based on the criteria you described.
I used a UDF I created named ConcatAll found here: ConcatAll UDF

In the example workbook there are two types of formulas. In cell E4 and copied down is this regular formula:

Please Login or Register  to view this content.

That is a bit long though, and is a lot of work to avoid using an array formula, which is the other option. In cell H4 and copied down is this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how it gets surrounded by the curly braces {}. Do not try to add those yourself.

Please Login or Register  to view this content.

[DamianWarS]

cool... thanks for all your help all the formulas submitted worked so thanks again!