Formula Help

[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

 

[Bill Pfister]

Cathy, see two of Chip Pearson's pages to help out. First, you want to
create a unique list of your items in column c (Extracting Unique Entries,
(http://www.cpearson.com/excel/duplicat.htm), then you want to remove the
blanks from the list (Eliminating Blank Cells From Lists,
http://www.cpearson.com/excel/noblanks.htm). This should provide the unique
list of cost centers, then you can use SumIf to total.

Regards,
Bill

 

[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

 

[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

 

[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$11,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

 

[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

 

[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

 

[Bill Pfister]

Cathy, I'll leave this file up for awhile - let me know when you've
downloaded it.

Bill

http://wcpii.com/Documents/Unique.xls

 

[Cathy Landry]

Hi Bill,

Just downloaded it this morning. Thank you! Cathy

 

[Cathy Landry]

Hi Bill,

Looks and works beautifully!

Thank you so much
Cathy

 

[Bill Pfister]

Hsppy to help.

Bill

Hi Bill,

Looks and works beautifully!

Thank you so much
Cathy