IF statement with CONCATENATE (I think)

[jono]

I have 24 columns that represent groups that a user may or may not be part of. If the user (in column A) is a member of any of these groups then the group number is displayed in the same row as the users name. If they are not part of any of those groups then the cell is left blank.

Here is what I need to do:

I need to create another column titled "group membership" that displays thenumbers of each of the groups that each user is a member of.

I also need each group number to be separated by two colons ::

It needs to look like this 1::2::5::22::24

So I need a formula that pulls all the numbers that may or may not be therein all of those columns that are adjacent to the users name and display them on the same row of the users name.

 

[Jono Landon]

I have 24 columns that represent groups that a user may or may not be part of. If the user (in column A) is a member of any of these groups then the group number is displayed in the same row as the users name. If they arenot part of any of those groups then the cell is left blank.



Here is what I need to do:



I need to create another column titled "group membership" that displays the numbers of each of the groups that each user is a member of.



I also need each group number to be separated by two colons ::



It needs to look like this 1::2::5::22::24



So I need a formula that pulls all the numbers that may or may not be there in all of those columns that are adjacent to the users name and display them on the same row of the users name.

this is what I have so far: =concatenate(if(H3 = $H$2, $H$2&"::",), if(I3 = $I$2, $I$2&"::",), if(J3 = $J$2, $J$2&"::",), if(k3 = $k$2, $k$2&"::",), if(l3 = $l$2, $l$2&"::",), if(m3 = $m$2, $m$2&"::",), if(n3 = $n$2, $n$2&"::",), if(o3 = $o$2, $o$2&"::",), if(p3 = $p$2, $p$2&"::",), if(q3 = $q$2, $q$2&"::",), if(r3 = $r$2, $r$2&"::",), if(s3 = $s$2, $s$2&"::",), if(t3 = $t$2, $t$2&"::",), if(u3 = $u$2, $u$2&"::",),if(v3 = $v$2, $v$2&"::",), if(w3 = $w$2, $w$2&"::",), if(x3 = $x$2, $x$2&"::",), if(y3 = $y$2, $y$2&"::",), if(z3 = $z$2, $z$2&"::",), if(aa3 = $aa$2, $aa$2&"::",), if(ab3 = $ab$2, $ab$2&"::",), if(ac3 = $ac$2, $ac$2&"::",), if(ad3 = $ad$2, $ad$2&"::",), if(ae3 = $ae$2, $ae$2&"::",), if(af3 = $af$2, $af$2&"::",))

which gives me this: 1::2::3::4::5::6::7::8::22::23::24::9::10::11::12::13::15::16::17::18::19::20::

ideally I wouldn't have the unnecessary :: after the last number.

 

[Claus Busch]

hi Jono,

Am Thu, 31 Jul 2014 14:23:18 -0700 (PDT) schrieb Jono Landon:

this is what I have so far: =concatenate(if(H3 = $H$2, $H$2&"::",), if(I3 = $I$2, $I$2&"::",), if(J3 = $J$2, $J$2&"::",), if(k3 = $k$2, $k$2&"::",), if(l3 = $l$2, $l$2&"::",), if(m3 = $m$2, $m$2&"::",), if(n3 = $n$2, $n$2&"::",), if(o3 = $o$2, $o$2&"::",), if(p3 = $p$2, $p$2&"::",), if(q3 = $q$2, $q$2&"::",), if(r3 = $r$2, $r$2&"::",), if(s3 = $s$2, $s$2&"::",), if(t3 = $t$2, $t$2&"::",), if(u3 = $u$2, $u$2&"::",), if(v3 = $v$2, $v$2&"::",), if(w3 = $w$2, $w$2&"::",), if(x3 = $x$2, $x$2&"::",), if(y3 = $y$2, $y$2&"::",), if(z3 = $z$2, $z$2&"::",), if(aa3 = $aa$2, $aa$2&"::",), if(ab3 = $ab$2, $ab$2&"::",), if(ac3 = $ac$2, $ac$2&"::",), if(ad3 = $ad$2, $ad$2&"::",), if(ae3 = $ae$2, $ae$2&"::",), if(af3 = $af$2, $af$2&"::",))

which gives me this: 1::2::3::4::5::6::7::8::22::23::24::9::10::11::12::13::15::16::17::18::19::20::

ideally I wouldn't have the unnecessary :: after the last number.

try:
=SUBSTITUTE(TRIM(H2&" "&I2&" "&J2&" "&K2&" "&L2&" "&M2&" "&N2&" "&O2&" "&P2&" "&Q2&" "&R2&" "&S2&" "&T2&" "&U2&" "&V2&" "&W2&" "&X2&" "&Y2&" "&Z2&" "&AA2&" "&AB2&" "&AC2&" "&AD2&" "&AE2&" " &AF2)," ","::")


Regards
Claus B.