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

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" wrote:

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

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" wrote:

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" wrote:

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

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



"Cathy Landry" wrote:

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" wrote:

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" wrote:

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

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!
================================================== ===



"Cathy Landry" wrote in message
...
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" wrote:

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" wrote:

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

Hi Bill,

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

Thank you
Cathy

"Bill Pfister" wrote:

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



"Cathy Landry" wrote:

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" wrote:

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" wrote:

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

Hello RD,

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

Thank you
Cathy

"RagDyeR" wrote:

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!
================================================== ===



"Cathy Landry" wrote in message
...
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" wrote:

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" wrote:

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

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" wrote:

Hi Bill,

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

Thank you
Cathy

"Bill Pfister" wrote:

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



"Cathy Landry" wrote:

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" wrote:

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" wrote:

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

Hi Bill,

Just downloaded it this morning. Thank you! Cathy

"Bill Pfister" wrote:

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" wrote:

Hi Bill,

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

Thank you
Cathy

"Bill Pfister" wrote:

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



"Cathy Landry" wrote:

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" wrote:

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" wrote:

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

Hi Bill,

Looks and works beautifully!

Thank you so much
Cathy

"Cathy Landry" wrote:

Hi Bill,

Just downloaded it this morning. Thank you! Cathy

"Bill Pfister" wrote:

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" wrote:

Hi Bill,

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

Thank you
Cathy

"Bill Pfister" wrote:

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



"Cathy Landry" wrote:

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" wrote:

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" wrote:

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

Hsppy to help.

Bill


"Cathy Landry" wrote:

Hi Bill,

Looks and works beautifully!

Thank you so much
Cathy

"Cathy Landry" wrote:

Hi Bill,

Just downloaded it this morning. Thank you! Cathy

"Bill Pfister" wrote:

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" wrote:

Hi Bill,

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

Thank you
Cathy

"Bill Pfister" wrote:

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



"Cathy Landry" wrote:

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" wrote:

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" wrote:

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