#### finding unique numbers

```I have a column of numbers.  Some numbers may appear in the column more than
one time.  I need to find out the numbers that appear only one time.

For instance the column looks like this:

32
47
15
92
32
15
92

I need to find out that 47 is only used once.  How can I do this?

Thanks.

```
 0
nospam (155)
8/18/2005 1:32:33 PM
excel 39879 articles. 2 followers.

11 Replies
573 Views

Similar Articles

[PageSpeed] 55

```Assuming that A1:A7 contains your data, try...

B1, copied down:

=IF(ROWS(\$B\$1:B1)<=SUM(--(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1)),INDEX(\$A\$1:\$A\$
7,SMALL(IF(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1,ROW(\$A\$1:\$A\$7)-ROW(\$A\$1)+1),ROW
S(\$B\$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <OEpRLl\$oFHA.496@TK2MSFTNGP10.phx.gbl>,
"Glenn" <nospam@yahoo.com> wrote:

> I have a column of numbers.  Some numbers may appear in the column more than
> one time.  I need to find out the numbers that appear only one time.
>
> For instance the column looks like this:
>
> 32
> 47
> 15
> 92
> 32
> 15
> 92
>
> I need to find out that 47 is only used once.  How can I do this?
>
> Thanks.
```
 0
domenic22 (716)
8/18/2005 1:57:27 PM
```Go to www.cpearson.com where Chip has lots of good stuff on locating unique
and duplicate items
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Glenn" <nospam@yahoo.com> wrote in message
news:OEpRLl\$oFHA.496@TK2MSFTNGP10.phx.gbl...
>I have a column of numbers.  Some numbers may appear in the column more
>than
> one time.  I need to find out the numbers that appear only one time.
>
> For instance the column looks like this:
>
> 32
> 47
> 15
> 92
> 32
> 15
> 92
>
> I need to find out that 47 is only used once.  How can I do this?
>
> Thanks.
>
>

```
 0
bliengme5824 (3040)
8/18/2005 1:58:23 PM
```I can't quite get this to work.  What do you mean by confirmed with
CONTROL+SHIFT+ENTER ?
"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-393C60.09572718082005@msnews.microsoft.com...
> Assuming that A1:A7 contains your data, try...
>
> B1, copied down:
>
> =IF(ROWS(\$B\$1:B1)<=SUM(--(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1)),INDEX(\$A\$1:\$A\$
> 7,SMALL(IF(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1,ROW(\$A\$1:\$A\$7)-ROW(\$A\$1)+1),ROW
> S(\$B\$1:B1))),"")
>
> ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> In article <OEpRLl\$oFHA.496@TK2MSFTNGP10.phx.gbl>,
>  "Glenn" <nospam@yahoo.com> wrote:
>
> > I have a column of numbers.  Some numbers may appear in the column more
than
> > one time.  I need to find out the numbers that appear only one time.
> >
> > For instance the column looks like this:
> >
> > 32
> > 47
> > 15
> > 92
> > 32
> > 15
> > 92
> >
> > I need to find out that 47 is only used once.  How can I do this?
> >
> > Thanks.

```
 0
nospam (155)
8/18/2005 2:16:19 PM
```In article <uljup9\$oFHA.3364@tk2msftngp13.phx.gbl>,
"Glenn" <nospam@yahoo.com> wrote:

> I can't quite get this to work.  What do you mean by confirmed with
> CONTROL+SHIFT+ENTER ?

After typing the formula, instead of hitting just ENTER, hold the
CONTROL and SHIFT keys down, then while both keys are held down, hit the
ENTER key.  Excel will place braces around the formula indicating that
you've entered the formula correctly.
```
 0
domenic22 (716)
8/18/2005 2:42:45 PM
```Glenn wrote:
> I have a column of numbers.  Some numbers may appear in the column more than
> one time.  I need to find out the numbers that appear only one time.
>
> For instance the column looks like this:
>
> 32
> 47
> 15
> 92
> 32
> 15
> 92
>
> I need to find out that 47 is only used once.  How can I do this?
>
> Thanks.
>
>
=IF(COUNTIF(A:A,A1)=1,A1,"") filled down

Alan Beban
```
 0
unavailable (273)
8/18/2005 6:29:38 PM
```Domenic wrote...
>Assuming that A1:A7 contains your data, try...
>
>B1, copied down:
>
>=IF(ROWS(\$B\$1:B1)<=SUM(--(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1)),INDEX(\$A\$1:\$A\$7,
>SMALL(IF(COUNTIF(\$A\$1:\$A\$7,\$A\$1:\$A\$7)=1,ROW(\$A\$1:\$A\$7)-ROW(\$A\$1)+1),
>ROWS(\$B\$1:B1))),"")
>
>...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
....

Bit redundant.

B1 could be simplified to =A1. As for B2, how about

=IF(OR(COUNTIF(B\$1:B1,A2:A\$7)=0),
INDEX(A2:A\$7,MATCH(0,COUNTIF(B\$1:B1,A2:A\$7),0)),"")

[I just figured out that both ranges could be anchored at one end,
floating at the other.]

```
 0
hrlngrv (1990)
8/18/2005 9:38:48 PM
```Alan Beban wrote...
....
>=IF(COUNTIF(A:A,A1)=1,A1,"") filled down

What, no plug for ArrayUniques?

```
 0
hrlngrv (1990)
8/18/2005 9:41:17 PM
```In article <1124401127.985383.136790@g14g2000cwa.googlegroups.com>,
"Harlan Grove" <hrlngrv@aol.com> wrote:

> B1 could be simplified to =A1.

I'm not quite sure what you mean by this.

> As for B2, how about
>
> =IF(OR(COUNTIF(B\$1:B1,A2:A\$7)=0),
> INDEX(A2:A\$7,MATCH(0,COUNTIF(B\$1:B1,A2:A\$7),0)),"")

Actually, the OP is looking for a list of values that occur once within
the range, not a list of unique values.

But I like the IF statement.  I'll have to make a note of it.

> [I just figured out that both ranges could be anchored at one end,
> floating at the other.]

Yep, makes an already expensive formula less so.
```
 0
