Formula Help

C

Cathy Landry

Hello,

I have a mileage form that in col C list multiple cost centers and in col D
the user has to enter their cost center again so the calculations will work.
I'd like a formula that will pull the cost center in C only once so there is
a total of mlg being claimed per cost center.

Example:

A B C D E
10 miles 09/01 611 611 40 miles
20 miles 09/02 612 612 60 miles
30 miles 09/03 611
40 miles 09/04 612

Thank you for any/all assistance
Cathy
 
C

Cathy Landry

Hi Bill,

That will work for the duplicates, but what if there are cost centers that
do not duplicate............I need to see all the cost center in col D, but
only once if they are duplicated...........I hope that makes sense.

A B C D E
10 miles 09/01 611 611 40 miles
20 miles 09/02 612 612 60 miles
30 miles 09/03 611 984 50 miles
40 miles 09/04 612
50 miles 09/05 984

Thank you
Cathy
 
B

Bill Pfister

Cathy, using the "=IF(COUNTIF($A$3:A3,A3)=1,A3,"")" formula will also return
duplicate and non-duplicate items. Did the formula not work for you when you
tried it?

Bill
 
R

RagDyeR

To produce a list of unique values in Column D, try this:

In D1 enter:
=C1

Then, in D2, enter this *array* formula:

=IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,C$1:C$5&""),0)),"",INDEX(IF(ISBLANK(C$1:C
$5),"",C$1:C$5),MATCH(0,COUNTIF(D$1:D1,C$1:C$5&""),0)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE entry, copy down.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Hi Bill,

That will work for the duplicates, but what if there are cost centers that
do not duplicate............I need to see all the cost center in col D, but
only once if they are duplicated...........I hope that makes sense.

A B C D E
10 miles 09/01 611 611 40 miles
20 miles 09/02 612 612 60 miles
30 miles 09/03 611 984 50 miles
40 miles 09/04 612
50 miles 09/05 984

Thank you
Cathy
 
C

Cathy Landry

Hi Bill,

Yes, this formula did work, but trying to combine the formula to remove the
blanks is not working.....argh!

Thank you
Cathy
 
C

Cathy Landry

Hello RD,

This formula works also, but is leaving "#N/A" in the blank cells. Can
these be suppressed/removed?

Thank you
Cathy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top