concatenate entries from a column into a cell by logical AND withanother column

[mc]

I have an array with a list of friends in a column and family and relativesalong the top row. Each entry is the relative amount of agitation caused to my family by the friends who are visiting. If certain friends are invitedover, I wanted to combine the agitation factor to each family member in a new array. To explain, here is the array. (x=ok, xx=mild, xx-strong dislike, no entry=neutral)


A B C D E
1 Mom Dad Sister Brother
2 tom x xx xxx
3 jane xxx x
4 silvia xxx xx
5 fred xxx xxx

So on Monday, I invited Tom, Jane, and Fred over and on Tuesday I invited Jane and Silvia over (indicated by a "1" in their cell for that day)

A B C
7 Monday Tuesday
8 tom 1
9 jane 1 1
10 silvia 1
11 fred 1

I want to create an effective agitation chart for each family member based on who I decided to invite over, so I need to "AND" the 2 arrays together and concatenate the results for each family member for each day. So the output will look like this

irritation degree Mom Dad Sister Brother
Monday x xxx xx xxx xxx x xxx
Tuesday xxx x xxx xx

so I wanted a 1-step formula to do this (but it won't work). I tried the following array formula to create an intermediate array.
{=IF((B$8:B$11=1),IF(($B$2:$B$5)="","",($B$2:$B$5)),"")}
which creates this column of data.
B(mon) C(Tues)
14 Mom Mom
15 x
16 xxx xxx
17
18

Now if I concatenate column B using a function "Concat" to make the following entry for Mom for Monday and similarly for Tuesday. This is the end result (which is repeated for all the family)
Mom Monday Tuesday
x xxx xxx

here is the concat function

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
Dim Cell As Range, Area As Variant

If IsMissing(Delimiter) Then Delimiter = ""

For Each Area In CellRanges
If TypeName(Area) = "Range" Then
For Each Cell In Area
If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
Next
Else
ConCat = ConCat & Delimiter & Area
End If
Next

ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

if I process this in 2 steps I get the result I want, however it requires ascratch sheet to be made for each column of data (which I don't want). This command errors out on me but if I use the debugger it shows the array is there.

=concat(" ",IF((B8:B11=1),IF((B2:B5)="","",(B2:B5)),""))

Anyone know what is wrong?
thanks
mark

 

[John Jones]

On Tue, 11 Feb 2014 05:52:38 -0800 (PST), "mc"

I have an array with a list of friends in a column and family and relatives along the top row. Each entry is the relative amount of agitation caused to my family by the friends who are visiting. If certain friends are invited over, I wanted to combine the agitation factor to each family member in a new array. To explain, here is the array. (x=ok, xx=mild, xx-strong dislike, no entry=neutral)


A B C D E
1 Mom Dad Sister Brother
2 tom x xx xxx
3 jane xxx x
4 silvia xxx xx
5 fred xxx xxx

So on Monday, I invited Tom, Jane, and Fred over and on Tuesday I invited Jane and Silvia over (indicated by a "1" in their cell for that day)

A B C
7 Monday Tuesday
8 tom 1
9 jane 1 1
10 silvia 1
11 fred 1

I want to create an effective agitation chart for each family member based on who I decided to invite over, so I need to "AND" the 2 arrays together and concatenate the results for each family member for each day. So the output will look like this

irritation degree Mom Dad Sister Brother
Monday x xxx xx xxx xxx x xxx
Tuesday xxx x xxx xx

so I wanted a 1-step formula to do this (but it won't work). I tried the following array formula to create an intermediate array.
{=IF((B$8:B$11=1),IF(($B$2:$B$5)="","",($B$2:$B$5)),"")}
which creates this column of data.
B(mon) C(Tues)
14 Mom Mom
15 x
16 xxx xxx
17
18

Now if I concatenate column B using a function "Concat" to make the following entry for Mom for Monday and similarly for Tuesday. This is the end result (which is repeated for all the family)
Mom Monday Tuesday
x xxx xxx

here is the concat function

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
Dim Cell As Range, Area As Variant

If IsMissing(Delimiter) Then Delimiter = ""

For Each Area In CellRanges
If TypeName(Area) = "Range" Then
For Each Cell In Area

Mark
I cant see what's wrong but an easier design to implement is:

If you replace the xxxx by 4 etc then you can use sumproduct
very easily to get the overall irritation.

But I doubt irritation is simply additive ;-) I'll bet Dad gets really
annoyed if both tom and fred appear in the same room as he.

Happy families!
HTH
JJ

 

[mcascia]

Hi Howard, thanks for the formula. Very innovative, however I need to maintain the "x" "xx" or "xxx" with spaces between them. I only used the irritation factor as an example. My real application has to do with efficiencies of certain chemical compounds in certain chemical reactions where "x" is inefficient and "xxx" is most efficient (or effective) out of a large matrix of chemical reactions. I've been stymied with how to do this directly. Thereis more to the spreadsheet that will muddy the waters but that is fairly straightforward. This was the difficult part. I can do it with a very long concatenate string with If statements but I quickly run out of room in a single cell for a large array.
mark