If Function based on Split

Question

I have 20 columns of data that has an amortisation formula in it.

I need to check if the amortisation meets two criteria.

Based on a split of row 15 I need it to check that both items (item refers to row 1) say they are include (row 3) and that both items have the same value (row 13).

Now the split could be item 1,2,4, or 1,2 or 1,5 etc. any combination based on the user inputs.

If they are not included state "Check" if they are state "" in row 18.

I know how to do an if function, but not based on a split.

I know how to do the split as part of a function through guidance I received here but I cant work out how to add an IF into a function.

My functions are used in rows 13 and 16 Function Question

My attempt:

Function checkIf(sItemIDX As String, cCostToAmortize As Currency, Optional lItemRow As Long = 1) As String
Dim v
Dim P As Currency
Application.Volatile
For Each v In Split(sItemIDX, ",")
    'Assuming Item List starts in column B
    ' But could use other methods to locate table
    P = P + Cells(lItemRow + 1, v + 1)
Next v

checkIf = IF(cCostToAmortize = Cells(lItemRow + 1, Application.Caller.Column) then IF(cCostToAmortize = Cells(lItemRow + 1, Application.Caller.Column) Then "" Else "Check"
End If

End Function

How I would like the outcome to look (the colours are there to make it easier to explain).

In row 18 I want to do a check, in my head this would be done in the following steps.

1. Split row 15, each number within row 15 represents the item (row 1). In the final sheet there could be 20 numbers in any cell in row 15.

2. Based on those numbers a If statement will be completed to check the included choice (row 3) all should be yes if entered into row 15, example B15 says item 1 and 2 so both Item 1 (b) and Item 2 (c) on row 3 should say yes. As this is not the case it should return "Check" in row 18 column B and C.

3. Next stage if these were both yes as is the case on items 4 (e) and items 5 (f) then it checks to make sure both the tool cost to amortise is the same value row 13. In this example they are not (highlighted in red) so again it puts "Check" in row 18 column E and F.

If it doesnt have anything in row 15 then it is fine so it would be "", and using columns B and C again if they both said yes in row 3 then that would return a "" to.

Note in the final version Row 15 could contain 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 in each cell on row 15 which would mean they would all need to be compared.

Image to check.

Formula image

Answer 1

If I understand what you want correctly, and you have Excel for windows 2013+, you can do this with worksheet functions.

For example, to Split the comma separated data into separate array elements, you can use:

=FILTERXML("<t><s>" & SUBSTITUTE(B15,",","</s><s>") & "</s></t>","//s")

(substitute B12 for B15 where appropriate)

So, your formulas could be as follows:

B13:  =IFERROR(SUM(INDEX(B$1:B$11,FILTERXML("<t><s>" & SUBSTITUTE(B12,",","</s><s>") & "</s></t>","//s"))),0)

B14:  =SUM(B5:B11,-B13)

B16: =IFERROR(B13/SUM(INDEX($B$2:$U$2,FILTERXML("<t><s>" & SUBSTITUTE(B15,",","</s><s>") & "</s></t>","//s")))*B2,0)

B17:  =B14+B16

B18:
=IFERROR(
        IF(AND(
                     AND(INDEX($B$3:$U$3,FILTERXML("<t><s>" & SUBSTITUTE(B15,",","</s><s>") & "</s></t>","//s"))="Yes"),
                     AND(INDEX($B$13:$U$13,FILTERXML("<t><s>" & SUBSTITUTE(B15,",","</s><s>") & "</s></t>","//s"))=B13)),
             "","Check"),
"")

In the screenshot below, "Check" appears because of the No on the Item 2 Included row.

If you change C3 to Yes, the check in row 18 will disappear.

The formulas will handle 20 columns of data. If you have more (or fewer), change the references to Bn:Un in the various formulas accordingly.