Find a break in sequential number

[Guest]

I have a spreadsheet that gets populated from an Access database. Once it is
in Excel, I want to look at a column that contains sequential numbers. THere
will be breaks in these numbers. I want to be able to put a blank line
between these breaks and then total 2 current columns.

ANy insights would be appreciated.

 

[Guest]

You might be able to use automatic subtotals.....

Example:
With
A1:A100 containing a list of sequential values, including some breaks in the
sequences, with a column heading in A1 (assuming MyList)

Try this:
Insert a column before Col_A
(column A has moved one column to the right)
A1: GapFlag
A2: FALSE
A3: =IF((B3-B2)>1,NOT(A2),A2)
copy A3 down as far as needed

Then, from the Excel main menu:
<data><subtotals>
At each change in: GapFlag
Use function: Sum
Add subtotal to: MyList
Click the [OK] button

That will insert a row at each break in the numeric sequence and create a
subtotal for the sequential numbers.

In your situation, you could subtotal on whatever columns you like.

Is that something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

I did this and all I get is False all the way down. see below I am cuer off
of the Line. field

GAPFLAG Line . Store . Ref . Debit Credit
FALSE 1557 130598 $1,200.00
FALSE 1558 130598 ($1,200.00)
FALSE 1570 1485501 $133.00
FALSE 1571 1490140 $180.00
FALSE 1572 1485501 ($133.00)
FALSE 1573 1490140 ($180.00)
FALSE 1672 4766 7669284 $180.00
FALSE 1675 4769 7698692 $37.00
FALSE 1679 4775 7751325 $7.50
FALSE 1680 4775 7751366 $8.50

--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

You might be able to use automatic subtotals.....

Example:
With
A1:A100 containing a list of sequential values, including some breaks in the
sequences, with a column heading in A1 (assuming MyList)

Try this:
Insert a column before Col_A
(column A has moved one column to the right)
A1: GapFlag
A2: FALSE
A3: =IF((B3-B2)>1,NOT(A2),A2)
copy A3 down as far as needed

Then, from the Excel main menu:
<data><subtotals>
At each change in: GapFlag
Use function: Sum
Add subtotal to: MyList
Click the [OK] button

That will insert a row at each break in the numeric sequence and create a
subtotal for the sequential numbers.

In your situation, you could subtotal on whatever columns you like.

Is that something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP

I have a spreadsheet that gets populated from an Access database. Once it is
in Excel, I want to look at a column that contains sequential numbers. THere
will be breaks in these numbers. I want to be able to put a blank line
between these breaks and then total 2 current columns.

ANy insights would be appreciated.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

 

[Guest]

Please ignore my last post. I forgot to change the column to check from B2
to C2
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

You might be able to use automatic subtotals.....

Example:
With
A1:A100 containing a list of sequential values, including some breaks in the
sequences, with a column heading in A1 (assuming MyList)

Try this:
Insert a column before Col_A
(column A has moved one column to the right)
A1: GapFlag
A2: FALSE
A3: =IF((B3-B2)>1,NOT(A2),A2)
copy A3 down as far as needed

Then, from the Excel main menu:
<data><subtotals>
At each change in: GapFlag
Use function: Sum
Add subtotal to: MyList
Click the [OK] button

That will insert a row at each break in the numeric sequence and create a
subtotal for the sequential numbers.

In your situation, you could subtotal on whatever columns you like.

Is that something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP

I have a spreadsheet that gets populated from an Access database. Once it is
in Excel, I want to look at a column that contains sequential numbers. THere
will be breaks in these numbers. I want to be able to put a blank line
between these breaks and then total 2 current columns.

ANy insights would be appreciated.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

 

[Guest]

Thanks for the feedback....I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP

Please ignore my last post. I forgot to change the column to check from B2
to C2
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

You might be able to use automatic subtotals.....

Example:
With
A1:A100 containing a list of sequential values, including some breaks in the
sequences, with a column heading in A1 (assuming MyList)

Try this:
Insert a column before Col_A
(column A has moved one column to the right)
A1: GapFlag
A2: FALSE
A3: =IF((B3-B2)>1,NOT(A2),A2)
copy A3 down as far as needed

Then, from the Excel main menu:
<data><subtotals>
At each change in: GapFlag
Use function: Sum
Add subtotal to: MyList
Click the [OK] button

That will insert a row at each break in the numeric sequence and create a
subtotal for the sequential numbers.

In your situation, you could subtotal on whatever columns you like.

Is that something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP

I have a spreadsheet that gets populated from an Access database. Once it is
in Excel, I want to look at a column that contains sequential numbers. THere
will be breaks in these numbers. I want to be able to put a blank line
between these breaks and then total 2 current columns.

ANy insights would be appreciated.
--
Cyberwolf
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf