Check text in a range

Hi all

I�m a new member & I�d appreciate help with the following:

I have a vertical range of text which changes and which is linked t
another sheet.  I need to be alerted if a text appears more than tw
times in the range.  Any takers??

e.g.

Bob
Bob
Tom
Tom
Jane
Jane
Bob

�alert, a member is oversubscribed� 

Thanks
Mik

--
Message posted from http://www.ExcelForum.com

0
8/11/2004 1:38:26 PM
excel 39879 articles. 2 followers. Follow

3 Replies
558 Views

Similar Articles

[PageSpeed] 4

> I�m a new member & I�d appreciate help with the following:
>
> I have a vertical range of text which changes and which is linked to
> another sheet.  I need to be alerted if a text appears more than two
> times in the range.  Any takers??
>
This may be a job for conditional formatting, use 'formula is':
=COUNTIF($A:$A;A1)>2 (I assume your range starts in A1).
---
Marcin


0
8/11/2004 1:51:07 PM
This will tell you each time there is more than one in the list
Sub morethanonce()
For Each c In Selection
If Application.CountIf(Selection, c) > 1 Then
MsgBox c & " is oversubscribed"
End If
Next
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"mlhynes >" <<mlhynes.1attm0@excelforum-nospam.com> wrote in message
news:mlhynes.1attm0@excelforum-nospam.com...
> Hi all
>
> I'm a new member & I'd appreciate help with the following:
>
> I have a vertical range of text which changes and which is linked to
> another sheet.  I need to be alerted if a text appears more than two
> times in the range.  Any takers??
>
> e.g.
>
> Bob
> Bob
> Tom
> Tom
> Jane
> Jane
> Bob
>
> "alert, a member is oversubscribed"
>
> Thanks
> Mike
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
8/11/2004 2:00:49 PM
Thanks people for your help so far.  I'm nearly there and excuse me i
the solution is obvious.  However I still can't quiet get it to work.
The vertical text range changes but I need to be alerted if "any typ
of text" appears more than once.

Your have helped point me in the right direction so far and I d
appreciate your time and help

Regards
Mik

--
Message posted from http://www.ExcelForum.com

0
8/14/2004 12:27:39 PM
Reply:

Similar Artilces:

Shrinking Text
Hi everybody, in our Office (Word) 2007 application, WTS 2008x64, the following happens (for some - not all- users): A text is written and formatted normally and without any problems. The document shall be printed. After the printjob is executed, the chracters of the text lines are compressed to only a few millimeters in width. The font height remains normal. Nothing is legible. This now ocurs on the printed doc as well as on the screen. After the doc is saved and opened in a different session, everything is OK and works well... What can I do? As always, every help and ...

Finding characters within a text
Hi How to check, using single formula, that a text within a certain cell contains one of certain characters? For instance, how to check if there is a 'R', 'L' or 'Ps' character within cell A1 that reads 'W-RII'. Thanks in advance Hmm..not very elegant, but maybe =NOT(AND(ISERROR(FIND("R",A1)),ISERROR(FIND("L",A1)),ISERROR(FIND("P",A1)))) Returns TRUE if the text in A1 contains 'R', 'L' or 'Ps' . FIND is case-sensitive, use SEARCH if you also want to find lower-case characters. Cheers, Joerg Mochiku...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Report Writer Text Spacing Problems While Modifying a PO form
Due to Report Writer static text field size limitations, I am forced to try to use two adjacent text fields to complete a sentence. I am having an inordinate amout of problems trying to get words to align in sentences with the correct amount of character spacing between words that span the text field boundaries. And, the process of toggling between Report Writer and Dyamics to get the Purchase Order form to reprint is time consuming. What am I missing (besides patience)? -- Jay Jay, 1) Setup a calculated field for your static text. Set the result type to String, and use the Constant...

Text Effects Scrolling or Blinking text
How do apply the scroll or blinking feature to text that you want to emphasize in a word document. Prior to MS2007 there was a feature on the fonts tab that allowed a user to apply the following features to text "marching ants, blinking text, scrolling marquee, etc. I cannot find the any of these features anywhere in Word 2007. "HELP" There is no direct access to them, but they are still available by macro I posted the following macro recently, for use in Word 2007 Sub AnimateFont() Dim sAnimation As String If Len(Selection.Range) = 0 Then MsgBox "...

Safe Pay Check Amount Problem
I am trying to setup Safe Pay. Everything seems to be working fine except for one annoying quirk. Our bank's Positive Pay data format gives us 12 characters for check amounts ($$$$$$$$$$¢¢). The file that Safe Pay generates is working perfectly except for those checks that are greater than $9,999,999.99 (9 characters). If the check amount is greater than that (say $10,000,000.00), the value that the Safe Pay file shows for that check is 002147483647. And, even if none of the individual checks exceed $9,999,999.99, if the total of the checks in the batch exceeds that a...

