+ Reply to Thread
Results 1 to 20 of 20

deleting lines

  1. 07-14-2007, 01:40 PM #1
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690

    deleting lines

    hi guys

    i have a spreadsheet with approxiamtley 4982 lines on it
    column a is a date
    column b is a time

    now and again column a and column b have the same answer in them
    is there anyway i can highlight these as i only want one of the lines so that i can delete the other without having to scroll down them manually?

    regards
    steve
    Reply With QuoteRegister To Reply
  2. 07-14-2007, 01:56 PM #2
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    See if this helps. I concatenated the time and date in Col C.

    Also this site shows you how to highlight duplicates

    http://www.cpearson.com/excel/duplic...tingDuplicates

    VBA Noob
    Attached Files Attached Files
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!
    Reply With QuoteRegister To Reply
  3. 07-14-2007, 02:08 PM #3
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    thats of great help but of no real help noob
    although im grateful for your help i really dont understand formulas
    Reply With QuoteRegister To Reply
  4. 07-14-2007, 02:20 PM #4
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi stevesunfold,

    Just add the code to a module in your workbook then use this forumla in cell C2 and drag down

    =TEXT(A2,"dd/mm/yy")&" "&TEXT(B2,"HH:MM")

    module.

    Please Login or Register  to view this content.
    The code will run from the last cell in Col C to C2 deleting the entirerow as it goes.

    Non VBA would be use this in C2 and drag down

    =IF(MAX(COUNTIF(C2:C20,C2:C20))>1,"Duplicates","No Duplicates")
    Then filter on Duplicates and delete

    HTH

    VBA Noob
    Reply With QuoteRegister To Reply
  5. 07-14-2007, 02:47 PM #5
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    vba noob

    what can i say but youre a star

    many many thanks
    i did what you said and although i dont understand it it worked a treat

    thanks so much
    steve
    Reply With QuoteRegister To Reply
  6. 07-14-2007, 02:48 PM #6
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Glad it helped

    VBA Noob
    Reply With QuoteRegister To Reply
  7. 07-14-2007, 02:53 PM #7
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    it was more than a help it was fantstic i need to learn excel more but i just cant take it in like some.

    one more question please is there anyway i can extract info from one cell
    for example

    in cell b1 i have a time followed by a track

    ie

    2:20 lingfield is there anyway i can get the lingfield part of the cell into say column c?

    regards
    Reply With QuoteRegister To Reply
  8. 07-14-2007, 02:58 PM #8
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    =TRIM(MID(B1,FIND(" ",B1),9999))

    VBA Noob
    Reply With QuoteRegister To Reply
  9. 07-14-2007, 03:12 PM #9
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    once again fantastic
    does exactly that but still leaves the time and the track in the first cell b1 where i did want it to be just the time

    gonna save then go have a play

    will let you know how i get on

    many many thanks
    if you are into horse racing let me know mate
    Reply With QuoteRegister To Reply
  10. 07-14-2007, 03:21 PM #10
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    No problem

    VBA Noob
    Reply With QuoteRegister To Reply
  11. 07-14-2007, 03:32 PM #11
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    hi vba

    i cant do it and i thought that would be easy after having the formula for the earlier trim

    guess im just not to use excel

    in my opinion its one of the best if not the best program ever made

    steve
    Reply With QuoteRegister To Reply
  12. 07-14-2007, 03:36 PM #12
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Post a sample of the data

    VBA Noob
    Reply With QuoteRegister To Reply
  13. 07-14-2007, 03:41 PM #13
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    this is what i have showing now

    01-Jan-07 2:45 Southwell Southwell
    Reply With QuoteRegister To Reply
  14. 07-14-2007, 03:57 PM #14
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe text to columns will help using space as the delimiter

    http://www.bettersolutions.com/excel...M315128331.htm

    if not try using two helper columns say C & D.

    In C1 enter

    =LEFT(B1,FIND(" ",B1))

    and in D1 enter

    =TRIM(MID(B1,FIND(" ",B1),9999))

    Then drag down. Select cols C & D and paste special values

    http://www.bettersolutions.com/excel...I216522111.htm

    and delete column B

    VBA Noob
    Reply With QuoteRegister To Reply
  15. 07-14-2007, 04:26 PM #15
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    VOOB

    thank you so so much i owe you

    you have saved me so much time thank u
    Reply With QuoteRegister To Reply
  16. 07-14-2007, 04:33 PM #16
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Glad it helped

    VBA Noob
    Reply With QuoteRegister To Reply
  17. 07-14-2007, 04:57 PM #17
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    VBA Noob Do You Know If It Is Possible To Extract Data From A Web Sheet If You Have The Source?
    And Can A Macro Do This?



    THANKS
    Last edited by VBA Noob; 07-14-2007 at 05:23 PM.
    Reply With QuoteRegister To Reply
  18. 07-14-2007, 05:26 PM #18
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    See link

    http://www.mrexcel.com/tip103.shtml

    Why do you need a macro ??

    VBA Noob
    Reply With QuoteRegister To Reply
  19. 07-14-2007, 05:48 PM #19
    stevesunfold is offline
    Forum Contributor
    Join Date
    06-23-2007
    Posts
    690
    To Be Honest Im Looking To Extract Certain Parts Of Data From A Web Page

    Ie A Football Result From A Page And Then Another One From Another Page

    Etc Etc And Copying And Pasting Is Such A Bind
    Reply With QuoteRegister To Reply
  20. 07-14-2007, 05:52 PM #20
    VBA Noob is offline
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Link should help

    VBA Noob
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1