Concatonate - formula too long

[Guest]

I have a cell that populates by determining if the columns next to it have an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added. I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

 

[Don Guillett]

Try this idea instead. Modify to suit
=INDEX(E13:J13,1,MATCH("x",E14:J14,0))

 

[Earl Kiosterud]

Foo2U2,

It sounds as though you've set up your table sideways, compared to the normal table layout.
That is, you have related stuff in columns instead of rows. There are many Excel tools that
won't help you with such a layout. If it's possible to have your table rearranged so it's
row for column, then you could use autofilter for this requirement. To rearrange the table,
copy it, then paste special somewhere else with transpose, then move it back over the
original. If you have more than 256 rows now, you'll end up with too many columns, and
won't be able to do this unless you have Excel 2007.

 

[Guest]

A simple Function is much easier to work with the a very long formula. They
are also easier to debug and easier to write. You can call the function with
your parameters. try it, your will like it.

Excel spreadsheet call (looks like the standarrd functtions)
=MyFunction (E3, F3, G3, H3, I3)


In VBA where the return value is Myfunction (the function name)

Function Myfunction( Parm1, Parm2, Parm3, Parm4, Parm5)

Myfunction = False

if( Parm1 = Parm2) then
Myfunction = true
end if

End Function


Here is one a wrote tthis morning
Function FindLast(ShortString, LongString) As Integer

Shortlength = Len(ShortString)
LongLength = Len(LongString)

FindLast = 0
If (Shortlength <= LongLength) Then

StringPosition = LongLength - Shortlength + 1

For i = StringPosition To 1 Step -1

If (StrComp(ShortString, Left(Mid(LongString, i, Shortlength),
Shortlength)) = 0) Then
FindLast = i
Exit For
End If

Next i

End If

End Function

 

[David Biddulph]

Will that deal with "X"s in more than one column, which seemed to be what
the OP was after?

 

[Guest]

That's true; I tried it and could only work with one value.

 

[Dave Peterson]

How about adding two more cells--three total.

In the first cell, put a long formula for lots of cells. In the next cell, do
the rest of the concatenation.

In the third cell, just concatenate the first two cells.

If you have to, hide the columns with those two "helper cells".

 

[Don Guillett]

Try this macro

Sub addifxinrow()
ar = ActiveCell.Row
For Each c In Range(Cells(ar, "e"), Cells(ar, "z"))
If UCase(c) = "X" Then ms = ms & "," & c.Offset(-1)
Next
MsgBox Right(ms, Len(ms) - 1)
End Sub

or a function that must go into a regular module.
Function ax() 'put in the active row at col D maybe
ar = ActiveCell.Row
For Each c In Range(Cells(ar, "e"), Cells(ar, "z"))
If UCase(c) = "X" Then ms = ms & "," & c.Offset(-1)
Next
ax = Right(ms, Len(ms) - 1)
End Function