Help with summarizing multiple cells

[PhilG]

Hello all,

I'm hoping that this is a simple solution, and I'm just not seeing it.

I have a worksheet with multiple client rows, with an adjacent date column (it's a schedule). What I need to do, is have a formula (or VBA code) that looks through the date column and finds all client rows with a given value (ie. same date), and summarize it all in one cell.

For instance ...

A2 = ABC Co.
A3 = XYZ Co.
A4 = John Doe

B2 = 01/01/2005
B3 = 01/12/2005
B4 = 01/01/2005

Here's what I need calculated from these ...

In C2, I need to display all clients with a date of 01/01/2005 (ie. ABC Co. and John Doe).

I have been able to do an OFFSET & MATCH combo to get me close

[ =OFFSET($A$1,MATCH("01/01/2005",$B$2:$B$4,0),0) ]

but that only gives me the first instance of 01/01/2005 (ie. ABC Co.). How do I get this to look through the entire list and list ALL clients scheduled on 01/01/2005?

Or would this require VBA code?

 

[Aladin Akyurek]

Wouldn't Data|Filter|AutoFilter meet your need?

 

[PhilG]

Not entirely ...

It's true that Autofilter would hide the non-matching rows, and leave me with multiple rows of clients with a matching date.

However, what I must have is the matching data to be shown in 1 cell (it's a long story).

Based on my example above, C2 must result in "ABC Co. John Doe" -- I can worry about the formatting latter once I get the values I need in there.

AutoFilter doesn't help me in this case, but thanks for the prompt reply Aladin.

 

[mazzymx6]

how many client records would you have in your list ?
how many would you expect in 1 day ?

 

[PhilG]

The # of client rows ranges from 70 to 300.
The # of times a given date is listed will range from 1 to 10

 

[Aladin Akyurek]

Not entirely ...

It's true that Autofilter would hide the non-matching rows, and leave me with multiple rows of clients with a matching date.

However, what I must have is the matching data to be shown in 1 cell (it's a long story).

Based on my example above, C2 must result in "ABC Co. John Doe" -- I can worry about the formatting latter once I get the values I need in there.

AutoFilter doesn't help me in this case, but thanks for the prompt reply Aladin.

I see. If you download and install the free morefunc.xll add-in, you can have:

Book1
	B	C	D	E	F	G
1
2	ABC Co.	1/1/2005
3	XYZ Co.	1/12/2005			1/1/2005	ABC Co.,John Doe
4	John Doe	1/1/2005
5
6
Sheet1

G3:

=SUBSTITUTE(MCONCAT(IF($C$2:$C$4=F3,","&$B$2:$B$4,"")),",","",1)

which must be confirmed with control+shift+enter, not just with enter.

Note that MCONCAT has a limit of 255 chars.

 

[PhilG]

Aladin,

That looks like it will do the trick.

Where can I download this free add-in?

 

[Aladin Akyurek]

Aladin,

That looks like it will do the trick.

Where can I download this free add-in?

See:

http://xcell05.free.fr/

 

[PhilG]

Got it ... and it works wonderfully!

Thanks much for the help. :D