Substituting text contents with subsequential numbers

[pajonate]

Hi all,

can't figure this out no matter what.

I have a field that has text inside as either words in one line or every word is in separate line (it won't be both ways, but if I can get solution for one of the ways I can live with that, and adjust my data). Total # of lines or words will be always 7 .

I have second field which is starting number for substituting

I have third field which is ending number for substituting (this might be an extra, but I have it)

Sample data:

"John John x x x Simon x" OR

"John

John

x

x

x

Simon

x"

2nd field = "5"

3rd field = "6"

I need probably a custom recursive function of same kind to replace all "John" values staring with the starting number and ending with ending number.

At the end i wish to end up with value in the 4th field as "5 6 x x x Simon x"

Next record might have:

"John John John x x John Simon" OR

"John

John

John

x

x

John

Simon"

2nd field = "7"

3rd field = "10"

Result: "7 8 9 x x 10 Simon"

Any ideas? I can't have scripts for this, just calculation of some kind.

Thank you.

[pajonate]

Since I was the one post it, and had trouble with it, I am now posting the solution I came with for this issue.

My solution works for fields where data is divided by return characters.

Field "ListNms" (7 rows)

"John

John

x

Angela

x

John

Simon"

Field "ListNmsSt#" starting number: "10"

Result expected in a separate field:

"10

11

x

Angela

x

12

Simon"

I manage to do it by creating custom function named "ChgWordOccWNums John" with parameters _list and "VstartNm"

the calc part for this cust. function is:

-----------------

Let ( [

vrep=Replace ( _list ; Position ( _list ; "John" ; 1 ; 1) ; 4 ; VstartNum );

vstartNum=vstartNum+1

];

If ( PatternCount ( _list ; "John" )>0 ;

RightValues(vrep & ¶ & ChgWordOccWNums Vac(vrep; VstartNum);7);

"" ))

------------

vrep is a variable that replaces first "John" occurrence with VstartNm (in our case 10. than increases the VstartNm by 1, and reapplies the same function to the rest of the text but with increased starting number for the next "John" occurrence. The result is a long string of repetitions of our first field, where in each one there is a one "John" occurrence less. Because I need the final 7 rows of that string I used RightValues function. To see what I am talking about substitute my "if" statement with this one:

------------

If ( PatternCount ( _list ; "John" )>0 ;

vrep & ¶ & ChgWordOccWNums Vac(vrep; VstartNum);

"" ))

------------

Make one field "ResultJ" as calculation and set the cust function as ChgWordOccWNums John (ListNms; ListNmsSt#) and the result will be there.

What I had as additional problem was how to apply this to the other names. i am not that good in custom functions, so when I tried to reapply this function for "Angela" inside "John"s function I got stuck. So, what i did was I made 2 new fields "ResultA" "NmsAStNm" (strating number for "Angela"s occurrences) and new custom function "ChgWordOccWNums Angela". But, my new cust function doesn't look at the ListNms field, but looks at ResultJ field where all John occurrences are changed already.

Here is the cust function for Angela

Let ( [

srep=Replace ( LeftValues ( _list ; 7 ) ; Position ( _list ; "Angela" ; 1 ; 1) ; 6 ; SstartNum );

SstartNum=SstartNum+1

];

If ( PatternCount ( _list ; "Angela" )>0 ;

RightValues(srep & ¶ & ChgWordOccWNums Angela(srep; SstartNum);8);

"" ))

Unfortunately, you have to do this for all the words you want to substitute with consequential numbers.

I'll add the file later when I do necessary adjustments.

Pavle Ancevski

Chief Office Administrator

Local 6, Hotel, Restaurant and Club

Employees & Bartenders Union

[email protected]

[David Jondreau]

How about...

NameReplace(valueList; Name; Num ; Counter)

//Counter should always be 1

Let([

End = ValueCount(valueList);

thisVal = GetValue( valueList; counter) ;

notLastValue = counter < End

];

//Checks for matching name.

Case(

//if name matches

thisVal = Name ;

// return number and check for last value

Num &

//if not last value, adds pilcrow and recurse

Case( notLastValue; "¶" & NameReplace(valueList; Name; Num+1 ; Counter + 1))

//else if name doesn't match, return Value and check for last value

; thisVal &

//if not last value recurse

Case( notLastValue; "¶" & NameReplace(valueList; Name; Num ; Counter + 1))

))

You can put in any name you want for the Name parameter; John, Angela, whatever.

Edited October 4, 2007 by Guest

[pajonate]

That's what I like about this forum, people are making solutions for problems in a different ways. David, thanks for posting your way of handling the problem.

I also adjusted my solution so the custom function is now:

Let ( [

vrep=Replace ( _list ; Position ( _list ; ChgWord ; 1 ; 1) ; Length ( ChgWord ) ; F1startNum );

F1startNum=F1startNum+1

];

If ( PatternCount ( _list ; ChgWord )>0 ;

RightValues(vrep & ¶ & ChgWordOccWNums F1(vrep; F1startNum; ChgWord);7);

"" ))

I added a (global) field (ChgWord) and parameter (ChgWord) in the custom function, so user can change the value in the field and that value will be evaluated.

Similar to David's way, but I am using only 3 parameters and not 4 (Counter). I haven't tried to adjust his solution for, let say 2 or 3 words you want to change into numbers. Maybe that can be achieved easier there than in mine.

Again, thanks for sharing!!!

P.S. I have attached my file for whoever wants it.

Iterative_function_Pajo.zip

Edited October 4, 2007 by Guest