Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 04, 01:31 AM
WIM4246
 
Posts: n/a
Default Count unique alpha numeric "characters" in a common cell

I have a table of customers by row with the orders in one column. Thus a
single cell may contain ordered items represented as "A", "B", "C", etc. or
"1", "2", "3", etc.

So Jill could have an order "B,B" which would represent and order for "two
item B's"
Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1 item "1" and 2 item
"2's".

How can these "items" be tabulated for the total worksheet order to list a
total for all the item "A's", "B's", etc and the items "1's", "2's", etc.

Thanks for any help - for the future we need to see if the worksheet could
be designed to comply with the lab's request to have the order listed in a
single cell and still be able to compile a tabulation for invoicing purposes.

  #2   Report Post  
Old December 9th 04, 04:51 AM
Biff
 
Posts: n/a
Default

Hi!

Assume the order numbers are in col A:

=SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A",""))))

In the SUBSTITUTE function, replace "A" with the specific
character that you want to count. Alpha charaters need to
be enclosed in quotes, numbers do not. This is case
sensitive! Better yet, use a cell to hold the character
that you want to count and use that cell reference in the
SUBSTITUTE function.

Biff

-----Original Message-----
I have a table of customers by row with the orders in one

column. Thus a
single cell may contain ordered items represented

as "A", "B", "C", etc. or
"1", "2", "3", etc.

So Jill could have an order "B,B" which would represent

and order for "two
item B's"
Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1

item "1" and 2 item
"2's".

How can these "items" be tabulated for the total

worksheet order to list a
total for all the item "A's", "B's", etc and the

items "1's", "2's", etc.

Thanks for any help - for the future we need to see if

the worksheet could
be designed to comply with the lab's request to have the

order listed in a
single cell and still be able to compile a tabulation for

invoicing purposes.
.

  #3   Report Post  
Old December 9th 04, 11:21 PM
WIM4246
 
Posts: n/a
Default

Biff:

THANK YOU!! I and the high school student working with me partime don't
fully understand the formula, but it WORKS!!

If you can offer any explanation about some parts of the formula - we don't
understand inside the first "(" what are the "--" for? AND the minus LEN
statement what is the empty "quotes", "" for? followed by the "))))"

We are going to be searching out what a "LEN" statement is all about -
perhaps that will help us understand. But in the mean time THANK YOU - over
two hours of questionable hand tabulation is being reduced to a a half hour
of formula entry and worksheet arrangement for the final tabulation. THANKS!

Wayne

"Biff" wrote:

Hi!

Assume the order numbers are in col A:

=SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A",""))))

In the SUBSTITUTE function, replace "A" with the specific
character that you want to count. Alpha charaters need to
be enclosed in quotes, numbers do not. This is case
sensitive! Better yet, use a cell to hold the character
that you want to count and use that cell reference in the
SUBSTITUTE function.

Biff

-----Original Message-----
I have a table of customers by row with the orders in one

column. Thus a
single cell may contain ordered items represented

as "A", "B", "C", etc. or
"1", "2", "3", etc.

So Jill could have an order "B,B" which would represent

and order for "two
item B's"
Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1

item "1" and 2 item
"2's".

How can these "items" be tabulated for the total

worksheet order to list a
total for all the item "A's", "B's", etc and the

items "1's", "2's", etc.

Thanks for any help - for the future we need to see if

the worksheet could
be designed to comply with the lab's request to have the

order listed in a
single cell and still be able to compile a tabulation for

invoicing purposes.
.


  #4   Report Post  
Old December 9th 04, 11:26 PM
Frank Kabel
 
Posts: n/a
Default

Hi
see:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
and
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for the double minus

The SUBSTITUTE formula part replaces all 'A' characters with 'nothing'
(""). So in total this formula evaluates the number of 'A' in a string
by subtracting the length of the string without 'A's from the original
string length

--
Regards
Frank Kabel
Frankfurt, Germany

"WIM4246" schrieb im Newsbeitrag
...
Biff:

THANK YOU!! I and the high school student working with me partime

don't
fully understand the formula, but it WORKS!!

If you can offer any explanation about some parts of the formula - we

don't
understand inside the first "(" what are the "--" for? AND the minus

LEN
statement what is the empty "quotes", "" for? followed by the "))))"

We are going to be searching out what a "LEN" statement is all

about -
perhaps that will help us understand. But in the mean time THANK

YOU - over
two hours of questionable hand tabulation is being reduced to a a

half hour
of formula entry and worksheet arrangement for the final tabulation.

THANKS!

Wayne

"Biff" wrote:

Hi!

Assume the order numbers are in col A:

=SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A",""))))

In the SUBSTITUTE function, replace "A" with the specific
character that you want to count. Alpha charaters need to
be enclosed in quotes, numbers do not. This is case
sensitive! Better yet, use a cell to hold the character
that you want to count and use that cell reference in the
SUBSTITUTE function.

Biff

-----Original Message-----
I have a table of customers by row with the orders in one

column. Thus a
single cell may contain ordered items represented

as "A", "B", "C", etc. or
"1", "2", "3", etc.

So Jill could have an order "B,B" which would represent

and order for "two
item B's"
Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1

item "1" and 2 item
"2's".

How can these "items" be tabulated for the total

worksheet order to list a
total for all the item "A's", "B's", etc and the

items "1's", "2's", etc.

Thanks for any help - for the future we need to see if

the worksheet could
be designed to comply with the lab's request to have the

order listed in a
single cell and still be able to compile a tabulation for

invoicing purposes.
.



  #5   Report Post  
Old December 11th 04, 03:27 AM
WIM4246
 
Posts: n/a
Default

FranK - Hi,

Thanks for the explanation - it will take some further study for me to get
completely familar with it. Being pointed in the right direction with some
explanation is a BIG HELP - THANKS!

I'm a professional photographer and just recently photographed a family from
Germany. The wife's parents celebrated 50 yrs of marriage so all the children
with their families came home. My wife from Sweden also has relatives in
Germany - We look forward to visiting - hopefully soon.

Thanks,

Wayne Makeeff
Des Moines, IA - USA

"Frank Kabel" wrote:

Hi
see:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
and
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for the double minus

The SUBSTITUTE formula part replaces all 'A' characters with 'nothing'
(""). So in total this formula evaluates the number of 'A' in a string
by subtracting the length of the string without 'A's from the original
string length

--
Regards
Frank Kabel
Frankfurt, Germany

"WIM4246" schrieb im Newsbeitrag
...
Biff:

THANK YOU!! I and the high school student working with me partime

don't
fully understand the formula, but it WORKS!!

If you can offer any explanation about some parts of the formula - we

don't
understand inside the first "(" what are the "--" for? AND the minus

LEN
statement what is the empty "quotes", "" for? followed by the "))))"

We are going to be searching out what a "LEN" statement is all

about -
perhaps that will help us understand. But in the mean time THANK

YOU - over
two hours of questionable hand tabulation is being reduced to a a

half hour
of formula entry and worksheet arrangement for the final tabulation.

THANKS!

Wayne

"Biff" wrote:

Hi!

Assume the order numbers are in col A:

=SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A",""))))

In the SUBSTITUTE function, replace "A" with the specific
character that you want to count. Alpha charaters need to
be enclosed in quotes, numbers do not. This is case
sensitive! Better yet, use a cell to hold the character
that you want to count and use that cell reference in the
SUBSTITUTE function.

Biff

-----Original Message-----
I have a table of customers by row with the orders in one
column. Thus a
single cell may contain ordered items represented
as "A", "B", "C", etc. or
"1", "2", "3", etc.

So Jill could have an order "B,B" which would represent
and order for "two
item B's"
Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1
item "1" and 2 item
"2's".

How can these "items" be tabulated for the total
worksheet order to list a
total for all the item "A's", "B's", etc and the
items "1's", "2's", etc.

Thanks for any help - for the future we need to see if
the worksheet could
be designed to comply with the lab's request to have the
order listed in a
single cell and still be able to compile a tabulation for
invoicing purposes.
.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you make some characters in a cell bold and some not? tracman Excel Discussion (Misc queries) 4 March 28th 05 05:17 AM
#### error if cell has more than 255 characters Budalacovyek Excel Discussion (Misc queries) 1 December 8th 04 07:42 PM
count in cell Joe Excel Worksheet Functions 2 December 7th 04 03:33 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
how to count the nr of occurrences of a text string in a cell rang eagerbuyer Excel Worksheet Functions 1 November 4th 04 01:27 PM


All times are GMT +1. The time now is 08:57 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017