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 content is only available to members.

To access this content, 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",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)),"")&")"

=$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",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 so, the second half (in bold) can be shorter:

=$A2&"("&IF($B2="Y",SUBSTI

**IF($Q2="Y",INDEX($R$1:$V$1**,MATCH("Y",$R2:$V2,0)),"") &")"

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:$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

```
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..