Results 1 to 5 of 5

Thread: Remove first character then compare to another cell

  1. #1
    Junior Member
    Join Date
    Jul 2015
    Posts
    1
    Rep Power
    0

    Remove first character then compare to another cell

    Hi all, new to this forum and forums in general, so I hope I posted my question correctly and that I am clear enough.

    I have two columns where column "E" has just numbers that are 6-10 digits in length and the second column "H" starts with an alphanumeric character (lets say the letter B) then 6-10 digits after it.

    The columns go from E1: E273 and H1:H273. (not always but long enough )

    I need to remove the first character in column H then compare the results to column E. If there is a different, I need to know and that can be up to your discretion, as long as I can see the results.. I could be notified by color, by putting a 0 or 1 in a different column, or by dropping the number in a any different column.

    What I have tried is creating a blank column G and putting in it the formula
    =REPLACE(H1,1,1,"")
    which removed the first character in column H and showed up in column G

    Then I put in blank column F the formula
    =IF(ISNA(MATCH(G1,E:E,0)),G1,1)
    which only copied the results from column G to column F as if to say they were different. If they were the same column F would have the results 1 and not the number.

    Not sure what I did wrong. And if it can be done through a VBA macro, that would be fantastic. I will be using it a lot.

    EX.

    Colum E

    14624720
    7802049
    24842259
    28675186
    32334880
    30466791
    2234169
    24717605
    45069143
    7825783
    16875105
    13656190
    9447593



    Column H

    B14624720
    B7802049
    B24842259
    B28675186
    B32334880
    B30466791
    J2234169
    J24717605
    J45069143
    B7825783
    B16875105
    B13656190
    B9447593

    Thank you for your time and help,

    Dave
    Last edited by dleckie; 07-11-2015 at 01:25 AM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Last edited by DocAElstein; 03-01-2024 at 03:00 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Last edited by DocAElstein; 03-01-2024 at 03:01 PM.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Here is another macro you can consider...
    Code:
    Sub Test()
      Dim LastRow As Long
      LastRow = Cells(Rows.Count, "E").End(xlUp).Row
      Range("F1:F" & LastRow) = Evaluate(Replace("IF(E1:E#=0+MID(H1:H#,2,99),1,E1:E#)", "#", LastRow))
    End Sub

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Or:
    Code:
    Sub M_snb()
       [I2:I200] = [if(E2:E200="","",if(E2:E200=abs(mid(H2:H200,2,100)),"x",""))]
    End Sub

Similar Threads

  1. compare two tables
    By emmye998 in forum Excel Help
    Replies: 1
    Last Post: 03-24-2014, 04:25 AM
  2. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  3. Macro To Delete Numbers With Trailing Character
    By Howardc in forum Excel Help
    Replies: 8
    Last Post: 04-05-2013, 08:14 PM
  4. compare column a to b and b to a
    By ayazgreat in forum Excel Help
    Replies: 18
    Last Post: 05-07-2012, 04:46 PM
  5. Count words having more than one character in a cell
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 03-13-2012, 08:29 PM

Posting Permissions

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