copy range of unique value

Hi,

i use Excel 2007 and i have a strange behavior with a particular thing 
that i do.

quite often i have list of values with several times the same values. 
e.g.: 2,3,8,5,4,3,9 from cell A1 to A7

when i use the function:
Data => Sort & Filter => Advanced => Copy to another location, list 
range $A$1:$A$7, copy to $D$1 and checked Unique records only.

i correctly get 2,3,8,5,4,9
it means second times that 3 is found, it is removed from the new list.

i have also a good result with only alpha value and only numerical values.

my main problem is when cells have alphanumerical values like
$A$1 = note0450
$A$2 = note0521
$A$3 = note0450
$A$4 = note8427
$A$5 = note0017

i get as result:

$D$1 = note0450
$D$2 = note0521
$D$3 = note0450
$D$4 = note8427
$D$5 = note0017

in this case nothing was removed. it's like i've never checked the 
Unique records only check box :-(

or if u try somewhere else on the sheet.
$A$1 = pc05
$A$2 = pc54
$A$3 = pc923
$A$4 = pc43
$A$5 = pc05
$A$6 = pc43
$A$7 = pc98
$A$8 = pc43

i get:
$D$1 = pc05
$D$2 = pc54
$D$3 = pc923
$D$4 = pc43
$D$5 = pc05
$D$6 = pc98

in this case something was removed but not all. for example, pc05 is 
another time.

what is the problem ?
if someone wants the file as example i can send him :-)

thanks a for help.

Al.
0
Alain
1/26/2010 12:43:05 PM
excel 39879 articles. 2 followers. Follow

1 Replies
1045 Views

Similar Articles

[PageSpeed] 40

It's because the first item is treated as a title. The REST is treated as 
data with unique values. So if your title matches your data, you'll see it 
twice. Give it a real title, like "The List" and you'll always see "The 
List" as the first item, but the rest will be unique. It has nothing to do 
with values being numeric or not.
HTH
Bob Umlas
Excel MVP

"Alain R." <nospam@noemail.com> wrote in message 
news:OygYcUonKHA.1544@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> i use Excel 2007 and i have a strange behavior with a particular thing 
> that i do.
>
> quite often i have list of values with several times the same values. 
> e.g.: 2,3,8,5,4,3,9 from cell A1 to A7
>
> when i use the function:
> Data => Sort & Filter => Advanced => Copy to another location, list range 
> $A$1:$A$7, copy to $D$1 and checked Unique records only.
>
> i correctly get 2,3,8,5,4,9
> it means second times that 3 is found, it is removed from the new list.
>
> i have also a good result with only alpha value and only numerical values.
>
> my main problem is when cells have alphanumerical values like
> $A$1 = note0450
> $A$2 = note0521
> $A$3 = note0450
> $A$4 = note8427
> $A$5 = note0017
>
> i get as result:
>
> $D$1 = note0450
> $D$2 = note0521
> $D$3 = note0450
> $D$4 = note8427
> $D$5 = note0017
>
> in this case nothing was removed. it's like i've never checked the Unique 
> records only check box :-(
>
> or if u try somewhere else on the sheet.
> $A$1 = pc05
> $A$2 = pc54
> $A$3 = pc923
> $A$4 = pc43
> $A$5 = pc05
> $A$6 = pc43
> $A$7 = pc98
> $A$8 = pc43
>
> i get:
> $D$1 = pc05
> $D$2 = pc54
> $D$3 = pc923
> $D$4 = pc43
> $D$5 = pc05
> $D$6 = pc98
>
> in this case something was removed but not all. for example, pc05 is 
> another time.
>
> what is the problem ?
> if someone wants the file as example i can send him :-)
>
> thanks a for help.
>
> Al. 

0
Bob
1/26/2010 2:55:02 PM
Reply:

Similar Artilces:

Copying subfolders
I have read several useful posts concerning FileCopy. I have not found one that will copy all the files and subfolders under a folder. Is this possible? -- Arkansas Lady The DOS xCopy command will do that Start >>> Run >>> cmd then type: xcopy /? to get a list of commands. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "Arkansas Lady" <ArkansasLady@discussions.microsoft.com> wrote in message news:93826857-7D0F-4E05-B4AD-9EEBD1ECD989@microsoft.com... >I have read several u...

