FINDITEM multiple columns

khoonks

Hello All,

I was wondering if there is a way we can use FINDITEM to find a value from multiple columns. 

The way FINDITEM works is by comparing a list with a specific line item (column). However, in this case, I need to be able to find an item that may be in column 1 or column 2 or 3. Is there a way to achieve this? Any other option if FINDITEM does not work?

ankit_cheeni

Can we just do IF ISNOTBLANK(FINDITEM(X, Col A)) then FINDITEM(X, Col A) ELSE IF ISNOTBLANK(FINDITEM(X, Col ) then FINDITEM(X, Col ELSE ...., X being the list you are finding in.

Create separate line items for each FINDITEM, i wrote it all together just for explanation

rob_marshall

@khoonks 

 

I representation of the data might help here just so we understand the requirements and be able to help.  I wouldn't suggest doing what @ankit_cheeni stated as you are doing the finditem() multiple times (doing a finditem for the isnotblank and then doing it again).  Instead, I would suggest breaking it up, creating a line item for each column and do the single finditem() with the formula being:  if isnotblank(col A) then finditem() else blank.  Yes, it is more cells, but it is far more performant.

 

Rob

MarkWarren

You would need a line item to check for which of the line items is not blank in your preferred order, then reference that in the FINDITEM. But why would there be three different values for 1 FINDITEM?

ankit_cheeni

@rob_marshall  I did recommend creating separate line items for each FINDITEM, which would replace all FINDITEMs in the formula. Not sure how is that different from what you are suggesting. 

 

Also, if you do 'if isnotblank(col A) then finditem() else blank' you are assuming that if there is a text, it will be present in the list and then you'd create another IF THEN ELSE to consolidate all 3 columns in one. It would be more inclusive if you do separate FINDITEMs and check on the result of finditem in the final line. 

rob_marshall

@ankit_cheeni 

 

I must have missed the last sentence and just looked at the formula you wrote as an example.  As for the isnotblank bit, it depends on the data as to which one to put first for the THEN vs. the ELSE (basically which criteria happens most often should be first).  As for separating the Finditems(), that is exactly what I said.

khoonks

Thanks all for the response.

The reason I wanted to have this multiple find item is to map is to solve this specific issue.

I have a module where I have the GL Account and the columns have different levels. It is a ragged hierarchy so we are not sure which column the parent is so i am trying to do a multi finditem. Any other solution would be appreciated. 

The reason i need it is becuase actuals come below the budget account and there is no specific rule regarding how many levels below. some may be one level, which is easier because the parent would be the account i am looking for, however, in some cases, the parent's parent would be the one i need, that is why i was trying to do a finditem with or so that it can check col1, or col2 etc.

alexpavel

@khoonks :

The purpose of FINDITEM function is to transform a text into a list element. It is up to the model builder what TEXT to use as a parameter in the FINDITEM function. 

 

If on the same row of your data, you have values only in one of the 3 columns: Column1 or Column2 or Column3, you can use the concatenation:

Finditem( List, Column1 & Column2 & Column3 )

 

If on the same row, you can have values in more than one column. then you will need to create a priority between column1, column2 or column 3 to choose what is the value to be used in Finditem. 

 

Hope it helps

Alex