Excel 2002: How to fill up text for like items ?

[Mr. Low]

Hi,

I am working on the following table:

A B C D E
1 Item Amount Balance Status Group
2 L21 100 Full
3 L21 -60 Full
4 L21 -40 0 Full Full
5 T48 250 Part
6 T48 -50 Part
7 T48 -50 -100 Part Part
8 M64 120 Unpaid
9 M64 220 340 Unpaid Unpaid
10 K74 500 Full
11 K74 -300 Full
12 K74 -100 Full
13 K74 -100 0 Full Full
14 Q32 400 Part
15 Q32 -150 250 Part Part

Column D is the status of the item based on the balance.

May I know what formula I must input at cell E2 and copy downwards to fill
up the status for the rows with like items ?

Thanks

Low

 

[Dave Eade]

do you mean D2?

Can you not just copy and paste the text?
or d3 "=d2", then copy and paste into any EMPTY cells

 

[Bob Umlas, Excel MVP]

In E2, enter this formula with Ctrl+Chift+Enter, then fill down:

=OFFSET(D1,MATCH(TRUE,A2<>OFFSET(A2,1,0,100,1),0),0)

Bob Umlas
Excel MVP

 

[Bob Umlas, Excel MVP]

make that Ctrl+Shift+Enter (!)

 

[Mr. Low]

Hello Bob,

Many thanks for the array formula if I am not mistaken.

Best Regards,

Low