domenic22 (716)
8/18/2005 11:06:17 PM
```Domenic wrote...
....
>Actually, the OP is looking for a list of values that occur once within
>the range, not a list of unique values.
....

Good point. All array formulas.

B1:
=IF(OR(COUNTIF(A1:A7,A1:A7)=1),
INDEX(A1:A7,MATCH(1,COUNTIF(A1:A7,A1:A7),0)),"")

B2:
=IF(SUMPRODUCT(--(COUNTIF(A\$1:A\$7,A\$1:A\$7)=1))>=ROWS(A\$1:A2),
INDEX(A\$1:A\$7,MATCH(1,COUNTIF(A\$1:A\$7,IF(COUNTIF(C\$1:C1,A\$1:A\$7)=0,
A\$1:A\$7)),0)),"")

B2 filled down as needed.

```
 0
hrlngrv (1990)
8/19/2005 12:20:11 AM
```In article <1124410811.117786.102770@g47g2000cwa.googlegroups.com>,
"Harlan Grove" <hrlngrv@aol.com> wrote:

> B2:
> =IF(SUMPRODUCT(--(COUNTIF(A\$1:A\$7,A\$1:A\$7)=1))>=ROWS(A\$1:A2),
> INDEX(A\$1:A\$7,MATCH(1,COUNTIF(A\$1:A\$7,IF(COUNTIF(C\$1:C1,A\$1:A\$7)=0,
> A\$1:A\$7)),0)),"")
>
> B2 filled down as needed.

Nice one, Harlan!

I take it that you meant...

COUNTIF(B\$1:B1,A\$1:A\$7)=0

....and maybe...

ROWS(B\$2:B2)
```
 0
domenic22 (716)
8/19/2005 1:52:22 PM
```Domenic wrote...
....
>I take it that you meant...
>
>COUNTIF(B\$1:B1,A\$1:A\$7)=0
>
>...and maybe...
>
>ROWS(B\$2:B2)

Yes. Oops!

```
 0
hrlngrv (1990)
8/19/2005 4:35:21 PM

Similar Artilces:

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

Auto assign Lot Numbers in PO receiving
Hi! Has anyone made a customization to auto assign Lot numbers upon item receipt? My client's lot number is numeric, they want the system to auto assign this. Any ideas? Thanks! -- Marisol Mortera Marisol, Yes indeed, I created one for a client. You probably will not be able to use it as-is because of some client specific functionality but I'm sure you could use this as a stepping stone. The key features were that it would allow for rapid lot number entry by a. Auto-incrementing the lot number - we had Alphanumeric lot numbers so we added a suffix and b. copy the previous lo...

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...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Where do I find SSADM 4+ templates in Visio ?
Hi, I understand SSADM 4+ templates were available in Visio 2002. I have just moved from Smartdraw to Visio Professional 2003 and am having difficulty finding SSADM 4+ templates. Does anyone know where I can find these templates? Are they still available in the 2002 version? ...

XL 2007: How to find out what keyboard shortcuts I've assigned to macros?
Over the years, I've recorded and written a lot of macros. (Probably time to take a week or two and go over them and delete at least half!) I assigned a keyboard shortcut to a lot of them. I seem to remember that before XL 2007, there was a keyboard organizer that I could go through and review what shortcuts I had already assigned to my macros. I can't find that in 2007, though. Is there an easy way to generate a list of all my keyboard assignments and what macros they go to? Ed I don't recall ever seeing any such keyboard organizer in 2007. Canned from a prev...

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 ...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

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 > > > . > ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

I can't find outlook express on my Mac?
I found a folder called Outlook express, but there is no actual program. I have Internet Explorer, should't I also have Outlook Express? http://www.google.com/search?num=100&hl=en&q=outlook+express+mac what version of IE is installed? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messa...

Receivings Number
Hi, When I am doing my purchase receiving at the Receivings Entry, I sometimes encounter some problem which the Receivings Number. For example, suppose the new receivings number should be REC/03/000004, but this number REC/03/000005 is given to me instead. What could be the possible cause of the missing receivings number:REC/03/000004. (this no. is not used by anyone). Please advise or tell me how can I avoid. Thanks in advance. I believe it's when someone started to do a receivings but then deleted it. It already increments to the next number. You can prevent deleting of documen...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

Limit number of characters in a cell
Hi, Is there a way to limit the number of characters that a user can key into a cell. I want to get a list of names and addresses imputted by user, but I don't want them to be more than 35 characters. Thanks for the help Dr. Senji Take a look at Data|Validation. You can have excel yell at the user when they hit enter after typing in a too-long string. Dr Senji wrote: > > Hi, > > Is there a way to limit the number of characters that a user can key into a > cell. > > I want to get a list of names and addresses imputted by user, but I don't > want the...

Counting the number of times more than 1 variable occurs
I need to determine how many Separations were processed by a particular salesperson. A | B 1 MIKE | SEPARATION 2 MIKE | LEAVE 3 SARA | SEPARATION 4 JOE | SEPARATION 5 JAMIE | LEAVE 6 MIKE | LEAVE 7 JOE | LEAVE 8 SARA | SEPARATION The only way I can think of is the COUNTIF Funtion *COUNTIF(-range,criteria-)* =COUNTIF(A1:A8,(A1:A8="MIKE")*(B1:B8="SEPARATION")) This does not work though. The results return 0 with no errors when the answer should be 1. does anyone else know of a wa...