Deleting Duplicate Rows 06-07-10
Hi, I checked out the archives for close to an hour, but I couldn't figure out how to change the codes given there to suit my needs. I have a large set of data, from about A1 to X441. In column X, there are a lot of exact duplicates, and I need to delete the rows where those duplicates are (but still leaving the first instance of the duplicate). For example: John Smith Oxford St. John Johnson Oxford St. John Johnson Rubble St. John Smith Oxford St. All of those have things in common, but I only want to delete the final row (and the whole row, not just the cell), because it is an exact duplicate. How do I code for this? Excel 2007. Thanks, Jbm
|
0 |
|
Utf
6/7/2010 8:06:22 PM
excel.programming
6508 articles. 
2 followers.
11 Replies
3371 Views
Select column X 2003 Data>Filter>Advanced Filter>Uniques only. Copy to another place. 2007 Data>Remove Duplicates. Unselect all. Select only column X and remove. Gord Dibben MS Excel MVP On Mon, 7 Jun 2010 13:06:22 -0700, Jbm <Jbm@discussions.microsoft.com> wrote: >Hi, >I checked out the archives for close to an hour, but I couldn't figure = out=20 >how to change the codes given there to suit my needs. >I have a large set of data, from about A1 to X441. In column X, there = are a=20 >lot of exact duplicates, and I need to delete the rows where those = duplicates=20 >are (but still leaving the first instance of the duplicate). For = example: > >John Smith Oxford St. >John Johnson Oxford St. >John Johnson Rubble St. >John Smith Oxford St. > >All of those have things in common, but I only want to delete the final = row=20 >(and the whole row, not just the cell), because it is an exact = duplicate. =20 >How do I code for this? Excel 2007. >Thanks, >Jbm
|
|
0 |
|
Gord
6/7/2010 8:30:54 PM
Another way that does not copy elsewhere IF? sorting is allowed. Assumes all text in ONE cell?? '== Option Explicit Sub SortAndDeleteDuplicatesSAS() Dim mc As Long Dim i As Long mc = 1 'column A Columns(mc).Sort Key1:=Cells(1, mc), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False,Orientation:=xlTopToBottom For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1 If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i).Delete Next i End Sub '==== -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Jbm" <Jbm@discussions.microsoft.com> wrote in message news:1FA919E8-69ED-4C39-BCB1-68FBCD6D24F9@microsoft.com... > Hi, > I checked out the archives for close to an hour, but I couldn't figure out > how to change the codes given there to suit my needs. > I have a large set of data, from about A1 to X441. In column X, there are > a > lot of exact duplicates, and I need to delete the rows where those > duplicates > are (but still leaving the first instance of the duplicate). For example: > > John Smith Oxford St. > John Johnson Oxford St. > John Johnson Rubble St. > John Smith Oxford St. > > All of those have things in common, but I only want to delete the final > row > (and the whole row, not just the cell), because it is an exact duplicate. > How do I code for this? Excel 2007. > Thanks, > Jbm
|
|
0 |
|
Don
6/7/2010 8:45:18 PM
=?Utf-8?B?SmJt?= <Jbm@discussions.microsoft.com> wrote in news:1FA919E8-69ED-4C39-BCB1-68FBCD6D24F9@microsoft.com: > Hi, > I checked out the archives for close to an hour, but I couldn't figure > out how to change the codes given there to suit my needs. > I have a large set of data, from about A1 to X441. In column X, there > are a lot of exact duplicates, and I need to delete the rows where > those duplicates are (but still leaving the first instance of the > duplicate). For example: > > John Smith Oxford St. > John Johnson Oxford St. > John Johnson Rubble St. > John Smith Oxford St. > > All of those have things in common, but I only want to delete the > final row (and the whole row, not just the cell), because it is an > exact duplicate. How do I code for this? Excel 2007. > Thanks, > Jbm Make a backup first just in case the results are not what you expect. Excel 2007 select the whole sheet ctrl+a, goto data>data tools>remove duplicates, in the dialogue box click unselect all and then select row X click ok this will remove the whole row A-X where X is a duplicate. Regards Steve
|
|
0 |
|
Steve
6/7/2010 8:49:04 PM
Well, those are not all duplicates, so what is the logic? John Smith Oxford St. = John Smith Oxford St. However, John Smith Oxford St. <> John Johnson Oxford St. Take a look at this: http://www.rondebruin.nl/easyfilter.htm Maybe you will have to run through the data a couple times, but that should do what you want. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jbm" wrote: > Hi, > I checked out the archives for close to an hour, but I couldn't figure out > how to change the codes given there to suit my needs. > I have a large set of data, from about A1 to X441. In column X, there are a > lot of exact duplicates, and I need to delete the rows where those duplicates > are (but still leaving the first instance of the duplicate). For example: > > John Smith Oxford St. > John Johnson Oxford St. > John Johnson Rubble St. > John Smith Oxford St. > > All of those have things in common, but I only want to delete the final row > (and the whole row, not just the cell), because it is an exact duplicate. > How do I code for this? Excel 2007. > Thanks, > Jbm
|
|
0 |
|
Utf
6/7/2010 8:57:54 PM
This assumes Column A is continuously populated from top to bottom of data
set. If not, then pick another column to use for finding last row. You can
also use the UsedRange property of the sheet if necessary.
Sub NoXDups()
Dim TestR As Long
Dim MyStr As String
For TestR = Range("A1").End(xlDown).Row To 1 Step -1
MyStr = Cells(TestR, "X").Value
If Range("X:X").Find(what:=MyStr, After:=Range("X1"), _
LookAt:=xlWhole).Row <> TestR Then
Rows(TestR & ":" & TestR).Delete shift:=xlUp
End If
Next TestR
End Sub
"Jbm" wrote:
> Hi,
> I checked out the archives for close to an hour, but I couldn't figure out
> how to change the codes given there to suit my needs.
> I have a large set of data, from about A1 to X441. In column X, there are a
> lot of exact duplicates, and I need to delete the rows where those duplicates
> are (but still leaving the first instance of the duplicate). For example:
>
> John Smith Oxford St.
> John Johnson Oxford St.
> John Johnson Rubble St.
> John Smith Oxford St.
>
> All of those have things in common, but I only want to delete the final row
> (and the whole row, not just the cell), because it is an exact duplicate.
> How do I code for this? Excel 2007.
> Thanks,
> Jbm
|
|
0 |
|
Utf
6/7/2010 8:58:08 PM
I tried your macro, but it doesn't seem to be working... Maybe the fact that there are headers is screwing it up? I've been working with your code since you posted it, but I can't get it to work correctly (Column A has data in every cell until the bottom of my data set).
|
|
0 |
|
Utf
6/7/2010 9:19:43 PM
Ryguy -- I can't install new software on this machine. Gord -- it's telling me that it removed 11 duplicates, and 400some unique values remain. Despite this, all the duplicates I can see are still there (and I am carefully checking to make sure they are the exact same.... They are).
|
|
0 |
|
Utf
6/7/2010 9:24:53 PM
Well this is deleting things, but not necessarily duplicates, and oftentimes cells instead of rows (which means correlated data is getting thrown off). Not all the data is in one cell, sorting would be allowed as long as the rows of data each stay together.
|
|
0 |
|
Utf
6/7/2010 9:34:24 PM
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Jbm" <Jbm@discussions.microsoft.com> wrote in message
news:BAC9AF70-9077-42FE-B2AA-BCCEB45CC466@microsoft.com...
>I tried your macro, but it doesn't seem to be working... Maybe the fact
>that
> there are headers is screwing it up? I've been working with your code
> since
> you posted it, but I can't get it to work correctly (Column A has data in
> every cell until the bottom of my data set).
|
|
0 |
|
Don
6/7/2010 9:34:44 PM
The headers would not prevent it working unless you had a blank row between the headers and the data, or no header in column A. Before posting, I tested this (Excel 2007 Pro) on a block of data with duplicates in column X and it worked just fine. And it accounts for all the conditions that appeared to need satisfying. From your sample data, it seems the duplicates may not be listed consecutively, and you specified the first instance should be the one left, so re-sorting the data is likely to create problems. If you've copied this section into a procedure that does other things as well, perhaps you could post the whole thing to see if there is some other factor causing it to fail. You don't happen to have any protection on the sheet do you? "Jbm" wrote: > I tried your macro, but it doesn't seem to be working... Maybe the fact that > there are headers is screwing it up? I've been working with your code since > you posted it, but I can't get it to work correctly (Column A has data in > every cell until the bottom of my data set).
|
|
0 |
|
Utf
6/7/2010 10:15:39 PM
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Jbm" <Jbm@discussions.microsoft.com> wrote in message
news:6F1418DF-9E77-4C86-B48D-094DC8132030@microsoft.com...
> Well this is deleting things, but not necessarily duplicates, and
> oftentimes
> cells instead of rows (which means correlated data is getting thrown off).
> Not all the data is in one cell, sorting would be allowed as long as the
> rows
> of data each stay together.
|
|
0 |
|
Don
6/7/2010 10:23:13 PM
Similar Artilces:
Where is the Keep Text Formatting feature located in Word 07
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...
Can I delete parts of Office?
I recently loaded Office 2004 on my iBook but want to conserve hard drive space for an app i just picked up, and was thinking about deleting all the MS apps except for Word and Excel. Can I do this without messing up Word and Excel to the point that I need to reinstall? I would just try it, but I'm away from home and don't have access to my Office install CD. Any help that could be provided would be great. Thanks <beau.keyes@gmail.com> wrote: > I recently loaded Office 2004 on my iBook but want to conserve hard > drive space for an app i just picked up, and was thinki...
test 05-11-10
hiiiiiiiiiiiiii ...
***Delete UNREAD email >90 Days (Exchange 2003)
I'm trying to delete all UNREAD emails older then 90 Days. Recipient Policy will only let me select the date but there isn't a selection for UNREAD emails. Exchange 5.5 Cleanup Agent (I think that's what it was called) had a check box for UNREAD emails. Is there way to do this in Exchange 2003, or is there 3rd party app that will do this? Boy I miss Exchange 5.5!!! James jdduong@comcast.net when you create a recipient policy (mailbox-managed), you can set the age in days (i.e. 90 days) and size in KB for all items in the selected folder for which the policy applies t...
Where is the Delete Messages options?
I've looked and looked without success. I'm pretty sure I saw it before. Its where it asks you if you want to save a copy of messages on the server and then should give an option for it to be deleted from the server when you delete it from your Deleted folder in Outlook. That seems like a good way of syncing things. Gary Gary wrote: > I've looked and looked without success. I'm pretty sure I saw it before. > Its where it asks you if you want to save a copy of messages on the server > and then should give an option for it to be deleted from the...
stop duplicate entries using validation
I have a named range (one entire column) where I would like to enter SSNs using the data form, But I do not want any duplicate entries. I know the validation function can be used! But I can't seem to find the way. can anyone help? Dennis, Use Data Validation. Select column A, goto menu Data>Validation, change the Allow option to custom, and enter a formula of =COUNTIF(A:A,A1)=1 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dennis" <denneca@earthlink.net> wrote in message new...
Server Application Unavailable 06-14-06
Dear All, In one of our customer's site CRM 1.2 is implemented on the following environment: SBS 2003 (with relevant SPs) SQL Server 2000 (with relevant SPs) MS CRM 1.2 Exchange MS CRM intermittently gives "Server Application Unavailable" whenever I try to access CRM. All the services are running. No event logs. Although the error message says that an Application Event Log for the Web Server has been logged, I dont see any log in the Event Viewer. And moreover CRM also displays the authentication form before giving that error. That means the CRM Authentication service is...
Adding new rows
I have a spreadsheet with 100 rows in, I need to add a row after each current row, is there a quick way to do this or have i got to insert each new row individually? -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=390760 I assume you want to add an empty new row in between the existing rows. One way would be with an extra column Save as "trial" first.......just in case thi...
Data displayed that is not equal to FALSE in a row
In row 1 cells A1-D1 contain formulas (IF/AND statements). In cell E1 I want whatever word that is not equal to “FALSE” to be displayed. A B C D E 1 FALSE FALSE Under FALSE Under The words can appear in any of cells A-D in each row as below. A B C D E 1 FALSE FALSE Under FALSE Under 2 FALSE On FALSE FALSE On 3 Over FALSE FALSE FALSE Over 4 FALSE FALSE FALSE On On Thank you in advance for your help. =SUBSTITUTE(A1&B1&C1&D1,FALSE,"") -- Gary''s Student - gsnu201001 Thanks Gary. That works great! "Gary'...
populate a listbox with values from rows in another worksheet
How would I populate a list box with values from rows in anothe worksheet -- Message posted from http://www.ExcelForum.com Hi Dovrox, > How would I populate a list box with values from rows in another > worksheet. > Give the range with the values a name (insert, name, define) and use that name as the listsource property. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com You'll have to use a named range. Select the range and press Ctrl+F3 and give it a name like "mylist" (no quotes). Then under Properties of the Listbox use mylist for the ListFillRange. H...
Conver Numbers to Log 10
How do I convert column of numbers (n=3000) to log 10 column of numbers? Assuming that your column is column A, one way: In an unused column, enter =LOG(A1) and copy down 3000 cells. Copy the column, choose column A and choose Edit/Paste Special, selecting the Values radio button. In article <0CDE6301-FC9C-488C-9D9F-3AD26B46EFCE@microsoft.com>, "Fishfan" <Fishfan@discussions.microsoft.com> wrote: > How do I convert column of numbers (n=3000) to log 10 column of numbers? ...
How do I change the row/column format in a macro to beyond letters?
Hi all, I recorded a very long VBA macro in Excel and the index of ranges is in letter format, for example, "M14:M19", etc. Now I am going to run this macro programmatically and automatically in a for loop and expand it from the left to the right so I want to change the "M" in the above example automatically. But after 26 letters, there will be AA, AB, etc. which is really hard to program. Is there a way to adapt the recorded macro (by changing as little as possible) to more than 26 letters. I really want to change as little as possible because I spent lots of time re...
Delete 3 end row
Hi I want to delete three end row of any sheet. any sheet has different row number. Thank's for any help. your's Hi, This will clear the last 3 rows of the active sheet. Note I the on error line is to cope when there are less than 3 rows on the sheet Sub del_Last_3() Dim LastRow As Long On Error Resume Next LastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row ActiveSheet.Rows(LastRow - 2 & ":" & LastRow).ClearContents End Sub -- Mike Whe...
Deleting a single character in a text field
In one of my tables i have a field that has a text values like '123.23123' i would like , to delete the full stop. How can i do this. I cant do it in excel as the number of records that i have is over 200000. hi, ma1000 wrote: > In one of my tables i have a field that has a text values like '123.23123' i > would like , to delete the full stop. How can i do this. I cant do it in > excel as the number of records that i have is over 200000. Create a update query, use Replace([yourField], ".", "") as new value. mfG --> stefan <-- ...
First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...
Excel XP Pivot Table moves incorrect field to row area
I have a problem that has been frustrating me for a while. I have built a number of workbooks that use Pivot Tables with an Analysis Server data source. When I drag a field from the page area to the row area in the Pivot Table, I occassionally get the wrong field. If I drag Location Type to the pivot table, location is added. If I drag financial class, insurance appears. The interesting (and infuriating) thing in this case is that Insurance was NOT in the page area. It is an available field in the data source, but had not been added to the workbook. Has anyone else had this occur and ho...
Prinding each row of Excel sheet on a seperate page dispallying one record on each pa
From a spread sheet ,I need to print out a row on each page seperately that is printout each record on the row on one page seperately. Can any one please advise how it can be done ? Fro example Row 1 Data 11 Data 12 Data 13 Row 2 Data 21 Data 22 Data 23 Row 3 Data 31 Data 32 Data 33 I would want to print out Data 11 and Data 22 on one page then Data 21 and Data 22 on the second page and data 31 and Data 33 on the third page Pleae advise if and hwo this is possible. Thank you Sanjeev --- Message posted from http://www.ExcelForum.com/ Sorry ...
Custom Report 03-19-10
There is no way for me to run a report on Buydown Pricing, is there anyone who has a report for this option? If not, can someone write a custom report for it, and how much might something like this run me? I would like it to do something like buydown quantity greater/less/equal and same for buydown price... please contact me at nagarra @ yahoo . com or respond here with your contact info! -- Thank You Vince :) ...
WLM Stopped working 02-20-10
I'm running Vista Home Premium, SP2, 2 GB of RAM, Firefox, WLM, all WU's current. I was in WLM mail newsgroups earlier today and things just stopped. WLM stopped responding. Now I cannot log into WLM. It used to log into window Live by itself (password and all) from my desktop icon, but now the only thing that happens when I try to open my WLM app window is I'm taken to IE (7). Then when when I try to sign in Windows live it won't do it. I've already ran SAS, MBAM, CCleaner and Avira in Safe mode but found nothing. Then I did a system restore back to a fe...
Outlook won't let me delete e-mail
I'm using Outlook 2002. Every time I try to delete an item, it tells me "Errors have been detected in the file ...outlook.pst. Quit all mail-enable applications, then use the Inbox Repair Tool." So I go and use the Inbox Repair Tool, SCANPST. I scan outlook.pst (n errors come up), re-open Outlook, try to delete an item and get th same error message. Can someone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Can you delete them if you hold shift-delet...
lookup row number
Hi, can you tell me how to lookup the row number of a name in a list. EG say I have a list of names as below: Andrew Mike Steve Ian Paul I would normally just add a column with the row numbers and then lookup the row number in the second column but is it possible to do without adding the extra column by using the row formula someohow? Thanks Hi Michael Try the below.. =MATCH("Mike",A:A,0) -- Jacob (MVP - Excel) "Michael" wrote: > Hi, > can you tell me how to lookup the row number of a name in a list. > > EG say I hav...
Email server 02-13-10
The smtp outgoing email with VISTA cannot be authenicated. Vista is not an e-mail client. Please post the error message in its entirety. You can left click on it to highlight and then right click to copy and then paste it into this thread. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Charles" <Charles@discussions.microsoft.com> wrote in message news:62A2017F-A531-4E34-89CC-7FE3BD9849F0@microsoft.com... > The smtp outgoing email with VISTA cannot be authenicated. ...
Identities 06-28-10
I had wanted to set up a separate identify to get my work e-mail as Windows Mail, rather than the webmail from my ISP; didn't know until after completing the process that Windows Mail combined both personal and work e-mail into one account. If I "undo" it, popup says I'll lose all of my work e-mail. How can I save it and undo the dual identities? -- mikezot Undo what? Windows Mail doesn't have identities. (boilerplate reply) You can add multiple accounts, via Tools - Accounts - Add, but Windows Mail doesn't have identities or a password option.......
outbox message will not send or delete
In outbox I have a file with a video clip in windows media. It will not send and I can not delete. Dialog box says outlook has already begun sending. thanks for any help ...
Context Menu with Bitmaps of Row and Column format
Hai Experts, I Want to have a Context menu with bitmaps(not text correspoding to that bitmap) displayed in Row and Column format , any ideas or links are appreciated and its just like a Messager "Smilyes" Button where in when i click on that button it displays some bitmaps in Row and Column format and please let me know about your sugestions Thanks and Regards suresh ...
I believe this Keep Text Formatting feature might be what I need, but I have been unable to locate exactly where it is located in Word 2007. I'm trying to rid a Word document sent to me of tables, text boxes, graphics and all other document formatting, while retaining the document's text content. It is unimportant to me whether the text formatting is retained or not. Thanks. Are you referring to a Keep Text Formatting feature in an earlier version of Word? I wonder whether what you're looking for is "Paste Unformatted," since you seem to be saying you _don...
Can I delete parts of Office?
I recently loaded Office 2004 on my iBook but want to conserve hard drive space for an app i just picked up, and was thinking about deleting all the MS apps except for Word and Excel. Can I do this without messing up Word and Excel to the point that I need to reinstall? I would just try it, but I'm away from home and don't have access to my Office install CD. Any help that could be provided would be great. Thanks <beau.keyes@gmail.com> wrote: > I recently loaded Office 2004 on my iBook but want to conserve hard > drive space for an app i just picked up, and was thinki...
test 05-11-10
hiiiiiiiiiiiiii ...
***Delete UNREAD email >90 Days (Exchange 2003)
I'm trying to delete all UNREAD emails older then 90 Days. Recipient Policy will only let me select the date but there isn't a selection for UNREAD emails. Exchange 5.5 Cleanup Agent (I think that's what it was called) had a check box for UNREAD emails. Is there way to do this in Exchange 2003, or is there 3rd party app that will do this? Boy I miss Exchange 5.5!!! James jdduong@comcast.net when you create a recipient policy (mailbox-managed), you can set the age in days (i.e. 90 days) and size in KB for all items in the selected folder for which the policy applies t...
Where is the Delete Messages options?
I've looked and looked without success. I'm pretty sure I saw it before. Its where it asks you if you want to save a copy of messages on the server and then should give an option for it to be deleted from the server when you delete it from your Deleted folder in Outlook. That seems like a good way of syncing things. Gary Gary wrote: > I've looked and looked without success. I'm pretty sure I saw it before. > Its where it asks you if you want to save a copy of messages on the server > and then should give an option for it to be deleted from the...
stop duplicate entries using validation
I have a named range (one entire column) where I would like to enter SSNs using the data form, But I do not want any duplicate entries. I know the validation function can be used! But I can't seem to find the way. can anyone help? Dennis, Use Data Validation. Select column A, goto menu Data>Validation, change the Allow option to custom, and enter a formula of =COUNTIF(A:A,A1)=1 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dennis" <denneca@earthlink.net> wrote in message new...
Server Application Unavailable 06-14-06
Dear All, In one of our customer's site CRM 1.2 is implemented on the following environment: SBS 2003 (with relevant SPs) SQL Server 2000 (with relevant SPs) MS CRM 1.2 Exchange MS CRM intermittently gives "Server Application Unavailable" whenever I try to access CRM. All the services are running. No event logs. Although the error message says that an Application Event Log for the Web Server has been logged, I dont see any log in the Event Viewer. And moreover CRM also displays the authentication form before giving that error. That means the CRM Authentication service is...
Adding new rows
I have a spreadsheet with 100 rows in, I need to add a row after each current row, is there a quick way to do this or have i got to insert each new row individually? -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=390760 I assume you want to add an empty new row in between the existing rows. One way would be with an extra column Save as "trial" first.......just in case thi...
Data displayed that is not equal to FALSE in a row
In row 1 cells A1-D1 contain formulas (IF/AND statements). In cell E1 I want whatever word that is not equal to “FALSE” to be displayed. A B C D E 1 FALSE FALSE Under FALSE Under The words can appear in any of cells A-D in each row as below. A B C D E 1 FALSE FALSE Under FALSE Under 2 FALSE On FALSE FALSE On 3 Over FALSE FALSE FALSE Over 4 FALSE FALSE FALSE On On Thank you in advance for your help. =SUBSTITUTE(A1&B1&C1&D1,FALSE,"") -- Gary''s Student - gsnu201001 Thanks Gary. That works great! "Gary'...
populate a listbox with values from rows in another worksheet
How would I populate a list box with values from rows in anothe worksheet -- Message posted from http://www.ExcelForum.com Hi Dovrox, > How would I populate a list box with values from rows in another > worksheet. > Give the range with the values a name (insert, name, define) and use that name as the listsource property. Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com You'll have to use a named range. Select the range and press Ctrl+F3 and give it a name like "mylist" (no quotes). Then under Properties of the Listbox use mylist for the ListFillRange. H...
Conver Numbers to Log 10
How do I convert column of numbers (n=3000) to log 10 column of numbers? Assuming that your column is column A, one way: In an unused column, enter =LOG(A1) and copy down 3000 cells. Copy the column, choose column A and choose Edit/Paste Special, selecting the Values radio button. In article <0CDE6301-FC9C-488C-9D9F-3AD26B46EFCE@microsoft.com>, "Fishfan" <Fishfan@discussions.microsoft.com> wrote: > How do I convert column of numbers (n=3000) to log 10 column of numbers? ...
How do I change the row/column format in a macro to beyond letters?
Hi all, I recorded a very long VBA macro in Excel and the index of ranges is in letter format, for example, "M14:M19", etc. Now I am going to run this macro programmatically and automatically in a for loop and expand it from the left to the right so I want to change the "M" in the above example automatically. But after 26 letters, there will be AA, AB, etc. which is really hard to program. Is there a way to adapt the recorded macro (by changing as little as possible) to more than 26 letters. I really want to change as little as possible because I spent lots of time re...
Delete 3 end row
Hi I want to delete three end row of any sheet. any sheet has different row number. Thank's for any help. your's Hi, This will clear the last 3 rows of the active sheet. Note I the on error line is to cope when there are less than 3 rows on the sheet Sub del_Last_3() Dim LastRow As Long On Error Resume Next LastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row ActiveSheet.Rows(LastRow - 2 & ":" & LastRow).ClearContents End Sub -- Mike Whe...
Deleting a single character in a text field
In one of my tables i have a field that has a text values like '123.23123' i would like , to delete the full stop. How can i do this. I cant do it in excel as the number of records that i have is over 200000. hi, ma1000 wrote: > In one of my tables i have a field that has a text values like '123.23123' i > would like , to delete the full stop. How can i do this. I cant do it in > excel as the number of records that i have is over 200000. Create a update query, use Replace([yourField], ".", "") as new value. mfG --> stefan <-- ...
First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...
Excel XP Pivot Table moves incorrect field to row area
I have a problem that has been frustrating me for a while. I have built a number of workbooks that use Pivot Tables with an Analysis Server data source. When I drag a field from the page area to the row area in the Pivot Table, I occassionally get the wrong field. If I drag Location Type to the pivot table, location is added. If I drag financial class, insurance appears. The interesting (and infuriating) thing in this case is that Insurance was NOT in the page area. It is an available field in the data source, but had not been added to the workbook. Has anyone else had this occur and ho...
Prinding each row of Excel sheet on a seperate page dispallying one record on each pa
From a spread sheet ,I need to print out a row on each page seperately that is printout each record on the row on one page seperately. Can any one please advise how it can be done ? Fro example Row 1 Data 11 Data 12 Data 13 Row 2 Data 21 Data 22 Data 23 Row 3 Data 31 Data 32 Data 33 I would want to print out Data 11 and Data 22 on one page then Data 21 and Data 22 on the second page and data 31 and Data 33 on the third page Pleae advise if and hwo this is possible. Thank you Sanjeev --- Message posted from http://www.ExcelForum.com/ Sorry ...
Custom Report 03-19-10
There is no way for me to run a report on Buydown Pricing, is there anyone who has a report for this option? If not, can someone write a custom report for it, and how much might something like this run me? I would like it to do something like buydown quantity greater/less/equal and same for buydown price... please contact me at nagarra @ yahoo . com or respond here with your contact info! -- Thank You Vince :) ...
WLM Stopped working 02-20-10
I'm running Vista Home Premium, SP2, 2 GB of RAM, Firefox, WLM, all WU's current. I was in WLM mail newsgroups earlier today and things just stopped. WLM stopped responding. Now I cannot log into WLM. It used to log into window Live by itself (password and all) from my desktop icon, but now the only thing that happens when I try to open my WLM app window is I'm taken to IE (7). Then when when I try to sign in Windows live it won't do it. I've already ran SAS, MBAM, CCleaner and Avira in Safe mode but found nothing. Then I did a system restore back to a fe...
Outlook won't let me delete e-mail
I'm using Outlook 2002. Every time I try to delete an item, it tells me "Errors have been detected in the file ...outlook.pst. Quit all mail-enable applications, then use the Inbox Repair Tool." So I go and use the Inbox Repair Tool, SCANPST. I scan outlook.pst (n errors come up), re-open Outlook, try to delete an item and get th same error message. Can someone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Can you delete them if you hold shift-delet...
lookup row number
Hi, can you tell me how to lookup the row number of a name in a list. EG say I have a list of names as below: Andrew Mike Steve Ian Paul I would normally just add a column with the row numbers and then lookup the row number in the second column but is it possible to do without adding the extra column by using the row formula someohow? Thanks Hi Michael Try the below.. =MATCH("Mike",A:A,0) -- Jacob (MVP - Excel) "Michael" wrote: > Hi, > can you tell me how to lookup the row number of a name in a list. > > EG say I hav...
Email server 02-13-10
The smtp outgoing email with VISTA cannot be authenicated. Vista is not an e-mail client. Please post the error message in its entirety. You can left click on it to highlight and then right click to copy and then paste it into this thread. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Charles" <Charles@discussions.microsoft.com> wrote in message news:62A2017F-A531-4E34-89CC-7FE3BD9849F0@microsoft.com... > The smtp outgoing email with VISTA cannot be authenicated. ...
Identities 06-28-10
I had wanted to set up a separate identify to get my work e-mail as Windows Mail, rather than the webmail from my ISP; didn't know until after completing the process that Windows Mail combined both personal and work e-mail into one account. If I "undo" it, popup says I'll lose all of my work e-mail. How can I save it and undo the dual identities? -- mikezot Undo what? Windows Mail doesn't have identities. (boilerplate reply) You can add multiple accounts, via Tools - Accounts - Add, but Windows Mail doesn't have identities or a password option.......
outbox message will not send or delete
In outbox I have a file with a video clip in windows media. It will not send and I can not delete. Dialog box says outlook has already begun sending. thanks for any help ...
Context Menu with Bitmaps of Row and Column format
Hai Experts, I Want to have a Context menu with bitmaps(not text correspoding to that bitmap) displayed in Row and Column format , any ideas or links are appreciated and its just like a Messager "Smilyes" Button where in when i click on that button it displays some bitmaps in Row and Column format and please let me know about your sugestions Thanks and Regards suresh ...