If alternative

[Busy Bee]

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!

[Pete_UK]

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!

[Franz Verga]

Nel post news:[email protected]
*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

[Pete_UK]

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!

[daddylonglegs]

...or just use LOOKUP...

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