Excel challenge

[Gene Khalyapin]

I know there must be a solution for this, just can't figure it out:

Row 1 has a set of triggeres (in different columns): 1,0,1,0,1
Row 2 has corresponding values: a,b,c,d,e

I need a formula that would look at triggers and for each trigger equal to 1, it should pick up the values from Row 2 and combines them in one sell. In the example above the desired result should be: "ace"

Is there an expandable formula solution for this?

Thanks!

 

[VoG]

Here's a User Defined Function that goes in a standard module

Function concat(r As Range) As String
Dim c As Range
For Each c In r
    If c.Offset(-1).Value = 1 Then concat = concat & c.Value
Next c
End Function

on the worksheet use

=concat(A2:E2)

 

[Gene Khalyapin]

Thanks for the quick response VoG,

Your formula works, but I need to have a macro-free workbook.

Thanks,

 

[ukmikeb]

Hi

Try -

 =CONCATENATE(IF(A1,A2,""),IF(B1,B2,""),IF(C1,C2,""),IF(D1,D2,""),IF(E1,E2,""))

hth

Mike

 

[Gene Khalyapin]

Mike, I need an expandable formula, that is I may have 150 triggers in first row and 150 corresponding values and need quickly expand the range to include all values.

The formula that you suggested, only in its array form (Ctrl+Shift+Enter), was my first try:

{=CONCATENATE(IF(A1:E1=1,A2:E2,""))}

this gives the desired range {"a","","c","","e"} but CONCATENATE does not accept arrays as an input...

 

[tweedle]

Does it need to be a single cell solution?

A3: =IF(A1=1,A2,"")
B3: =IF(B1=1,CONCATENATE(A3,B2),A3)

Stretch B3 as wide as you need and reference the last column to the final destination.

 

[Gene Khalyapin]

Does it need to be a single cell solution?

A3: =IF(A1=1,A2,"")
B3: =IF(B1=1,CONCATENATE(A3,B2),A3)

Stretch B3 as wide as you need and reference the last column to the final destination.

Yes, it needs to be one cell (but I'll check if I can use your approach). The example with 1,0,1,0 triggers and values is extremely simplified just to illustrate the functionality that I need (once I know how it's done I will be able to apply it in this very complex model).

In the actual model the triggers are different numbers and strings of text, but I can "clean" them to be something like 1,0,2,4,1,0,2 etc. and then pick up only 1s or 2s... And the raw data that I am working with has thousands of rows. So I need one column to the right that would "digest" triggers in different columns of the raw data dump and assign predefined values (again, even this set of predefined values will be different for a different first trigger) to give me a string of values, the "code", that will contain all info about that line item. This code will be used as cut-and-paste input by different departments who don't need to know all the details presented in raw data.

I just did not want to distruct with too much info. So, I need a macro-free, one sell solution to the problem in my first post. If an easier solution could work - I would have used it.

I could not find any solution for now (easy or complex), that's why I called it a "Challenge" Thanks!

 

[tweedle]

Well, if you can't get past it, push comes to shove, you could put your analytics/concatenations on another sheet and just pull the aggregate codes back to your main sheet. It's a data explosion, but may be the option given the constraints.

 

[ukmikeb]

Mike, I need an expandable formula, that is I may have 150 triggers in first row and 150 corresponding values and need quickly expand the range to include all values.

The formula that you suggested, only in its array form (Ctrl+Shift+Enter), was my first try:

{=CONCATENATE(IF(A1:E1=1,A2:E2,""))}

this gives the desired range {"a","","c","","e"} but CONCATENATE does not accept arrays as an input...

Gene

I guessed that was probably what you were after and thought that the response would trigger an answer from one of the Wizards.

However if you look at post #8 in this thread -
http://www.mrexcel.com/forum/showthread.php?t=400486&highlight=array+string+concatenation

it should provide you with the answer.

Good luck and have a nice weekend.

Mike

 

[yytsunamiyy]

Try the following:

Tabelle1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>a</TD><TD>b</TD><TD>c</TD><TD>d</TD><TD>e</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>ace</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Formeln der Tabelle</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Zelle</TD><TD>Formel</TD></TR><TR><TD>A3</TD><TD>=CONCATENATE(IF($A$1=1,$A$2,""),IF($B$1=1,$B$2,""),IF($C$1=1,$C$2,""),IF($D$1=1,$D$2,""),IF($E$1=1,$E$2,""))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel Tabellen im Web darstellen - Excel Jeanie Html 4

The formula in A3 is easily generated with the following code:

Sub makeFormula()
Dim StrFormula As String
Dim StrRng As String
Dim rng As Range
Dim cll As Range
On Error Resume Next
StrRng = InputBox("Type the range the formula should apply to:")
If Not StrRng = "" Then
 
    Set rng = ActiveSheet.Range(StrRng)
    If Not rng Is Nothing Then
        If rng.Columns.Count <= 255 Then
            StrFormula = "=Concatenate("
            For Each cll In rng
                StrFormula = StrFormula & "IF(" & cll.Address & "=1," & cll.Offset(1, 0).Address & "," & """" & """" & "),"
            Next cll
            'Debug.Print StrFormula
            StrFormula = Left(StrFormula, Len(StrFormula) - 1)
            'Debug.Print StrFormula
            StrFormula = StrFormula & ")"
            'Debug.Print StrFormula
            ActiveCell.Formula = StrFormula
        Else
            MsgBox "You have choosen more than 255 columns. No Formula generated."
        End If
    Else
        MsgBox "You have not entered a valid range. No Formula generated."
    End If
End If
End Sub

The code obviously only needs to be run once, so no need to save as a macro-enabled wb. You can even put the code in your personal.xls and assign a keyboard shortcut to it if you are going to need to generate that formula more often.

This is just a proof-of-concept. Expand the code to your needs, adding inputs for values and triggers.