Tips and Tricks: Find Gaps in a series of numbers

Find Gaps in a series of numbers

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3989
Printed Date: 02 May 2025 at 2:06pm

Topic: Find Gaps in a series of numbers

Posted By: Bryan
Subject: Find Gaps in a series of numbers
Date Posted: 14 Aug 2008 at 1:06pm

Hi,

Newbie here, and just learning Crystal. I am looking for a way to find the gaps in a series of UPC'S. I have returned a list of the 7th - 11th characters in our UPC and now I need to find the gaps. Can anyone help? Thanks

Replies:

Posted By: rahulwalawalkar
Date Posted: 15 Aug 2008 at 2:05am

Hi,

Can you post some sample data.

Cheers

Rahul

Posted By: Bryan
Date Posted: 15 Aug 2008 at 9:47am

Thanks for the response, and sorry about not being clear. Below is some sample data. I have insertes our item #, UPC # (A unique 12 digit number), and then returned only places 7,8,9,10,11 in the seq clumn. There are about 2561 total UPCs, and the 7-11 numbers are the unique value. the 664232 is a prefix, and the last digit is a check digit. So is there a way to find the gaps between each UPC number? Thanks and I apoligize for not explaining it so well. I am pretty new to this stuff. THanks

Item No UPC Cd @UPC Seq

664232041074

F10200 664232041074 4107

664232041943

95646-A/I 664232041943 4194

664232041981

95646-C/B 664232041981 4198

664232042728

D1246-O/W 664232042728 4278

664232043428

D1250F-O/W 664232043428 4342

Posted By: rahulwalawalkar
Date Posted: 18 Aug 2008 at 4:38am

Hi,

What I understand is are you trying to find our missing UPC numbers when you mean by Gaps between UPC numbers.

so after 4107 should be 4108 .4109....... and so on

let me know if my assumption is correct,if not please explain a bit more...

Cheers

Rahul

Posted By: Bryan
Date Posted: 18 Aug 2008 at 9:19am

That is correct. Thanks

Posted By: rahulwalawalkar
Date Posted: 18 Aug 2008 at 10:11am

Hi,

Create a details section below main details section ,then create a formula and place the code below in that formula

if mailto:%7b@UPC - {@UPC Seq} + 1 = Next ( mailto:%7b@UPC - {@UPC Seq}) then ""

else

if mailto:%7b@UPC - {@UPC Seq}+2 = Next ( mailto:%7b@UPC - {@UPC Seq}) then

"Missing " + totext( mailto:%7b@UPC - {@UPC Seq}+1,"0000")

else

"Missing " + totext( mailto:%7b@UPC - {@UPC Seq}+1,"0000") + " to " +
totext(next( mailto:%7b@UPC - {@UPC Seq})-1,"0000")

The above formula was taken from the following website ,I have modified to suit your needs

http://www.crystalkeen.com/articles/crystalreports/missingdata.htm - http://www.crystalkeen.com/articles/crystalreports/missingdata.htm

Once done place the formula in new deatils section .

Cheers

Rahul

Posted By: Bryan
Date Posted: 18 Aug 2008 at 2:08pm

That was awesome! Worked like a charm. Thanks Rahul