Assign auto numbers based on a condition

[Guest]

Hello,
I need your expertise. I have few tabs with the similar data, where (a) is
below scope. I want to order all the variances above the scope (a) in
consecutive numbers by ignoring the blanks and below scope items. I am using
this formula but it doesn't work because it is counting a's.
=IF((ABS(C6)>250)+ABS(E6)>10%,COUNT($C$6:C6)&"","{a}")

In addition, I want to use the next available number in the following tab,
so if the last item in sheet1 is 250, the next item in sheet2 would be 251.
Is this possible?

Thanks in advance,

a scope <2 & .25%
2006 2005 Change %
A B C D E
10 8 2 0.25 {a}
15 6 9 1.5 2
8 9 -1 -0.111111111 {a}
9 10 -1 -0.1 {a}

 

[Guest]

One interp on your main query and a way to get there ..

Assuming data in cols A to E, from row6 down
Cols C (change) and E (scope) are the key cols

Assuming empty cols to the right of col E,
paste the same col headers from A5:E5 into G5:K5

Then put in F6:
=IF(OR(E6="",E6="{a}"),"",C6+ROW()/10^10)
(Leave F1:F5 blank)

Put in G6:
=IF(ROW(A1)>COUNT($F:$F),"",INDEX(A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
Copy G6 across by 5 cols to K6. Then select F6:K6, fill down to cover the
max expected extent of source data. Hide away col F. Cols G to K will return
only the lines from cols A to E which are not either blank or contain {a}
under col E (scope), with all lines neatly auto-sorted at the top in
ascending order by the values in col C (change).

 

[Guest]

:
....

In addition, I want to use the next available number in the following tab,
so if the last item in sheet1 is 250, the next item in sheet2 would be 251.
Is this possible?

Some thoughts to address your 2nd query above ..

Assume that in Sheet1, you have row numbering applied in col A from A6 down,
using for eg in A6: =ROW(A1) with A6 copied down.

Then in Sheet2, you could place in the corresponding start cell A6:
=MAX(Sheet1!$A$6:$A$65536)+ROW(A1)
and copy A6 down. Sheet2 will return the consecutive numbering linked to
Sheet1 that's wanted.

If you're certain that there's going to be no numbers (or dates) placed
within A1:A5, just use entire cols instead, viz in Sheet2's A6, copied down:
=MAX(Sheet1!A:A)+ROW(A1)

Extend accordingly for the other sheets ..

---

 

[Guest]

Hi Max,

Thank you for the prompt response. I think I wasn't clear enough. On column
E I do want to show (a) below scope, but I wanted the above scope items to be
in sequencial order:for example
Current Sit Desired
Col E Col E
1. a a
2. 2 1
3. a a
4. a a
5. 5 2

The second formula works prefectly when I apply as in your example, but for
some reason when I apply to my spreadsheet returns wrong values....

I would like to thank you once again for taking time to assist me...

 

[Max]

On your main query, afraid I'm not able to offer a solution to get it sorted
in exactly the manner you illustrated below. But if you'd accept an
alternative "close-fit" sort where "numbers" would appear in ascending sort
order above the lines with {a}'s, viz.:

Col E --> In col K
1. a 1
2. 2 2
3. a a
4. a a
5. 5 a

then we could use this criteria instead in F6, with F6 copied down:
=IF(E6="","",IF(E6="{a}",ROW()+10^10,C6+ROW()/10^10))

(no change to the formulas in cols G to K)

As for your linked numbering query:

.. The second formula works perfectly when I apply as in your example,
but for some reason when I apply to my spreadsheet
returns wrong values....

If it's not working in Sheet2, check that all the numbers used in Sheet1's
numbering (the first sheet) are real numbers, not text numbers. Text
numbers, if any, would be ignored by MAX(..).

---

 

[Guest]

Unfortunately, I cannot change the row order since it is for financial
statements. Thanks anyways...

 

[Max]

No prob. Thanks for feeding back. Monitor your thread awhile. Perhaps there
could be insights for you from other responders.

 

[David Biddulph]

In your condition test you are adding a logical (ABS(C6)>250), which will
have a value of 0 or 1, to ABS(E6), and then comparing the total with 10%.
Is that what you're trying to do?
In other words you'll get the TRUE condition either if ABS(E6) is greater
than 10%, or if ABS(C6)>250 [because in the latter case it doesn't matter
what ABS() value we add to the logical 1 result, it will already be above
the 10% threshold.]
If that's what you're trying to do, it might be clearer if you express it as
an OR.
--
David Biddulph

Hello,
I need your expertise. I have few tabs with the similar data, where (a) is
below scope. I want to order all the variances above the scope (a) in
consecutive numbers by ignoring the blanks and below scope items. I am
using
this formula but it doesn't work because it is counting a's.
=IF((ABS(C6)>250)+ABS(E6)>10%,COUNT($C$6:C6)&"","{a}")

....

 

[Guest]

Thank you, guys!

I think I found a solution, not perfect but it seems that it works.
=IF((ABS(C6)>250)+ABS(D6)>10%,COUNT($E$5:E5,1),"{a}")

In your condition test you are adding a logical (ABS(C6)>250), which will
have a value of 0 or 1, to ABS(E6), and then comparing the total with 10%.
Is that what you're trying to do?
In other words you'll get the TRUE condition either if ABS(E6) is greater
than 10%, or if ABS(C6)>250 [because in the latter case it doesn't matter
what ABS() value we add to the logical 1 result, it will already be above
the 10% threshold.]
If that's what you're trying to do, it might be clearer if you express it as
an OR.
--
David Biddulph

Hello,
I need your expertise. I have few tabs with the similar data, where (a) is
below scope. I want to order all the variances above the scope (a) in
consecutive numbers by ignoring the blanks and below scope items. I am
using
this formula but it doesn't work because it is counting a's.
=IF((ABS(C6)>250)+ABS(E6)>10%,COUNT($C$6:C6)&"","{a}")

....