route217
asked on
Tricky lookup and concatenate formula
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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",SUBSTI TUTE(IF(C2 ="","",C$1 &",")&IF(D 2="","",D$ 1&",")&IF( E2="","",E $1&",")&IF (F2="","", F$1&",")&I F(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(N 2="","",N$ 1&",")&IF( O2="","",O $1&",")&IF (P2="","", P$1&",")&I F($Q2="Y", "/",""),", ","",COUNT A(C2:P2)), "")&IF($Q2 ="Y",SUBST ITUTE(IF(R 2="","",R$ 1&",")&IF( S2="","",S $1&",")&IF (T2="","", T$1&",")&I F(U2="","" ,U$1&",")& IF(V2=""," ",V$1&",") ,",","",CO UNTA(R2:V2 )),"")&")"
=$A2&"("&IF($B2="Y",SUBSTI
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",SUBSTI TUTE(IF(C2 ="","",C$1 &",")&IF(D 2="","",D$ 1&",")&IF( E2="","",E $1&",")&IF (F2="","", F$1&",")&I F(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(N 2="","",N$ 1&",")&IF( O2="","",O $1&",")&IF (P2="","", P$1&",")&I F($Q2="Y", "/",""),", ","",COUNT A(C2:P2)), "")&IF($Q2="Y",INDEX($R$1:$V$1 ,MATCH("Y" ,$R2:$V2,0 )),"")&")"
If so, the second half (in bold) can be shorter:
=$A2&"("&IF($B2="Y",SUBSTI
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
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
As I thought, it can be done with a UDF; see attached. The script of the Function is below:
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:$V 2,$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
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
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,
$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
ASKER
Trying find that extra comma..