multi-row formula, Row - 1 , need to make that dynamic somehow

[wonka1234]

Hi

before multi row tool:

FileName	Name	Value
Book (3)	Amt	2
	Amount
	Process

after:

FileName	Name	Value
Book (3)	Amt	2
File Name :  does not contain field :	Amount
File Name :  does not contain field :	Process

multi row formula:

if isnull([FileName]) then "File Name : " + [FileName] + " does not contain field : " else [FileName] endif

I know I could do this:

if isnull([FileName]) then "File Name : " + [Row-1:FileName] + " does not contain field : " else [FileName] endif

however it will not work. I need to be able to do -2 but in a dynamic fashion. As There could be many rows before row 1 here.. 

if I did -1 it will look like :

FileName	Name	Value
Book (3)	Amt	2
File Name : Book (3) does not contain field :	Amount
File Name : File Name : Book (3) does not contain field :  does not contain field :	Process

any help would be appreciated.

[ed_hayter]

I've added another conditon to your multi-row, assuming the names are in a consistent order,

if isnull([FileName]) AND [Name] = "Amount"
then "File Name : " + [Row-1:FileName] + " does not contain field : " elseif
isnull([FileName]) AND [Name] = "Process"
then "File Name : " + [Row-2:FileName] + " does not contain field : " else
[FileName] endif

I've attached the workbook as well.

If your Name column is not in a consistent order you can make a record ID for every 3 rows (https://www.thedataschool.co.uk/henry-mak/modulo-increment-number/) and then assign the names an order rank (amt = 1, Amount = 2, Process = 3) and sort by the recordID, then the measure rank to ensure the correct order

[binuacs · Accepted solution]

@wonka1234 one way of doing this

[wonka1234]

@ed_hayter thanks but my "Name" column will never be the same.. I cant really hardcode this :( also I dont know how many differnet names there will be..

[SPetrie]

Another possible method is using regex in the multi-row to test for and replace the name as appropriate.

Im sure my regex could be improved upon, but it shows how the method could work.

if isnull([FileName]) and contains([Row-1:FileName],":") then REGEX_Replace([Row-1:FileName],"
.+:(.+) does.+","File Name : $1 does not contain field :") elseif isnull([FileName]) and !contains([Row-1:FileName],":") then "File Name : "+[Row-1:FileName]+" does not contain field :" else [FileName] endif