Solved

Tricky lookup and concatenate formula

Posted on 2016-10-19
6
73 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

726 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