Evening,
This one is a little over my headโฆ
I need to lookup a value in one column and if it exists in any one of 5 other columns, it needs to return the column to the right of where it is found.
I could use LOOKUP for one instance but donโt know how to do it for multiple columns?
LOOKUP( survey_branch_number , survey , branch_A_number , branch_A_assets )
I need to lookup:
[survey_branch_number]
In these columns:
[branch_A_number], [branch_B_number], [branch_C_number], [branch_D_number], [branch_E_number]
And return the corresponding:
[branch_A_assets], [branch_B_assets], [branch_C_assets], [branch_D_assets], [branch_E_assets]
And, as if this wasnโt tricky enough, the expression will also need to account for the following:
SELECT(
survey[branch_x_assets],
AND(
[select_asset_type] = [_THISROW].[select_asset_type],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
))
Sample of survey table below:
Thanks in advanceโฆ
IFS(
ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_A_number , branch_A_assets)),
LOOKUP(survey_branch_number , survey , branch_A_number , branch_A_assets),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_B_number , branch_B_assets)),
LOOKUP(survey_branch_number , survey , branch_B_number , branch_B_assets),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_C_number , branch_C_assets)),
LOOKUP(survey_branch_number , survey , branch_C_number , branch_C_assets),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_D_number , branch_D_assets)),
LOOKUP(survey_branch_number , survey , branch_D_number , branch_D_assets),
TRUE,LOOKUP(survey_branch_number , survey , branch_E_number , branch_E_assets)
)
)
)
)
Hi @LeventK,
Thank you very much for thatโฆ! You make that look easyโฆ!
Iโll give that a go right away and will report backโฆ
Cheersโฆ
This:
IFS(
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_A_number,
branch_A_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_A_number,
branch_A_assets
),
TRUE,
IFS(
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_B_number,
branch_B_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_B_number,
branch_B_assets
),
TRUE,
IFS(
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_C_number,
branch_C_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_C_number,
branch_C_assets
),
TRUE,
IFS(
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_D_number,
branch_D_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_D_number,
branch_D_assets
),
TRUE,
LOOKUP(
survey_branch_number,
survey,
branch_E_number,
branch_E_assets
)
)
)
)
)
simplifies to:
IFS(
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_A_number,
branch_A_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_A_number,
branch_A_assets
),
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_B_number,
branch_B_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_B_number,
branch_B_assets
),
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_C_number,
branch_C_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_C_number,
branch_C_assets
),
ISNOTBLANK(
LOOKUP(
survey_branch_number,
survey,
branch_D_number,
branch_D_assets
)
),
LOOKUP(
survey_branch_number,
survey,
branch_D_number,
branch_D_assets
),
TRUE,
LOOKUP(
survey_branch_number,
survey,
branch_E_number,
branch_E_assets
)
)
simplifies to:
ANY(
LIST(
LOOKUP(
survey_branch_number,
survey,
branch_A_number,
branch_A_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_B_number,
branch_B_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_C_number,
branch_C_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_D_number,
branch_D_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_E_number,
branch_E_assets
)
)
- LIST("")
)
Hi @Steve,
Thanks for coming inโฆ and for your time spent on thisโฆ!
Iโm busy trying to figure out where to insert your expression in my Initial Valueโฆ
Please could you advise?
ANY(
LIST(
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_A_numberโ,
โbranch_A_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_B_numberโ,
โbranch_B_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_C_numberโ,
โbranch_C_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_D_numberโ,
โbranch_D_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_E_numberโ,
โbranch_E_assetsโ
)
)
-LIST("")
)
This is a nonsense statement. The Initial value expression is an expression. I provided you with an expression. Put my expression in Initial valueโฆif thatโs actually where you want itโฆ
You havenโt explained (clearly) what you want to do with this list youโve asked us to generate for you. If you want the user to select one of the values from a dropdown menu, use what weโve provided as a columnโs Valid If expression. If you want the user to choose more than one value, make the column type EnumList.
Sorry, my misunderstanding @Steveโฆ I already have something in InitialValue and I didnโt realise this was to replace thatโฆ
IFS(
ISBLANK([survey_branch_number]),
ORDERBY(
SELECT(asset[id],
IN([id],
SORT(
UNIQUE(
SELECT(rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
))
+
SELECT(rf_design[Destination Asset ID],
AND(
[_THISROW-2].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[lcs] = [_THISROW-2].[LCS],
NOT(
[destination_asset_type]=โCabinetโ
)))
),FALSE
)),TRUE
),[asset_name]
),AND(
[survey_type] = โSingle Assetโ,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
)))>0
),LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
))),
โsurveyโ,"_ROWNUMBER",โsurvey_asset_idโ
),AND(
[survey_type] = โMultiple Assetsโ,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = โCAT6Aโ,
[select_asset_type] = โFibreโ,
[select_asset_type] = โJumperโ,
[select_asset_type] = โPowerโ
))))>0
),LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = โCAT6Aโ,
[select_asset_type] = โFibreโ,
[select_asset_type] = โJumperโ,
[select_asset_type] = โPowerโ
)))),
โsurveyโ,"_ROWNUMBER",โsurvey_asset_idโ
)
)-[branch_A_assets]
-[branch_B_assets]
-[branch_C_assets]
-[branch_D_assets]
-[branch_E_assets]
I have your expression in a [test_column] ValidIf and Iโm getting an error:
Column Type โEnumโ has an invalid data validation constraint
I tried this ValidIf expression:
ANY(
LIST(
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_A_numberโ,
โbranch_A_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_B_numberโ,
โbranch_B_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_C_numberโ,
โbranch_C_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_D_numberโ,
โbranch_D_assetsโ
),
LOOKUP(
โsurvey_branch_numberโ,
โsurveyโ,
โbranch_E_numberโ,
โbranch_E_assetsโ
)
)
- LIST("")
)
I also tried this ValidIf but it yeilds the same error:
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_A_numberโ, โbranch_A_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_A_numberโ, โbranch_A_assetsโ),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_B_numberโ, โbranch_B_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_B_numberโ, โbranch_B_assetsโ),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_C_numberโ, โbranch_C_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_C_numberโ, โbranch_C_assetsโ),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_D_numberโ, โbranch_D_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_D_numberโ, โbranch_D_assetsโ),
TRUE,
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_E_numberโ, โbranch_E_assetsโ)
)
)
)
)
Iโve taken a look here:
and noted this:
Make sure your Enum and/or EnumList columns have all possible values explicitly defined, OR make sure Allow other values is ON .
Iโve gone through all of the columns involved in the lookup and confirmed that that all have โallow other valuesโ set to ON.
I also tried wrapping the lookuo in a SPLIT() but got a NUL result:
I created a list to help troubleshoot:
UNIQUE(
SELECT(
survey[branch_A_assets],
[branch_A_number] > 0
)
)
And I get a โThis entry is invalidโ error where an item is a coma separated value:
But I donโt get the โThis entry is invalidโ error where an item is a not coma separated value:
Could this be related to main issue?
Column Type โEnumโ has an invalid data validation constraint
Any advice would be appreciatedโฆ
Remove data validation for the spreadsheet column.
Hi @Steve
Any chance it has something to do with this odd behaviour?
I created a list to help troubleshoot:
UNIQUE(
SELECT(
survey[branch_A_assets],
[branch_A_number] > 0
)
)
And I get a โThis entry is invalidโ error where an item is a coma separated value:
But I donโt get the โThis entry is invalidโ error where an item is a not coma separated value:
Try this:
SORT(
UNIQUE(
SPLIT(
(
LIST(
LOOKUP(
survey_branch_number,
survey,
branch_A_number,
branch_A_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_B_number,
branch_B_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_C_number,
branch_C_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_D_number,
branch_D_assets
),
LOOKUP(
survey_branch_number,
survey,
branch_E_number,
branch_E_assets
)
)
- LIST("")
),
" , "
)
)
)
Hi @Steve,
Thank youโฆ I did as suggested above and not getting the error any moreโฆ and moving forward againโฆ
Cheersโฆ
Hi @LeventK,
Thanks for your time.
Iโd be very grateful if you could spare a little more to help me apply your expression?
It feels like it belongs in this ValidIf in [asset_name] EnumList. BAseType Ref, (asset table)
Iโve tried it in a coupe of places but canโt get it to work.
Iโve also tried it on itโs own as an Initial Value
If you do have time, hereโs how the current ValidIf flows:
IFS(
ISBLANK([survey_branch_number]),
ORDERBY(
SELECT(asset[id],
IN([id],
SORT(
UNIQUE(
SELECT(rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
))
+
SELECT(rf_design[Destination Asset ID],
AND(
[_THISROW-2].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[lcs] = [_THISROW-2].[LCS],
NOT(
[destination_asset_type]=โCabinetโ
)))
),FALSE
)),TRUE
),[asset_name]
),
AND(
[survey_type] = โSingle Assetโ,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
)))>0
),
LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
))),
โsurveyโ,"_ROWNUMBER",โsurvey_asset_idโ
),
AND(
[survey_type] = โMultiple Assetsโ,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = โCAT6Aโ,
[select_asset_type] = โFibreโ,
[select_asset_type] = โJumperโ,
[select_asset_type] = โPowerโ
))))>0
),
LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = โCAT6Aโ,
[select_asset_type] = โFibreโ,
[select_asset_type] = โJumperโ,
[select_asset_type] = โPowerโ
)))),
โsurveyโ,"_ROWNUMBER",โsurvey_asset_idโ
)
)
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_A_numberโ, โbranch_A_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_A_numberโ, โbranch_A_assetsโ),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_B_numberโ, โbranch_B_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_B_numberโ, โbranch_B_assetsโ),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_C_numberโ, โbranch_C_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_C_numberโ, โbranch_C_assetsโ),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_D_numberโ, โbranch_D_assetsโ)),
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_D_numberโ, โbranch_D_assetsโ),
TRUE,
LOOKUP(โsurvey_branch_numberโ, โsurveyโ,
โbranch_E_numberโ, โbranch_E_assetsโ)
)
)
)
)
Try:
UNIQUE(
SORT(
SPLIT(
(
SELECT(
survey[branch_A_assets],
AND(
([_THISROW].[survey_branch_number] = [branch_A_number]),
([_THISROW].[select_asset_type] = [select_asset_type]),
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs])
)
)
+ SELECT(
survey[branch_B_assets],
AND(
([_THISROW].[survey_branch_number] = [branch_B_number]),
([_THISROW].[select_asset_type] = [select_asset_type]),
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs])
)
)
...
+ SELECT(
survey[branch_E_assets],
AND(
([_THISROW].[survey_branch_number] = [branch_E_number]),
([_THISROW].[select_asset_type] = [select_asset_type]),
([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
([_THISROW].[LCS] = [lcs])
)
)
),
" , "
)
)
)
Hi @Steve,
I guess once I get the other bit working Iโll apply thisโฆ I can see what itโs going to doโฆ Cheersโฆ
Hi @Steve,
I donโt use any data validation on the excel sheet but I went in anyway and cleared all just in caseโฆ Still getting the same errorโฆ
I did notice that the number columns in the spreadsheet were formatted to โtextโ so Iโve changed them to numbers and am busy testing nowโฆ
Corrected formatting in sheet and testedโฆ Still getting the same errorโฆ
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |