Unique entries down a column

Posted by Uttam on May 02, 2001 6:53 AM

Hello,

I maintain a list of bugs in a spreadsheet. Each row consists of a bug. The data is entered as a bug report is received.

Each Column has different kind of data for a bug e.g. Date of reporting the bug in one column, reported by in another, description in a third, the module in which the bug was reported and so on.

As we all know too well ;-( , more than one bug can occur in the same module.

Using formulas only, is it possible to get a list, in a different column, of all modules in which bugs have been reported and more importantly, (and this is the crunchy part for me), get a UNIQUE list, in consecutive rows, with no module repeated in that list?

To explain

The "Module" column may contain the following data:

Add User
Add User
Add User
Delete User
Modify User
Modify User
Delete User
Print User

The new / unique list should consist only of

Add User
Delete User
Modify User
Print User

I have a formula to get a unique list .. but I am not able to come up with a list in Consecutive rows.

So my list for the example above would read something like:

Add User
>BLANK CELL<
>BLANK CELL<<br>Delete User
Modify User
>BLANK CELL<
>BLANK CELL<<br>Print User

Hope to hear soon.

Thanks in advance.

Posted by Aladin Akyurek on May 02, 2001 10:32 AM

Hi Uttam,

I'll assume that your data occupy A1:A9 and consist of:

{"Modules";"Add User";"Add User";"Add User";"Delete User";"Modify User";"Modify User";"Delete User";"Print User"}

In C2 enter: DataList [ just a label ]
In D2 enter: =ADDRESS(ROW(A2),COLUMN(A2))&":"&ADDRESS(COUNTA(A:A),COLUMN(A2))

In C3 enter: LocList [ just a label ]
In D3 enter: =ADDRESS(ROW(E2),COLUMN(E2))&":"&ADDRESS(COUNTA(E:E)+1,COLUMN(E2))

In C4 enter: SortList [ just a label ]
In D4 enter: =ADDRESS(ROW(F2),COLUMN(F2))&":"&ADDRESS(COUNTA(F:F)+1,COLUMN(F2))

In C5 enter: NoDupsList [ just a label ]
In D5 enter: =ADDRESS(ROW(G2),COLUMN(G2))&":"&ADDRESS(COUNTA(F:F)+1,COLUMN(G2))

Note 1. That's right: COUNTA(F:F) and not COUNTA(G:G).

In E2 array-enter: =SUM(IF(A2>INDIRECT($D$2),1),1)

Note 2. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ ENTER at the same time.

Copy the formula in E2 down to E9.

In F2 enter: =IF(ISNUMBER(MATCH(ROW()-ROW($E$2)+1,INDIRECT($D$3),0)),INDEX(INDIRECT($D$2),MATCH(ROW()-ROW($E$2)+1,INDIRECT($D$3),0)),0) [ copy this formula down to F9 ]

In G1 enter: Unique Modules [ just a label ]
In G2 array-enter: =IF(ROW()-ROW(INDIRECT($D$5))+1>ROWS(INDIRECT($D$4))-COUNTIF(INDIRECT($D$4),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($D$4)&LT;&GT;0,ROW(INDIRECT($D$4)),ROW()+ROWS(INDIRECT($D$4)))),ROW()-ROW(INDIRECT($D$5))+1),COLUMN(INDIRECT($D$4))))) [ See Note 2. Copy this formula down to G9. ]

In column G from G1 on, you'll see the desired result:

{"Unique Modules";"Add User";"Delete User";"Modify User";"Print User"}

Aladin

Posted by Uttam on May 02, 2001 10:53 AM

Phew!

I just blindly cut and pasted the formulas below and BINGO! it works!!..

Now I have to sit and understand the formulas.

Thank you very much Alladin.

Regards,

Uttam

================