I have a list of over 300 accounts #.

100000 Cash
100100 Petty Cash
200100 A/R
etc...

Each range represents a different category. The ranges are 100000-199999 is
an A, 200000-29999 is a B. 300000 - 319999 is a C, 320000-399999 is D etc...
I need to identify each row as being an A, B, C etc... so I can sort and
add/subtract depending on its identifyer.

I know I can combine several "if" statement but is there an easier way? I
do not know VBA so would rather avoid it.

Thx!

You could create a little table in an unused part of the sheet (say Y1
to Z5) as follows:

100000 A
200000 B
300000 C
320000 D
400000 E

then use this as a lookup table as follows, assuming your account
numbers are in cell A2 onwards:

=VLOOKUP(A2,Y$1:Z$5,2,0)

Copy the formula down and you will have your letter categories. You can
use copy | Edit | Paste Special | Values to fix the values, then do
your sort.

Hope this helps.

Pete

Busy Bee wrote:
I have a list of over 300 accounts #.

100000 Cash
100100 Petty Cash
200100 A/R
etc...

Each range represents a different category. The ranges are 100000-199999 is
an A, 200000-29999 is a B. 300000 - 319999 is a C, 320000-399999 is D etc...
I need to identify each row as being an A, B, C etc... so I can sort and
add/subtract depending on its identifyer.

I know I can combine several "if" statement but is there an easier way? I
do not know VBA so would rather avoid it.

Thx!

Nel post
*Busy Bee* ha scritto:

I have a list of over 300 accounts #.

100000 Cash
100100 Petty Cash
200100 A/R
etc...

Each range represents a different category. The ranges are
100000-199999 is an A, 200000-29999 is a B. 300000 - 319999 is a C,
320000-399999 is D etc... I need to identify each row as being an A,
B, C etc... so I can sort and add/subtract depending on its
identifyer.

I know I can combine several "if" statement but is there an easier
way? I do not know VBA so would rather avoid it.

Thx!


I think you colud use VLOOKUP function. See on line help and then if you
should have any problem, post again here.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Sorry, the final parameter should be 1 (or TRUE), as follows:

=VLOOKUP(A2,Y$1:Z$5,2,1)

Pete

Pete_UK wrote:
You could create a little table in an unused part of the sheet (say Y1
to Z5) as follows:

100000 A
200000 B
300000 C
320000 D
400000 E

then use this as a lookup table as follows, assuming your account
numbers are in cell A2 onwards:

=VLOOKUP(A2,Y$1:Z$5,2,0)

Copy the formula down and you will have your letter categories. You can
use copy | Edit | Paste Special | Values to fix the values, then do
your sort.

Hope this helps.

Pete

Busy Bee wrote:
I have a list of over 300 accounts #.

100000 Cash
100100 Petty Cash
200100 A/R
etc...

Each range represents a different category. The ranges are 100000-199999 is
an A, 200000-29999 is a B. 300000 - 319999 is a C, 320000-399999 is D etc...
I need to identify each row as being an A, B, C etc... so I can sort and
add/subtract depending on its identifyer.

I know I can combine several "if" statement but is there an easier way? I
do not know VBA so would rather avoid it.

Thx!

...or just use LOOKUP...

=LOOKUP(A2,Y$1:Z$5)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=557147