+ Reply to Thread
Results 1 to 73 of 73

Delete a row with conditions for same ID

  1. #1
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Delete a row with conditions for same ID

    HI all,

    thank you for the help you could provide.
    I'm currently busy with a code to sort a lot of data and I'm stuck ...

    I need to deleted specific rows with same ID but non-matching values (without taking into account blanks)

    To help you :

    ID Value1 Value2 Value3 Value4
    A1 21 22 24
    A1 21 23 24
    A2 12 12 13
    A2 12 13 14
    A2 13 13 14


    in this table, I would keep both rows for A1 because 1) they match the same ID, 2) All values are similar (I'm not taking into account blanks) but I would delete all A2 rows because Value2 is different.

    Any idea how to compare column's value for same ID ?

    thank you

    franck

  2. #2
    Valued Forum Contributor
    Join Date
    02-06-2014
    Location
    N/A
    MS-Off Ver
    N/A
    Posts
    373

    Re: Delete a row with conditions for same ID

    Hi, when a duplicate is detected, how would you determine which row to keep and which row(s) to delete?

  3. #3
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Hi Poizhan,

    the criteria would be the comparison of values per column for a same ID, if they match = I keep, if not = I deleted all record for this ID

    does it sound clear enough ?

    thank you

  4. #4
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    I struggle making a comparison between values for the same ID

    Probably because my way of selecting them is not appropriated. I use application.countif to determine if the ID is repeated more than once, then I don't know how to make the comparison with the "same ID value".

    I might need something more "powerful"

    thanks

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Seems like you want to delete the lines that have no common value within the same column for each unique ID.
    What if you have another line like this?
    Which line(s) do you want to keep/delete?
    ID Value1 Value2 Value3 Value4
    A1 21 22 24
    A1 21 23 24
    A2 12 12 13
    A2 12 13 14
    A2 13 13 14
    A2 12 12 14

  6. #6
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Hi Jindon,

    in your exemple,

    All A2 IDs would be deleted because column "value2" has more than one different value for the sameID

  7. #7
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    To explain you what this s about :

    IDs are rings, which are birds
    Values are genotype values.

    My table is gathering genotypes values for all birds we follows and we could have caught and sampled the same bird more than once (it is why you could have more than one record for the same id) but we don't want to keep the analyses which differ because a genotype remains the same always. In this case, we will sample this bird again and run a new analyse.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Not sure though...
    Assuming Data from columnA and row1 has header.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    thank you

    I though it was a matter of dictionary but every time I try to use it on my mac, I have the ActiveX issue, I tried to solve it in vain ....

    any clue to sort this out ?

    thank you

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    You need to change your profile then.

    If your profile states Mac, I wouldn't post the code in this thread...

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    See if this works..
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    My mistake sorry ... I forgot to edit it



    ----Edit ---


    I realised asking you to remove the rows doesn't solve the issue in fact ! because if on next season we catch and sample this bird again, the genotype will be different anyway from the other ones (even if it is correct) and following this rule will delete all IDs again and ask to sample this ID again ...can't work

    we shall keep the record with the max(repeated genotype).
    Last edited by benfontein; 04-20-2018 at 05:42 AM.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    See and try my last post.

  14. #14
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    It doesn't work.


    could you please add comment even if I more or less understand.

    Your level is indeed higher than mine and I couldn't do this by myself

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Yep, the code doesn't work...
    If you say
    we shall keep the record with the max(repeated genotype).
    I need a sample data and the result.

    If you upload a small sample workbook, I can work on it.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  16. #16
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Sorry for the delay,

    I had to reconsider everything.


    My wish is slightly different than what I asked and might be easier : I would like to clear all different values for same ID without taking into account blank values


    I guess this is the only think I don't know/see how to do and I will include your script in mine (after I calculate the number of blank values per row and copy/delete if >2)


    please find the attached file fr more information.


    please could you detail your script as I can try to learn from it


    Thank you
    Attached Files Attached Files
    Last edited by benfontein; 04-20-2018 at 11:50 AM.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Try
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Thank you Jindon, it works.

    I forgot to mention I could have more than 2 ID with different values, I'm trying to sort that by myself but I struggle a bit.

    just need to add an other loop ?
    can you help ?


    but this is not the worst. I just learnt that values between alleles could change and the order means nothing... Nevertheless, alleles values will always be written side by side

    this is showed is the attached file



    thank you
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    I need a clear logic.

    1) Unique ID is a combination of "Metal Ring"(Col.A) and "Ase18"(Col.B).
    2) Column value need to be blank when only 2 identical ID with different values.
    3) Leave most common value in each column when more than 3 different values and make other blank.

    Is this correct?

  20. #20
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    1) what I call ID, is metal ring only.
    I guess you could not have a unique ID because even if you concatenate all columns' values, there are duplicates in this file.

    Do you need a unique ID ?

    2/3) For a same ID : Column's value needs to be blank when values are different from 1) the values in the same column 2) the values in the column with the same name .
    You can have >2 same ID in the file





    an idea: for same columns' name you can't have odd numbers' values. If so, values from the column with not an even number of values can be cleared
    does it help ?

    bad idea
    doesn't fit
    Last edited by benfontein; 04-23-2018 at 05:13 AM.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Confusing.
    an idea: for same columns' name you can't have odd numbers' values. If so, values from the column with not an even number of values can be cleared
    1)
    Take BH18745 has 3 duplicates and the result for "GSW15" is 187, which is an even number, where you have 190 & 187 in original.
    2)
    Take BH23967 has 2 duplicates, "Ase18"(Col.B & C) has both different values, but both are not cleared.
    Last edited by jindon; 04-23-2018 at 05:09 AM.

  22. #22
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Yes indeed, I just realised it ... I edited my previous post



    I assume we could test if

    from column 2 to 33

    for same ID, in
    for even number : value = (row+1,colum).value and value = (row,colum-1).value

    for odd number : value = (row-1,colum).value and value = (row,colum-1).value

    something like this ?

    Sorry I'm trying to help but perhaps it doesn't help
    Last edited by benfontein; 04-23-2018 at 05:21 AM.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Does the workbook uploaded follow your logic?

  24. #24
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Sorry but I see not workbook uploaded ...

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    I mean your latest workbook uploaded.

  26. #26
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    I hope it is

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Still confusing.

    why;

    1) Take BH18745 in row 2,3 & 4, "GSW15" has 190,187,187 and the result is 187, "CAM-01" has 334,334,333 and the result is 334.

    2) BH23967 in row 13 & 14, "Ase18" both col.B & C has different value but stay the same.

  28. #28
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    JINDON !!


    I have a "solution" to helps us (actually you )

    The idea could be to sort by numerical order he value for the same column.

    I asked the lab to understand and they always write the shortest length (smaller number)first, but in 1% case there is a mistake. Hence we could "solve" this issue by sorting the value with the same name column

    Then we could apply your first script (just need to consider we could have >2 identical ID)


    Does it make cense ?

  29. #29
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Quote Originally Posted by jindon View Post
    Still confusing.

    why;

    1) Take BH18745 in row 2,3 & 4, "GSW15" has 190,187,187 and the result is 187, "CAM-01" has 334,334,333 and the result is 334.

    2) BH23967 in row 13 & 14, "Ase18" both col.B & C has different value but stay the same.

    Sorry but I don't understand why you said "result"

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Result means your after sheet.

  31. #31
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    I'm creating a new workbook to help me explaining my wishes


    thank you
    Attached Files Attached Files
    Last edited by benfontein; 04-23-2018 at 06:46 AM.

  32. #32
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Please Login or Register  to view this content.

    i thought about this to sort data ... but it doesn't work yet !

    could it help ?
    Last edited by benfontein; 04-23-2018 at 07:24 AM.

  33. #33
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Ahh, I didn't realize that you attached a new workbook.

    Let me look at it.

  34. #34
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    One question.

    Why C14 in AFTER2 = 235 where it is blank in BEFORE?

  35. #35
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    my mistake : C14 in AFTER2 = 235

    well spotted

    sorry

  36. #36
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    1) So both C13 & C14 should be blank?
    2) When one of the value is blank in the same header name in the same row, blank should be treated greater than the other, means blank comes 2nd?

  37. #37
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    1) Both should remain as in RESULT2 because prior to "cleaning task" I would like data to be sort, in this case 211 from C13 in BEFORE will switch in C14 and C14 in C13. Thus, values will be equal and will not be cleaned

    2) blank should be the condition to make to sort, if they is a blank in same header name/same row = no sort.

  38. #38
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    OK,

    This is only to check the logic, if the sort within same header is correct or not.
    Please Login or Register  to view this content.

  39. #39
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Apparently it is

    First I though doing it only for duplicates ID only to do not slow down the program too much (I have over 4000 rows), but you method seems super fast !

    and you found a solution faster han me, I'm still fighting with it to make it workss.

  40. #40
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    See if this works.
    This output in a new worksheet, but you can change it later, if it is good.
    Please Login or Register  to view this content.
    Last edited by jindon; 04-23-2018 at 09:00 AM.

  41. #41
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    thanks Jindon it seems to work

    could you add some details to your code, for my personal training.

    How do you build code with Ubound ?
    Why this code is faster than everything I could have done?

    I still need to make some test and to understand the logic to be done with this thread

    but thank you very much

  42. #42
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    It's about half an hour before mid night here, so I will give you the details sometime tomorrow.

    It is fast because everything is done within memory (Array).

  43. #43
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    sleep well

  44. #44
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Morning,

    I've been working on these scripts and I was wondering why I got an VBA: Subscript out of Range (Error 9) when I was using this code in my data sample. I just edited to have data and result in the same tab, I tried different solutions but no clues...

    Also -, may I ask you to solve one more problem because i tried but failed (also because I struggled a bit to understand your last code) but I would like to combine 2 of your scripts + edit the last one.

    Indeed, would it be possible to :
    1) still sort the value by ascending numerical order
    2) Still dont tke into account blank value for comparison
    3) Delete both value for same header name and same ID in case of 2 duplicates
    4) Delete the value which differs in case of >2 duplicates (didn't manage to solve this)

    please check attached workbook for more details.

    thank you very much
    Attached Files Attached Files

  45. #45
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Is there any chance to have more than 3 duplicate rows for the same "Metal Ring"?

  46. #46
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Yes there is

  47. #47
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    OK, that I was worried about.

    When 4 or more duuplicate, and the column has 2 or more x 2dups or 3dups, what should be the condition?

  48. #48
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    for >2 duplicates, the condition will be always the same : if we can distinguish within the column 1 value which appears more than the others = keep this once only, otherwise delete all values from this column.

    ----

    new workbook to help.
    Could you please made the script for "test" tab ?

    thanks
    Attached Files Attached Files

  49. #49
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    I think I'm still confusing...
    Please Login or Register  to view this content.

  50. #50
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Quote Originally Posted by jindon View Post
    I think I'm still confusing...

    let me check


    will be out for 1h, I checked as soon as I come back.
    Last edited by benfontein; 04-24-2018 at 03:46 AM.

  51. #51
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    question : why does it give me an error when I'm trying to execute this code within the same worksheet (without exporting the result to another worksheet) ?

  52. #52
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    NO... it is no error
    Delete the red line, it was my line to check the movement...
    Please Login or Register  to view this content.

  53. #53
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    You are almost there !


    I admit that I had an error between the BEFORE and AFTER worksheets, it was probably the source of your confusion.


    #In E3:E4 values 187 shouldn't be deleted because 187 appeared 2x and 190 once.

    #In J2:J3 value 334 x 2 , 333 x1 : should keep 334 on each row

    #In L2:M4, value 259<282 so M4 switches with L4.
    Then, 282 x 2, 259 x1 : delete 259 only.
    Then, in M2:M4 you have 294x1, 299x1, 282x1 : delete all values because there are all x1



    # in G14:G18, 234x3, 233x1, 233x1 : keep only 234 in G14, G17, G18


    thank you very much
    Attached Files Attached Files
    Last edited by benfontein; 04-24-2018 at 06:14 AM.

  54. #54
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    What I see from your result is when duplicate in a column, keep only most common value and delete other.

    Is that correct?

  55. #55
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83
    Quote Originally Posted by jindon View Post
    What I see from your result is when duplicate in a column, keep only most common value and delete other.

    Is that correct?
    Yes it is.
    If there is no most common value, delete all please

  56. #56
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    As I mentioned earlier,

    If 4 or more duplicate rows and the column has 2 dups(ex 244 & 244 vs 200 & 200), both alive?

  57. #57
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83
    Quote Originally Posted by jindon View Post
    As I mentioned earlier,

    If 4 or more duplicate rows and the column has 2 dups(ex 244 & 244 vs 200 & 200), both alive?
    No both delete

  58. #58
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    OK,

    What about 3 vb 3 (244,244,244, vs 200,200,200) or can be more combination...

  59. #59
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    rule = if x vb y and x=y, delete x and y
    Last edited by benfontein; 04-24-2018 at 08:49 AM.

  60. #60
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Try and check this
    Please Login or Register  to view this content.

  61. #61
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    when I edit the table, I found some mistakes


    here is the file to help you

    thanks
    Attached Files Attached Files

  62. #62
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    I don't think your after sheet is correct.
    Please Login or Register  to view this content.

  63. #63
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Quote Originally Posted by jindon View Post
    I don't think your after sheet is correct.
    what's troubling you ?

  64. #64
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    C7,C8 should be blank I believe.

  65. #65
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Yes it should indeed, but I paid more attention on the "new" case .. but I must admit you are wonderful.

    I will put your impressive job to the test.


    May I ask you to get some details on this code ?
    don't need to do it now, later works obviously

  66. #66
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Explanation will be done when the code works prefect, otherwise it would be useless/helpless.

  67. #67
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    As far as I checked it works like a charm, but still need to test it fully


    could you tell me if it is possible to call your "Private Sub SortColumns(ByRef a)" in a script different than "your main one". But obviously pasted into "mine"

    I'm trying different approaches but it doesn't work

  68. #68
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Quote Originally Posted by benfontein View Post


    could you tell me if it is possible to call your "Private Sub SortColumns(ByRef a)" in a script different than "your main one". But obviously pasted into "mine"

    I'm trying different approaches but it doesn't work

    found
    Application.Run

  69. #69
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    well it didn't work finally but anyway ...

    Jindon, it really seems that your code is a success !!

    A wonderful job which is, for sure, way faster than my work but I guess you do have a lot of experience.

    May I ask you how did you learn all your knowledge ? experience only ? hanging on forums to solve issues ? any courses' web site ? I would like to perform because I can clearly see that everything I have done for this code takes ages ... but it appears to work at least !

    I still give myself few days to check if there is no unspotted mistakes but I thank you anyway for everything you have done and if there is a way I can learn I will be please to (try to) follow it.

    Thank you for your patience and time.

  70. #70
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    Is it working somehow?

    Step through to see the variables in Local Window.
    Please Login or Register  to view this content.
    If you have Windows version, many of the lines can be shorter using Dictionary, SortedList or something else...

  71. #71
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Quote Originally Posted by jindon View Post
    Is it working somehow?
    somehow indeed and like a charm

  72. #72
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Delete a row with conditions for same ID

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  73. #73
    Registered User
    Join Date
    10-11-2015
    Location
    france
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    83

    Re: Delete a row with conditions for same ID

    Quote Originally Posted by benfontein View Post
    well it didn't work finally but anyway ...

    I still give myself few days to check if there is no unspotted mistakes
    I will after this, no worries

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help combining macros to delete rows on conditions and add data into cells on conditions
    By JayJayGC in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-03-2017, 10:14 AM
  2. [SOLVED] Delete Row if 3 conditions are met
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-14-2015, 11:49 PM
  3. [SOLVED] Trying to delete some rows if conditions met or do nothing if not met
    By rreifs68 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-21-2015, 10:16 PM
  4. [SOLVED] Delete Row under certain conditions
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-28-2012, 09:07 PM
  5. Delete empty row on conditions
    By tek9step in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2009, 03:28 AM
  6. Delete rows with conditions
    By mridzuani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-13-2008, 02:52 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1