trouble compairing char in row with next row 1

[Tbirdbully]

Thanks in advance!
I am using CR XI

I am having trouble with a formula to compair the first 4 char with the next row first 4 char.

I have a SWCODE field that has 4 or 5 char in it.
0400
0400a
0400b
0401
0402
0402a

My goal is to create a formula to compair the left 4 char to the row below it to see if they match and if so I want to print "Additional Products"

so it would be 0400 Aditional Products
0400a
0400b
0401 Aditional Products

I had thought maybe this might work but I can't tell it to look at the next row of excel:
If Left({Sheet1_.SW Dir Code},4)= (Left({Sheet1_.SW Dir Code},4)+1)

the +1 dosn't work

any help?

 

[synapsevampire]

Try:

if left({table.field},4) <> previous(left({table.field},4))
and
left({table.field},4) = next(left({table.field},4)) then
"Additional Products"

Your example shows that you want this ONLY when it also has changed, not "My goal is to create a formula to compair the left 4 char to the row below it to see if they match and if so I want to print "Additional Products""

-k

 

[lbass]

If you insert a group on {@left4}:

left({table.field},4)

...then you could add a formula like this for the Additional products label:

if distinctcount({table.field}, {@;eft4}) > 1 then "Additional Products"

This will only show up if products with an extension (a,b) are nested within the group.

-LB

 

[Tbirdbully]

lbass
I tried to make a group on {@left4}

how do you do this? I went to insert group>customize group name by formula> left({table.field},4)

Is there a way to change the name to {@left4} because its name is the same as my swcode field name shown below which gives the error msg that {Sheet1_.SW Dir Code} is not a field.



if distinctcount({Sheet1_.SW Dir Code},GroupName {Sheet1_.SW Dir Code})) > 1
then "Additional Products"


so my question is how do u "insert a group on {@left4}:

 

[Tbirdbully]

I tried just creating a formula called {@left4} and put in left({table.field},4)



if distinctcount({Sheet1_.SW Dir Code},{@left4}) > 1
then "Additional Products"

when I try this I get "there must be a group that matches this field(({Sheet1_.SW Dir Code},{@left4}))

I will try vampire's method now, I just thought the distintcount function is a good technique.


Thanks to both of you!

 

[Tbirdbully]

I didnt have any luck with synapsevampire method ethier.
When I try the formula below I get the error "A field is required here" for previous(left({Sheet1_.SW Dir Code},4))

I guess it does not like the Left after previous or the left after next.



If left({Sheet1_.SW Dir Code},4) <> previous(left({Sheet1_.SW Dir Code},4))
and
left({Sheet1_.SW Dir Code},4) = next(left({Sheet1_.SW Dir Code},4)) then
"Additional Products"

any help?
Thanks

 

[synapsevampire]

Sorry, tht should have been:

left(previous({Sheet1_.SW Dir Code}),4)

-k

 

[lbass]

After creating the formula {@left4}, you would go to the toolbar->insert->group and choose {@left4} to group on. Then create the second formula I mentioned in my first post and drag that formula into the group header. You would then place the field {Sheet1_.SW Dir Code} in the detail section, under the formula for additional products. I should have added that you would then want to go to the section expert (report->section expert) and select the details section->suppress->x+2 and enter:

{Sheet1_.SW Dir Code} = {@left4}

This will suppress the 'parent' code in the detail section, since it is already displayed in the group header.

-LB