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:
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?
Please Login or Register to view this content.
Might give this a try
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
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:
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.Please Login or Register to view this content.
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)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
here is the UDF I am working with
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 seePlease 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.
Please Login or Register to view this content.
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 UDFThen use this formula in F2 dragged down.Please Login or Register to view this content.
=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
Note that the formula in E2 is array-entered.Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by shg; 09-14-2012 at 11:20 AM.
Entia non sunt multiplicanda sine necessitate
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:
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.
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
cool... thanks for all your help all the formulas submitted worked so thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks