Counting unique items, disregarding thier annexs

"THIS IS A rePOST WITH SOME CORRECTION"
hi all,
   seeking help please.
   The column A1:A100 contains items and also empty cells like  
 facil.1,beauty.2,facil.,facil.3,beauty.4 and so on....                      
       
 Looking for a solution where on counting items will yield 2 only (for 
"facil" 
and "beauty") and not 5 as each entry is unique, as the flowing formula do: 
=sum(if(frequency(match(A1:A100,A1:A100,0)match(A1:A100,A1:A100,0))>0,1)) 
many thanks for any help.

 
 
 

0
ExcelFan (36)
9/15/2004 10:23:03 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
528 Views

Similar Articles

[PageSpeed] 40

On Wed, 15 Sep 2004 03:23:03 -0700, "excelFan"
<excelFan@discussions.microsoft.com> wrote:

>"THIS IS A rePOST WITH SOME CORRECTION"
>hi all,
>   seeking help please.
>   The column A1:A100 contains items and also empty cells like  
> facil.1,beauty.2,facil.,facil.3,beauty.4 and so on....                      
>       
> Looking for a solution where on counting items will yield 2 only (for 
>"facil" 
>and "beauty") and not 5 as each entry is unique, as the flowing formula do: 
>=sum(if(frequency(match(A1:A100,A1:A100,0)match(A1:A100,A1:A100,0))>0,1)) 
>many thanks for any help.
>
> 
> 
> 

Try this *array* formula:

=SUM(IF(FREQUENCY(MATCH(LEFT(A1:A5,FIND(".",A1:A5)-1),
LEFT(A1:A5,FIND(".",A1:A5)-1),0),MATCH(LEFT(A1:A5,FIND(
".",A1:A5)-1),LEFT(A1:A5,FIND(".",A1:A5)-1),0))>0,1))

To enter an *array* formula, after typing or pasting it into the cell, hold
down <ctrl><shift> while hitting <enter>.  Excel will place braces {...} around
the formula.


--ron
0
ronrosenfeld (3122)
9/15/2004 11:19:56 AM
On Wed, 15 Sep 2004 06:07:06 -0700, "excelFan"
<excelFan@discussions.microsoft.com> wrote:

>thanks Ron for your reply, 
>i made a try with your proposal but sorry to tell you; it's not working 
>maybe due to empty cells in the range A1:A100
>many thanks Ron. 
>still waiting for solution

Did your original formula work over that range with the empty cells?


--ron
0
ronrosenfeld (3122)
9/15/2004 6:25:45 PM
On Wed, 15 Sep 2004 06:07:06 -0700, "excelFan"
<excelFan@discussions.microsoft.com> wrote:

>thanks Ron for your reply, 
>i made a try with your proposal but sorry to tell you; it's not working 
>maybe due to empty cells in the range A1:A100
>many thanks Ron. 
>still waiting for solution.

OK, well assuming now that the contents of A1:a100 are either BLANK or have a
string which includes a ".", then the *array* formula:

=SUM(IF(FREQUENCY(MATCH(IF(A1:A100<>"",LEFT(A1:A100,
FIND(".",A1:A100)-1)),IF(A1:A100<>"",LEFT(A1:A100,
FIND(".",A1:A100)-1)),0),MATCH(IF(A1:A100<>"",LEFT(A1:A100,
FIND(".",A1:A100)-1)),IF(A1:A100<>"",LEFT(A1:A100,FIND(".",A1:A100)-1)),0))>0,1))

should take care of the blanks.

========================

A simpler solution might be to download morefunc.xll -- a free download written
by Laurent Longre http://longre.free.fr/english/ and use his COUNTDIFF
function.  

Again it would be an array formula:

  =COUNTDIFF(LEFT(IF(A1:A100<>"",A1:A100),FIND(".",IF(A1:A100<>"",A1:A100))-1))


