+ Reply to Thread
Results 1 to 9 of 9

Scanning for shifted values in different columns

  1. #1
    Registered User
    Join Date
    10-03-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Scanning for shifted values in different columns

    Hi everyone,

    I would really appreciate your help guys, I am a programmer, but not familiar with Excel's VBA.

    Let's say I have two columns as below:

    A A
    B B
    C C
    D E
    E D
    F F

    As you can see, the content of the two columns are same, but not the arrangement, I want a third column to scan each value of the second column, and detect if it was placed at the same place it was in column one, if not, how many *rows* it has shifted.

    For example the third column would look like this:

    A A 0
    B B 0
    C C 0
    D E -1
    E D 1
    F F 0


    I was trying to write a macro for it, but as I said I am not familiar with VBA and how to communicate and access the sheet's content.

    Your help is much appreciate everyone. What is the best way to do that? A formula, macro anything that gets the job done is great!

    Thank you.
    Last edited by luinox86; 06-14-2011 at 02:27 AM.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Scanning for shifted values in different columns

    luinox86,

    Attached is a sample workbook based on the data you provided. In column C is the following formula:
    =MATCH(A1,$B:$B,0)-ROW(A1)

    Then I copied down.

    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Scanning for shifted values in different columns

    A similar approach would be:

    Please Login or Register  to view this content.
    if your first row of data was in row 2.

    Cheers,

  4. #4
    Registered User
    Join Date
    10-03-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Scanning for shifted values in different columns

    Thank you so much tigeravatar and ConneXionLost.

    Both answers work great, I was going to write a big VB-like function for it!

    I will try to implement it the way I want it into my work-book, will get back to you if there are anymore questions.

    Thanks a lot, have a nice day.

  5. #5
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Scanning for shifted values in different columns

    If interested in VBA:
    Please Login or Register  to view this content.
    Last edited by Mordred; 06-14-2011 at 02:23 AM.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  6. #6
    Registered User
    Join Date
    10-03-2010
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Scanning for shifted values in different columns

    Thank you Mordred,

    Of course I am interested in the VBA. I really should learn writing VBAs for some special cases.

    I will go through your code and test it, will get back to you if I didn't understand.


    Regards.

  7. #7
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Scanning for shifted values in different columns

    Just because I am over tired but can't resist playing, I've added a button for you to run your tests. Let me know if it fails.

    Cheers!
    Attached Files Attached Files

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Scanning for shifted values in different columns

    I'm also interested in VBA:

    Please Login or Register  to view this content.



  9. #9
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Scanning for shifted values in different columns

    Hot diggity snb,

    Mordred's line count = 35
    snb line count = 3

    Very cool!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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