To "list" and "separate"...

G

Gritz

Preliminaries:
- I have Office 2003
- I have looked in the Help area and Archives
-If I knew how to better describe this I probably wouldn't be here...most of
the time I can figure my way through the help files...<grin>

Problem: Trying to "sumarize" how many times to charge discrete Department
Codes
--I have a column of Department Codes that we will be charging for training
--Some Departments will send 1 person to a class (we change them 1 time)
--Some Departments will send >1 person to a class (we charge them "x" times)
--Class list is alpha-sorted by last name
--Department Codes are one of the last columns in a long sheet

Desired Result:
--To have a formula that will:
--List the unique Department Codes in a new column
--Sum how many times this unique Department Code is listed on the sheet
in a new column

Example Raw Data
"Dept.Code"
AD111
AB123
AB123
AC222
AB123
AC222
AD111


Desired Result:
AB123 3
AC222 2
AD111 2

I have tried looking over FREQUENCY, MATCH and a few others, but not sure
how to manipulate it

Thank you in advance (TYIA...)

Gritz
 
B

Biff

Gritz said:
Preliminaries:
- I have Office 2003
- I have looked in the Help area and Archives
-If I knew how to better describe this I probably wouldn't be here...most
of the time I can figure my way through the help files...<grin>

Problem: Trying to "sumarize" how many times to charge discrete
Department Codes
--I have a column of Department Codes that we will be charging for
training
--Some Departments will send 1 person to a class (we change them 1 time)
--Some Departments will send >1 person to a class (we charge them "x"
times)
--Class list is alpha-sorted by last name
--Department Codes are one of the last columns in a long sheet

Desired Result:
--To have a formula that will:
--List the unique Department Codes in a new column
--Sum how many times this unique Department Code is listed on the sheet
in a new column

Example Raw Data
"Dept.Code"
AD111
AB123
AB123
AC222
AB123
AC222
AD111


Desired Result:
AB123 3
AC222 2
AD111 2

I have tried looking over FREQUENCY, MATCH and a few others, but not sure
how to manipulate it

Thank you in advance (TYIA...)

Gritz
Click to expand...

The formula to list the unique codes is complicated. Using an advanced
filter is a lot easier.

Use the advanced filter to filter for unique values and copy the unique
values to another location then use a COUNTIF formula.

If you really, really, really got to have a formula for the uniques post
back.

Biff
 
R

RaymundCG

Hi Gritz!

You may try using an array formula but before that try the ff steps:

Assuming that A2 is the cell containing the first data point

(1) Name the range of dept codes (i.e. A2:A8); for this example I name this
"data"
(2) On the column beside it select the same number of rows (i.e. B2:B8) as
in the first one then enter the ff array formula:

=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data)))))

remember to enter the array formula using CTRL+SHIFT+ENTER

(3) On the third column, use the ff formula:
=COUNTIF(data,B2) then copy down to the last row.

Now you have to just figure out how to remove the extraneous #NUM! data
(i.e. B5:B8 output) using the ISERR function.

Hope this helps!
 
R

RaymundCG

Some minor modifications to my previous post; incorporated the corrections to
them. :)
 
G

Gritz

Thank you for your time in looking this over...Please forgive the return to
this, but I've gotten lost in some of the syntax (and I've never worked with
array formulae that require a CTRL+SHIFT+ENTER).

In the formula below you said to replace the term "data" with the
range...but the term data is used several times, so I'm not sure which
'data' location to replace with which value.

For example: Data Range is A2:A20

Formula is
=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data)))))

Does this mean:

=INDEX(A2:A20)....MATCH(A2,A20,0)............&ROWS(A2:A20).....MATCH(A2,A20,0)......&ROWS(A2:A20)
????

Again, thank you for your assistance in looking this over!!!!!!!
 
R

RaymundCG

Hi Gritz!

It's OK no prob! :) Regarding your question...

if data is equal to A2:A20 then

=INDEX(A2:A20,SMALL(IF(MATCH(A2:A20,A2:A20,0)=ROW(INDIRECT("1:"&ROWS(A2:A20))),MATCH(A2:A20,A2:A20,0),""),ROW(INDIRECT("1:"&ROWS(A2:A20)))))

since it is an array formula, when you enter this successfully in a cell (by
pressing CTRL, SHIFT and ENTER together at the same time), the above formula
should now look like this...

{=INDEX(A2:A20,SMALL(IF(MATCH(A2:A20,A2:A20,0)=ROW(INDIRECT("1:"&ROWS(A2:A20))),MATCH(A2:A20,A2:A20,0),""),ROW(INDIRECT("1:"&ROWS(A2:A20)))))}

Take note of the {} brackets. This is NOT equivalent to manually placing {}
brackets on the formula. :)

To prevent errors from appearing, a slightly longer formula would be needed
which generally looks like this...

=IF(ISERROR(formula),"",formula) where formula refers to the ff equation in
this example...

=IF(ISERROR(INDEX(A2:A20,SMALL(IF(MATCH(A2:A20,A2:A20,0)=ROW(INDIRECT("1:"&ROWS(A2:A20))),MATCH(A2:A20,A2:A20,0),""),ROW(INDIRECT("1:"&ROWS(A2:A20)))))),"",INDEX(A2:A20,SMALL(IF(MATCH(A2:A20,A2:A20,0)=ROW(INDIRECT("1:"&ROWS(A2:A20))),MATCH(A2:A20,A2:A20,0),""),ROW(INDIRECT("1:"&ROWS(A2:A20))))))

Yes it's rather LONG unless you want to resort to VBA. Remember also to take
note of the parentheses and to enter this formula as an array formula.

Hope this helps!
 
Top