--ron
0
ronrosenfeld (3122)
9/15/2004 6:59:00 PM
>-----Original Message-----
>On Wed, 15 Sep 2004 06:07:06 -0700, "excelFan"
><excelFan@discussions.microsoft.com> wrote:
>
>>thanks Ron for your reply, 
>>i made a try with your proposal but sorry to tell you; 
it's not working 
>>maybe due to empty cells in the range A1:A100
>>many thanks Ron. 
>>still waiting for solution
>
>Did your original formula work over that range with the 
empty cells?
>
>
>--ron
>.
>I know this sounds simple but have you tried COUNT IF 
If a1:a100 contain the word Beauty and that is what you 
want to count then You would enter Countif
(a1:a100,"Beauty") it ignores blanks and anything that 
isn't the word beauty. 

--Goose
0
goose1 (2)
9/15/2004 11:02:55 PM
On Wed, 15 Sep 2004 16:02:55 -0700, "goose@Shadoux.com"
<anonymous@discussions.microsoft.com> wrote:

>I know this sounds simple but have you tried COUNT IF 
>If a1:a100 contain the word Beauty and that is what you 
>want to count then You would enter Countif
>(a1:a100,"Beauty") it ignores blanks and anything that 
>isn't the word beauty. 

That would give the number of "beauty"'s, but not the number of unique entries.


--ron
0
ronrosenfeld (3122)
9/16/2004 1:39:09 AM
hi Ron,
   many thanks again, also i tried your second suggestion where you said 
this shoud take care of blanks ,but did not work agreeably
Let's keep trying to find an answer.
Good luck and still waiting.

   
"Ron Rosenfeld" wrote:

> On Wed, 15 Sep 2004 06:07:06 -0700, "excelFan"
> <excelFan@discussions.microsoft.com> wrote:
> 
> >thanks Ron for your reply, 
> >i made a try with your proposal but sorry to tell you; it's not working 
> >maybe due to empty cells in the range A1:A100
> >many thanks Ron. 
> >still waiting for solution.
> 
> OK, well assuming now that the contents of A1:a100 are either BLANK or have a
> string which includes a ".", then the *array* formula:
> 
> =SUM(IF(FREQUENCY(MATCH(IF(A1:A100<>"",LEFT(A1:A100,
> FIND(".",A1:A100)-1)),IF(A1:A100<>"",LEFT(A1:A100,
> FIND(".",A1:A100)-1)),0),MATCH(IF(A1:A100<>"",LEFT(A1:A100,
> FIND(".",A1:A100)-1)),IF(A1:A100<>"",LEFT(A1:A100,FIND(".",A1:A100)-1)),0))>0,1))
> 
> should take care of the blanks.
> 
> ========================
> 
> A simpler solution might be to download morefunc.xll -- a free download written
> by Laurent Longre http://longre.free.fr/english/ and use his COUNTDIFF
> function.  
> 
> Again it would be an array formula:
> 
>   =COUNTDIFF(LEFT(IF(A1:A100<>"",A1:A100),FIND(".",IF(A1:A100<>"",A1:A100))-1))
> 
> 
> --ron
> 
0
ExcelFan (36)
9/16/2004 12:23:07 PM
On Thu, 16 Sep 2004 05:23:07 -0700, "excelFan"
<excelFan@discussions.microsoft.com> wrote:

>hi Ron,
>   many thanks again, also i tried your second suggestion where you said 
>this shoud take care of blanks ,but did not work agreeably
>Let's keep trying to find an answer.
>Good luck and still waiting.

There's no trouble shooting I can do based on "did not work agreeably".


--ron
0
ronrosenfeld (3122)
9/16/2004 4:26:08 PM
Reply:

Similar Artilces:

count the number of new lows within a range of cells
Hello, Is there a formula or method to count the number of new lows within a range of cells? For example, the following are in col A rows 5 to 14. Within this set a new low has been reached 3 times- (96,95,94). What I would like is a formula or macro that that would produce the results - 3. There are about 9000 rows of data and I am looking for "count of new lows within the preceding 10 rows" on a rolling basis. Thanks. 100 101 96 97 98 97 95 97 94 95 Version 1: Use this if the 9000 or so rows don't have blanks in them. In this version you just need to select the first cel...

CListView losing items
Hi, I have a problem with a CListView in report mode. I can add items but when I add the 10:th (or any > #10) item it's subtexts aren't shown, but is rather shown in the last element in the view (sorted by column #1 (ints showing the row number)) so far always the ninth element. result: 1 text 10 11 2 text 3 text 4 text 5 text 6 text 7 text 8 text 9 text corsponding to # 11 I can't figure this one out. Can someone please help me? For something like this you should post some code.. But here is how to fix half of your problem. get the return valu...

count string in cell
Hi All In my excel one of column is Status in detail worksheet This column have multi status. e.g. Color Error,Size Error,Data Matched. I want check how many error by type. In other sheet call Statistics, check how many status in detail worksheet. e.g. Color Error Size Error Data Matched I am using =COUNTIF('Item List'!T:T,A4) where T is Status Column. Funciton Countif just handle one value in status. Do you know which function can handle multi status ? If you want one cell to count all 3 of those criteria... One way... =SUMPRODUCT(--(ISNUMBER(MATCH(&...

edit variable length items in "Access" database tables
In my database table I am holding a variable length item in a database record; the item may be from two to six alphabetic characters long. Trailiing spaces pad the item where required. To print out the item in a report, the item itself is sandwiched between two fixed-length items separated by an oblique stroke, (forward slash): - ***/variable length/***. How can I suppress the trailing spaces in my variable length item when printing results ? Yours Sincerely, CHARLES WALKER Take a look at the RTrim function or even the Trim function. RTrim(SomeField) will trim trailing spaces off a...

Count Records
Hi Guys, How can I count records in a a combobox to allow me to scroll using the slider bar without first having to scroll to the bottom of the list? Regards John Count the records in the rowsource of the combobox: NumRecord = DCount("*","NameOfTheRowSource") PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "John" <johnlee2509@btopenworld.com> wrote in message news:0E1DBE7E-AD54-4715-A547-EC2DDE9CDE39@microsoft.com... > Hi Guys, > How can I count records in a a combobox...

Discontiuned inventory items
Once an item in inventory has been marked as discontinued and has a quantity on hand of zero, the user should have the option of removing it from lookup lists throughout the sales order processing and inventory modules WITHOUT removing history for the item and deleting the item. Can you please tell me how to do this? Is there a way to get the quantity to zero so that I can delete it? We are changing part numbers (again). I have over 700 parts in GP's and need only about 75 and I have never, in five years, been able to figure out how to do this. Any help you can give me would ...

Unread count is always (1) when there are no unread messages
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop I have tried everything - marking as unread, selecting all and marking as unread followed by marking all as read, cmd-alt-t, cmd-l. It still comes up with one unread message which I cannot find. <br><br>How can I fix this? On 1/22/10 12:30 AM, in article 59bb16b0.-1@webcrossing.JaKIaxP2ac0, "Lou_C@officeformac.com" <Lou_C@officeformac.com> wrote: > have tried everything - marking as unread, selecting all and marking as > unread followed by marking all as r...

matching items #2
Peter I am aware of the using filter on what worksheet, however i am being bit basic with what i am trying to achieve. I need the information t be on a seperate worksheet in a completely different arrangemen basically like a report with the information what i need being shown. am strying to show the principle in what i am trying to do with m quaestion. Thanks anyway. Ton -- tony lindsle ----------------------------------------------------------------------- tony lindsley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1499 View this thread: http://www.excelf...

a simple count question ... ?
I'm using Excel 2007 and in column A I have a list of items that I wish to have a total count of. I have spaces (empty cells) between some items in column A to make it easier to group (and to see) but was wondering if there's a way to count the cells that only contain info. In case I haven't described this clearly, I don't want an addition of these items (they're words and not numbers), I just want to know how many cells in column A contain data. Thanks, Dave Horne Hi Dave =COUNTA(A1:A100) Regards, Per "Dave Horne" <davehorne@home.nl> skrev i m...

Error "Unable to Read Item" opening .msg files
I am having intermittent problems where a user will have saved a .MSG file from Outlook 2003, and then is unable to open it later. By default, these saved files are marked read-only, and the following error is displayed "Microsoft Office Outlook - You don't have appropriate permission to perform this operation." If the read-only option is unchecked, they receive the following error trying to open the file "Microsoft Office Outlook - Unable to read the item." This problem seems to occur randomly to different users. All of my users are using Outlook 20...

select multiple items from dropdown
hello everyone, i have a table which is populated using a form. one of the fields is person's name, the dropdown for which picks up data from a second table (which has an index and person's name). what i want to do is modify the form so that a user can select multiple items from the dropdown, but i havent been able fo find a way to do so. i am using access 2003. i know that access 2007 lets you do that, but i dont have an option of upgrading right now. i would appreciate your suggestions thanks, vc-programmer Create a related table, where you can store many options for one...

count passed or failed
im having a problem getting the correct formula for this one. i used =IF((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)=(DATA!$K$2:$K$5000-DATA!$J$2:$J$5000),"Passed","Failed") and =COUNTIF(DATA!$L$2:$L$5000, "name@email.com") and it gave me the desired output. but the problem is, i have to get the total number of passed and failed for each person in my team. i can't seem to find the exact formula to combine those conditions. please help me, thanks! Neri, =SUMPRODUCT(((DATA!$G$2:$G$5000-DATA!$F$2:$F$5000)= (DATA!$K$2:$K$5000-DATA!$J$2:$J$5000)) *(DATA!$L$2:$L$...

Can I arrange items on my clipboard in a specific order?
Is it possible to change the arrangement of items in my clipboard to a certain order to make it more usable for me? Thanks! ...

Reading other users 'sent items' folders
Hi all, When using delegate access - Is there 'any' way of granting access for others to view the sent items folder? This does not appear as an option. Alternatively is there any other method to implement this? Many thanks - ras - Hi Craig! In Outlook you can right-click on Sent Items, select Properties, open Permissions tab and delegate appropriate permissions for other users. R.V. "Craig Pilkington" wrote: > Hi all, > > When using delegate access - Is there 'any' way of granting access for > others to view the sent items folder? This does n...

Tracking PST items in CRM
I have a user who wishes to track email that's stored in a PST file on his computer in CRM. When he clicks the "Track in CRM" button, he gets an error message about it not being in an active folder or something similar. Is it possible to track PST items in CRM? Thanks in advance. Only the default Outlook/Exchange/PST folder is being synch with Microsoft CRM. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm "Herb Powell" wrote: > I have a user who wishes to track email that's stored in a PST file on his > computer in CRM. When he clicks ...

Startup Item pops up twice, taskbar color flips
On an Acer Aspire One, I put a notepad TO DO file in All Users Startup and it comes up twice. On this machine, I also have a wierdness in that the taskbar sometimes comes up Blue XP and others grey (like Win 98). - = - Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist http://www.panix.com/~vjp2/vasos.htm http://www.facebook.com/vasjpan2 ---{Nothing herein constitutes advice. Everything fully disclaimed.}--- [Homeland Security means private firearms not lazy obstructive guards] [Urb sprawl confounds terror] [Phooey on GUI: Windows...

Pivot Tables
What is the diffence between "Count Numbers" and "Count" in Pivot Tables? I am finding that "Count Numbers" in Pivot tables is like "Count" in formulas, and "Count" in Pivot tables is like "CountA" in formulas. Is this true? I think you have it! It's like what you find in the statusbar, too. RS wrote: > > What is the diffence between "Count Numbers" and "Count" in Pivot Tables? I > am finding that "Count Numbers" in Pivot tables is like "Count" in formulas, ...

Error: "A failure may have corrupted the content of the item..."
Hi there! In Outlook 2002, when replying back to an email, getting error message: "A failure may have corrupted the content of the item. Copy the content to a new item, or save the item as a text file or RTF file." Has anyone ever heard of this? If so, please let me know!!! thanks!!! cat I have a user who is experiencing the same problem when trying to reply to an HTML formatted email. Using Outlook 2000. I attempted to "repair" the Office installation, with no effect. Does anyone have an answer for this one? ...

Count items in cel
Okay, here's my problem: In my excel-list I have 3 codes: "l","s" and "ls". l is the equivalent of 1,50 euro s the equivalent of 1 euro ls is thus 2,50 euro The codes are entered during the month. At the end of the month I want to see how much someone has to pay. So if that's 3 times "l", 2 times "s" and 1 time "ls" that would be 9 euro. How do I do that in a formula? Thanks in advance! =(LEN(A1)-LEN(SUBSTITUTE(A1,"1s","")))/2 gives the number of "1s" values in A1 =LEN(SUBSTITUTE(A1,"...

Counting Unique Record #s
I have a database showing assignments with their subassignments. On reports, I am attempting to count unique AssignmentIDs under groups (for example different months the assignments are due). Unfortunately, if the assignment has two (or more) sub assignments, the count function counts the AssignmentID twice (since it lists the AssignmentID with the SubAssignment ID). Do you know a way to get around this so I can count how many unique main assignments are under each group? Totals query. GroupBy your "group"; Count your AssignmentID. Regards Jeff Boyce Microsoft Office/Ac...

Tracking items to Cases
Hi.. We're busy trying to learn as much as we can but a few things seem to have us beat... I've searched the groups so decided to ask if you good folks can help! At the moment we mostly use the Service side of CRM... so we quite often have emails come in which we then tag the 'regarding' field to a case in CRM... The problem is that the 'lookup' box for Cases shows *EVERY* case... not just the 'Open' cases.... This is utterly impractical when you have 100's or 1000's of cases... Can anyone point me in the direction of how we can limit the displ...

Item Integration not populating list price
I am doing and inventory item integration. I can't get the price group, default price level, or the list price to populate. I made sure the list price was put in the currency folder. My price group and price level values do exist. I have tried putting a constant (1.00) into the list price field and that doesn't work either. We are not using extended pricing. Any ideas? Jenny, You will need to have to sources: one for your items and one for your list price. Since list prices are part of the currency collection, IM expects a one to many relationship between the ...

Inventory Items
Is there a way to indicate an item is a drop ship on the inventory card? -- HK Could you use one of the user categories? -- Jim@TurboChef "HK" wrote: > Is there a way to indicate an item is a drop ship on the inventory card? > -- > HK ...

How to disable "delete opportunity" menu item and action?
I have the need to disable the "delete opportunity" functionality, forcing users to "close" the opportunity with a reason. This update needs to be in two places: 1) Sales\Opportunities main screen, where there is the delete "X" on the screen. 2) Opportunity main form, where there is <Action\Delete Opportunity>. Can someone assist with a reference on how to make this happen? Thanks. Steve. Suggest you look at user role settings in order to accomplish this. -Chris There is no supported method to remove the interface elements. However what you c...

Again
This has been an issue for a few weeks now. I have observed not only an arbitrary deletion of a message that came into my Inbox about 2 minutes before, but also seeing the Deleted Items folder being completely emptied. Both actions took place within a few seconds of each other This occured with zero user input from me and there are no user recipient policies in place. The enviroment is Exchange 2000\SP3 under Windows 2k, fully patched Nothing out of the ordinary in event viewer. No recipient message policies. No nothing. Any thoughts? George Do you see vanished messages as rec...