How to change the string value in the registry?
How to change the "string value" in the registry from the code? As there is a functionality in my application that the user can change the "string value" with some GUI. But I m not able to change it as there is a function RegReplaceKey but its only to take the backup of old file and to replace it with new one and even i dont know how to use this functin to suit my case. RegSetValue/Ex() should do it I think. -Seetharam Look at the Registry APIs. Also, take a look at my Registry class on my MVP Tips site. joe On Sun, 26 Aug 2007 23:59:33 -0700, HItz <hitesh_im...

Getting rid of #value
How am I able to add a column of numbers when one of the numbers in the formula is itself a formula that results in #VALUE (because that number the product of zero times another number)? Thanks First, the product of zero and another number shouldn't be giving you #VALUE!. Your best bet is to fix or bypass whatever is causing the #VALUE! error. If you can't do that for some reason, array-enter (CTRL-SHIFT-ENTER or CMD-RETURN): =SUM(IF(ISERROR(A1:A20),"",A1:A20)) In article <_e6dnZvUCo7b1mHdRVn-sw@comcast.com>, "Craig" <jcstone1@comcast.net>...

Range Names
I need to figure out how to automatically assign dynamic range names to each "Saturday to Friday" in a list of monthly day numbers and associated day names for an entire year. A B 1 Sat 2 Sun 3 Mon 4 Tue 5........etc. I don't know where to start. Thanks, Wes Hi Wes, Depending on what you wish to do, XL's built in WeekNum function may assist. --- Regards, Norman "Wes" <additude@texstapes.com> wrote in message news:uV1CDQweFHA.3836@tk2msftngp13.phx.gbl... >I need to figure out how to automatically assign dynamic range name...