What is 'Align Text to Base Line Guide'?
Trying to make sure all lines have the same amount of space between them I selected paragraph from the format menu and under the line adjustments there is a box 'align text to base line guides' Ok I did that, now there are lines at the margines of my newsletter. What are these? Also selecting this pushed my text down from the top edge of the text box. What is this? The baseline guide measurements are in the Arrange, Layout Guides, Baseline tab. There is help in the Help menu. -- Mary Sauer http://msauer.mvps.org/ "Go_Girl3647" <GoGirl3647@discussions.microsoft.com...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Vendor transactions and checks
I'm trying to create a vendor payment report from my program. From which tables I can get the vendor payable transaction information??? I also need to know whether there is a check aleady issue to a transaction. Thank you. ...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Changing Text Box Size and Location on a form, at run time with Access Xp
Hello All, Is it possible to change the size of the text box (or any other control) on a form at run time, by Drag and drop or resizing borders using the mouse events. (Just like the design mode but the user will use it at run time on form view). Thanks in advance... You should be able to resize in your code if necessary. Me.control.Visible = True Me.control.Height = 1000 Me.control.Width = 1000 Don't forget to set them back when you are done. "Developer" wrote: > Hello All, > Is it possible to change the size of the text box (or any other control) on > a ...

How do text capture tools work?
Here are two examples of this technology. http://www.textcapture.com/en/default.html http://www.deskperience.com/textcapture/ Sounds like they will have to involve OCR, which works fine for standard fonts but probably won't work with fancy fonts. Key here is to test them try a static control with a TextOut and change the fonts and see what happens. joe On Wed, 31 Oct 2007 15:02:04 -0500, "Peter Olcott" <NoSpam@SeeScreen.com> wrote: >Here are two examples of this technology. > > http://www.textcapture.com/en/default.html > http://www.deskp...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

Spiral text
Does anyone know how to create spiral text? In a draw program for sure. You probably can get close in Publisher, but it will not look as good as a draw program can do. Serif has a free program you can try. DrawPlus http://www.freeserifsoftware.com/ -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jaykay" <jaykay@discussions.microsoft.com> wrote in message news:71954A65-C46F-4005-9553-B324EFEE7500@microsoft.com... > Does anyone know how to create spiral text? I've downloaded the program, but can't figur...

Email Text Dissappears when Save
When we save email messages (as a draft) the text dissappears, even when th I use my administrator account. Anybody have any idea what that is happening. ...

Can I have spell check ignore errors and alert me to real words?
Instead of stopping on misspellings and skipping real words, is there a way to stop on correct spellings to find actual words in a bunch of gibberish? Sort of an "UNspell check". For example, pass over "JJKSXO" but show me "SPELL". No, there is no way of doing that regards, Peo Sjoblom "JenLynFish" wrote: > Instead of stopping on misspellings and skipping real words, is there a way > to stop on correct spellings to find actual words in a bunch of gibberish? > Sort of an "UNspell check". For example, pass over "JJKSX...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

Setting Text in a ComboBox
Hi, Does anybody know why I cannot set the text in the edit control of a CBS_DROPDOWN style CombBox control using SetWindowText()? I was able to change the text in the edit control part of the ComboBox manually. Thanks, Vincent. I don't know why can't you? Post some code. You should be able to m_MyCombo.SetWindowText("The Text"); Ali R. "Vincent Yu" <anonymous@discussions.microsoft.com> wrote in message news:5e2b01c3ad48$5309d630$a601280a@phx.gbl... > Hi, Does anybody know why I cannot set the text in the > edit control of a CBS_DROPDOWN style ...

anchor picture to text box
(Publisher 2003) I have a newsletter with a Kudos box. In the corners of the box I have a happy face. I'd like the happy faces to be anchored to their corners so when I enlarge or shorten the box, the faces move along with the text box. Why not create a rectangle, insert the happy faces, group. The problem as I see it would be if you resize, the happy faces could be distorted if you widen or shrink the group. You would always have to re-size proportionally. -- Mary Sauer http://msauer.mvps.org/ "Helen" <Helen@discussions.microsoft.com> wrote in message news:193...

Altering the range that is plotted by a chart via VBA
Good morning all. I haven't really done a huge amount on charting, so I'm sure you'll think that these questions are a bit simplistic, but here goes! I plot a chart from a worksheet that contains three columns of data - the X axis contains week numbers, and is in B29:B54. Actual hours are entered in F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the form: =SUM($F$29:F29) So, each week, in order to plot another week's data, I have to copy this cumulative formula down by one cell, which, considering I have a lot of these reports, is a bit time cons...

Text Ticker
Hello Experts i was asked to do a simple project 1-want do a text ticker can be scroll left to right and right to left 2-transparent 3-no flicker 4-has a background image please where can i start and how to do it thank you BitBlt background to offscreen bitmap TextOut to the bitmap with appropriate offset and clipping region BitBlt offscreen bitmap to client area of screen Use a subclassed CStatic joe On 18 Aug 2006 20:59:22 -0700, "Prafulla T" <prafulla.tekawade@gmail.com> wrote: >Hello Experts > >i was asked to do a simple project >1-want do a text tic...

text box email publication
I am a publisher novice. I am trying to develop an e-mail newletter. The templates in publisher have a text box under each article with what appears to be a hyperlink with "more details". I assume this will enable the reader of the email to expand the text box and see the full details of the article. I like this feature as it means that readers only get to see the title and abstract and then can select the articles they want to read in full. Unfortunately I can not work out how to use this function and as usual help is no help. Tim no such feature in Publisher. "kiwi ti...