Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Tricky lookup and concatenate formula

Posted on 2016-10-19
6
Medium Priority
?
81 Views
Last Modified: 2016-10-20
Hi Experts using excel 2010

See attached workbook
Need a formula that does the following look the I'd in column A and if we have a Y in column b then find the corresponding Y in range c2:p5 and also if we have a Y in column q and a Y in second data range r2:v5 then return back results as shown in column x.
Book2.xlsx
0
Comment
Question by:route217
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 41849854
Almost there with this formula:

=$A2&"("&IF($B2="Y",IF(C2="","",C$1&",")&IF(D2="","",D$1&",")&IF(E2="","",E$1&",")&IF(F2="","",F$1&",")&IF(G2="","",G$1&",")&IF(H2="","",H$1&",")&IF(I2="","",I$1&",")&IF(J2="","",J$1&",")&IF(K2="","",K$1&",")&IF(L2="","",L$1&",")&IF(M2="","",M$1&",")&IF(N2="","",N$1&",")&IF(O2="","",O$1&",")&IF(P2="","",P$1&",")&"/","")&IF($Q2="Y",IF(R2="","",R$1&",")&IF(S2="","",S$1&",")&IF(T2="","",T$1&",")&IF(U2="","",U$1&",")&IF(V2="","",V$1&","),"")&")"

Bit lengthy and it needs tweaking for extra comma after last entry from Key1 range and the "/" between Key1 and Key2 ranges.
0
 

Author Comment

by:route217
ID: 41849939
Thanks for the feedback...excellent
Trying find that extra comma..
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41849985
Made even longer in the process of dealing with the surplus comma after the last entry from each key range and the requirement for "/" or not:

=$A2&"("&IF($B2="Y",SUBSTITUTE(IF(C2="","",C$1&",")&IF(D2="","",D$1&",")&IF(E2="","",E$1&",")&IF(F2="","",F$1&",")&IF(G2="","",G$1&",")&IF(H2="","",H$1&",")&IF(I2="","",I$1&",")&IF(J2="","",J$1&",")&IF(K2="","",K$1&",")&IF(L2="","",L$1&",")&IF(M2="","",M$1&",")&IF(N2="","",N$1&",")&IF(O2="","",O$1&",")&IF(P2="","",P$1&",")&IF($Q2="Y","/",""),",","",COUNTA(C2:P2)),"")&IF($Q2="Y",SUBSTITUTE(IF(R2="","",R$1&",")&IF(S2="","",S$1&",")&IF(T2="","",T$1&",")&IF(U2="","",U$1&",")&IF(V2="","",V$1&","),",","",COUNTA(R2:V2)),"")&")"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41850005
For the key2 values will there be multiple entries or will it always be only one of the 5 issues?

If so, the second half (in bold) can be shorter:

=$A2&"("&IF($B2="Y",SUBSTITUTE(IF(C2="","",C$1&",")&IF(D2="","",D$1&",")&IF(E2="","",E$1&",")&IF(F2="","",F$1&",")&IF(G2="","",G$1&",")&IF(H2="","",H$1&",")&IF(I2="","",I$1&",")&IF(J2="","",J$1&",")&IF(K2="","",K$1&",")&IF(L2="","",L$1&",")&IF(M2="","",M$1&",")&IF(N2="","",N$1&",")&IF(O2="","",O$1&",")&IF(P2="","",P$1&",")&IF($Q2="Y","/",""),",","",COUNTA(C2:P2)),"")&IF($Q2="Y",INDEX($R$1:$V$1,MATCH("Y",$R2:$V2,0)),"")&")"
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41852193
I have been looking at this again to see if I can come up with anything shorter.

Is there a maximum or fixed number of Y entries in Key1 range? With your sample, both entries where there are entries in the Key1 range, there are 3 entries. If we knew that there would only be 3 entries then it could be reduced to checking three ranges based on the placing of the Y.

Alternatively, it could be done with a User Defined Function (UDF). A UDF is basically a bit of VBA that is used in the same way as a formula. This would mean changing the workbook to an xlsm though with the subsequent allowing of macros which that would enforce.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41852378
As I thought, it can be done with a UDF; see attached. The script of the Function is below:

Function CheckKey(Name As Range, Ch1 As Range, Key1 As Range, Res1 As Range, Ch2 As Range, Key2 As Range, Res2 As Range)

If Ch1 = "Y" Then
    Str1 = ""
    Count1 = 0
    For Each Cell In Key1
        Count1 = Count1 + 1
    Next Cell
    
    For C = 1 To Count1
        Check = WorksheetFunction.Index(Key1, 1, C)
        If Check = "Y" Then
        Result = WorksheetFunction.Index(Res1, 1, C) & Chr(44)
        Else
        Result = ""
        End If
        Str1 = Str1 & Result
        Result = ""
    Next C
        Str1 = Left(Str1, Len(Str1) - 1)
        If Ch2 = "Y" Then Str1 = Str1 & Chr(47)
End If

If Ch2 = "Y" Then
    Str2 = ""
    Count2 = 0
    For Each Cell In Key2
        Count2 = Count2 + 1
    Next Cell
    
    For C = 1 To Count2
        Check = WorksheetFunction.Index(Key2, 1, C)
        If Check = "Y" Then
        Result = WorksheetFunction.Index(Res2, 1, C) & Chr(44)
        Else
        Result = ""
        End If
        Str2 = Str2 & Result
        Result = ""
    Next C
    Str2 = Left(Str2, Len(Str2) - 1)
End If

CheckKey = Name & "(" & Str1 & Str2 & ")"

End Function

Open in new window

It has several input values, they are listed below with the equivalent description and range in your sample workbook:

Name As Range - The name to be included at start of final result, column A (ID)
Ch1 As Range - The cell to be checked for Key1 values, column B (Key1)
Key1 As Range - The range of cells containing Y as a result of Key1, columns C to P
Res1 As Range - The range of cells containing the result of Key1, columns C to P (1 to 14)
Ch2 As Range - The cell to be checked for Key2 values, column Q (Key2)
Key2 As Range- The range of cells containing Y as a result of Key2, columns R to V
Res2 As Range - The range of cells containing the result of Key2, columns R to V (Issue 1 to 5)

It is used in the spreadsheet like a formula with the following syntax:

=CheckKey($A2,$B2,$C2:$P2,$C$1:$P$1,$Q2,$R2:$V2,$R$1:$V$1)
$A2 = Name
$B2 = Ch1
$C2:$P2 = Key1
$C$1:$P$1 = Res1
$Q2 = Ch2
$R2:$V2 = Key2
$R$1:$V$1 = Res2

Note the $ locking the row for Res1 and Res2; this will ensure the results are still taken from row 1 when the formula is copied down to more rows.

Any further questions, please let us know.

Thanks
Rob H
Lookup-UDF.xlsm
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question