Data Validation using List (But needs unique list in drop down lis
Hi all, In sheet 1, column A is my title name while column B is person name. Sheet 1 is my database where i do data entry in this. In sheet 2, contains my query page. In cell A5, i uses data validation - list, on this cell. Say in sheet 1 : column A column B XXXXXXX Mr A YYYYYYYY Mr A ZZZZZZZZ Mr A AAAAAAA Mr B WWWWW Mr C DDDDDDD Mr C But In sheet 2, cell A5, I saw in the drop down list as follows: Mr A Mr A Mr A Mr B Mr C Mr C But i want to see this in cell A5 instead (Unique name that is) : Mr A Mr B Mr C ...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Why cannot copy autorun.inf across network drive
I've copied a CD software into my harddisk. Then I copy to another PC. But I find that it's not possible to copy the autorun.inf over. "Access is denied" I wonder why ? Thanks "dingdongdingding" <dingdongdingding@yahoo.com> wrote in message news:54c040ef-d6e7-4e82-9727-5260929a8e35@q39g2000prh.googlegroups.com... > I've copied a CD software into my harddisk. Then I copy to another > PC. But I find that it's not possible to copy the autorun.inf over. > "Access is denied" I wonder why ? One solution (among many): ...

'Counter' value of the 'Perflib' subkey
I have a program which queries the 'Counter' value of the 'Perflib' subkey in the Windows registry using RegQueryValueEx. It used to work fine but now the debug build of my program is broken on Windows 2008 Server R2. The 'Counter' value is a REG_MULTI_SZ type data and is terminated with two NULL characters. However, I found out for Windows 2008 Server R2, the value returned from RegQueryValueEx has an extra character(0xcdcd) after the two NULL characters, which ends the 'Counter' string value. And that extra character causes my program to fail ...

copy & paste
i currently use Excel 2003 and have a copy and paste query. i have a spreadsheet called "Orders Wk1" that has tabs for each day of the week and each tab has several column headings i.e Customer 1, Customer 2, Customer 3 etc. I would like a macro that identifies a particular column heading (i.e Customer 2) and then copies the contents of that single column into a separate spreadsheet called "Order Summary"; this needs to be done for each day of the week. The idea is to build up a history of daily orders for Customer 2. Also. each time the macro is activated the data needs...

Problem copy/pasting images from IE8 to MS Office 2007
I've got a weird problem that just started happening last week on one of my PCs (running Vista Home Premium). If I copy an image from IE8, and paste it into MS Office (Word, PPT, Outlook) the first one works fine, but then when I paste subsequent images, I get a copy of the first one. I'm not sure how to clear the clipboard, but I don't think that's even the problem. It gets more weird... When I paste the second time (or any subsequent copy/pastes after the first), it knows the size of whatever item I copied to the clipboard, but it pastes the first image distorted...

Copy & Paste Data from Web into Excel
When I do the above & use Vlookup for numerical data the numeric data is not recognised, although some data are in another excel workbook. How can I sort the data from the web so that I can do a proper vlookup What looks like a numeric field is actually text. This trick often works: In an empty cell enter the value 1 and copy it Select the offending numbers and use Edit/Paste Special->multiply Delete the cell with the 1 Best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Kelvin Pakaree" <k.pakaree@credcor.co.za> wrote in mes...

Copying inbox from one account to another on exchange 2003
Hi I need to copy the inbox of user from one account to another account. Is there a way to do that? I tried copying to the public folder then copying from public to new account - but the dates changed to todays date for all emails! Any thoughts? Use PSTs. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Ali" <Ali@discussions.microsoft.com> wrote in message news:B67F087A-39D7-4DBA-947D-F20F63574952@microsoft.com... > Hi > I need to copy the inbox of user from one account to another account. Is > t...

copying text on sheet 1 to corresponding cells on sheets 2 to 5
I want the text entered in A8 on sheet 1(named Productivity) to automatically transfer to A8 on sheets 2 thru 5. I used the simple formula =Productivity!a8 in the corresponding cells but when there is no data in Productivity A8 I get a 0 in the corresponding cells. I would like the corresponding cells to remain blank if there is not text in Productivity A8. I am really new to excel, like only used it two weeks! Is there a simple fix to this? -- newbie at large! Hi A formula will always return 0 if it's refering to an empty cell. This formula will let the cell look empty if no...

VBA Code to Open File1; Open File2 copy sheets to File1
Using XL 2003 & 97 Need help with the following VBA code: Sub Macro1() Dim myNewWorkbook As String myNewWorkbook = ActiveWorkbook.Name Workbooks.Open Filename:="OldWorkbookFile.xls" Sheets(Array("Formulas", "Department Lables")).Activate Sheets(Array("Formulas", "Department Lables")).Copy _ Before:=Workbooks(myNewWorkbook).Sheets(2) Windows("OldWorkbookFile.xls").Activate ActiveWorkbook.Close Windows(myNewWorkbook).Activate ' ........ End Sub My goal is to have myNewWorkbook active then:...

How do you get the attribute value using XPath in VB.Net 2003?
Hi, How do you get the attribute value using XPath in VB.Net 2003? Many thanks, aushknotes "aushknotes" <aushknotes@discussions.microsoft.com> wrote in message news:508426F2-1C8A-4AD2-A52E-B80B9798AC0C@microsoft.com... > Hi, > > How do you get the attribute value using XPath in VB.Net 2003? > Prefix @ to the name of the attribute value. XmlAttribute attrib = (XmlAttribute)dom.selectSingleNode("/path/@attributeName"); -- Anthony Jones - MVP ASP/ASP.NET aushknotes wrote: > How do you get the attribute value using XPath in VB.Net 2003? ...

retrive value from lookup screen
hi, i hv small problem i make refrence for smartlist (SL)in GP form to enable me open the SL lookup form ,and i add lookup button to open that smart list but i want the retive value from SL to be inserted in text box exist on GP form please help me ...

Adding Hyperlink to multiple values within a cell
My spreadsheet contains a list of people. The cell next to each nam contains multiple numeric values for identifying a specific piece o information. I would like to be able to click on one of those number (value) and a comment window pop up with the information associate with it, or be hyperlinked to the information further down th speadsheet. I want to avoid using multiple cells for this. Is this possible? Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this...

copy all incoming and outgoing mail to alternate mailbox?
I have been tasked with finding a way to monitor some user (s) so that we can keep a copy of all mail sent to and from that user without their knowledge. How can this be done? I am using W2003 AD / Exchange 2003. Take a look at SelectiveJournaling application at www.ivasoft.biz - it could help. -- Regards, Victor Ivanidze, software developer www.ivasoft.biz "Jim Rodgers" <jim.rodgers@bataviatrans.com> wrote: news:17c6f01c44962$0ce121c0$a001280a@phx.gbl... > I have been tasked with finding a way to monitor some user > (s) so that we can keep a copy of all mail se...

My recipients recieving multiple copies
I'm using Outlook 2002 with Win XP Pro. I have dial up (POP3 & SMTP) in a little town with old phone lines. My recipients (never more than 50) sometimes recieve from 2 to 10 copies of the same message. I have increased the server time-out time from 1 minute to 9 minutes ( I figured that out on my own) but I'm not yet sure if that's the solution because my ISP guy tells me that Outlook really wasn't designed to send one message to more than 3 recipients esp. Bcc (I find that remark very hard to believe). My ISP guy tells me I should get an email program designed t...

building a range for SetSourceData
I am trying to build a range object to feed a chart input. I want to move from old to new: ' OLD myChart.SetSourceData Source:=Sheets("Main Sheet").Range( _ "DF71:IS71,DF73:IS73"), PlotBy:=xlRows ' NEW myChart.SetSourceData Source:=ws , PlotBy:=xlRows Here is the way I built the robj. I use the other range objects to collect row and column info. What am I doing wrong? Dim ws As Range Set ws = myWs.Range( _ myWs.Range(myWs.Cells(Date_Week.row, FirstDataCol.Column), myWs.Cells(Date_Week.row, LastDataC...

Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the values of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that still leads to the same issues. How can I do this? As example - I have a form w...

Name range within a name range
In Excel is it possible to create a name range that contains multiple name ranges. As an example, I have created several name ranges with one cell reference, such as Sales(A2), COS(A5), ADM(A7), SELLING(A8), MRKT(A9). I would like to create a name range that I could include the all the name ranges and call it GROSS_PROFIT. If later I add another range I would like to be able to just add the range name to GROSS_PROFIT, such as R&D. Also the reason I chose to use named ranges is my cell references can change from month to month, I find it easier to create formulas using name refer...

Reverse sign of range
I have a routine that reverses the sign of a list of data. This particularly useful when entering a list of items and discovering that they're the wrong way round. However, it relies on cell A1 being available to create the -1 in the clipboard. The drawback with this is that it doesn't work on protected sheets (unless A1 is unprotected, of course). Is there a better way of putting -1 into the clipboard ? Sub Paste_Minus() ' Check that a file is open before attempting to run the procedure If IsFileOpen() = False Then Exit Sub ' cFormula is the formula c...

Copying messages to CD
I have bought a laptop, and I need to copy my messages, and folders from my old PC,to CD from Outlook Express, then load them from the CD to my new laptop running Outlook. Any suggestions? Thanks Use the Simple Backup mehod described here http://www.insideoe.com/backup/ Import the messages and addresses into OE on your laptop Open Outlook, select File-Import-Import Internet Mail and Adresses Regards Mark Dormer <demarte@bigpond.net.au> wrote in message news:1153033346.337593.137330@h48g2000cwc.googlegroups.com... >I have bought a laptop, and I need to copy my messages, and fo...

Add values in a column according to value in another column
How can I add the values in a column according to values in another column? If there is any value in a row in column B, I want to include the value of the corresponding row in column A. I'm flexible as to whether this is ANY value (i.e. not empty) or greater than zero. Hi Paul Maybe something like this =IF(B1="","",IF(B1>0,B1+A1)) Regards Cimjet "Paul Kaye" <paulmjkaye@gmail.com> wrote in message news:05befaf3-9ba8-48c8-aebb-654f0269d1dc@34g2000hsf.googlegroups.com... > How can I add the values in a column according to values in another > colu...