Scanning for shifted values in different columns

[luinox86]

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.

[tigeravatar]

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

[ConneXionLost]

A similar approach would be:

=ROWS($B$2:B2)-MATCH(B2,$A$2:$A$7,0)

if your first row of data was in row 2.

Cheers,

[luinox86]

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.

[Mordred]

If interested in VBA:

Sub PositionLetters()
Dim colB As Variant, colC As Variant
Dim colBCnt As Long, colCCnt As Long
Dim colBArray(1 To 26) As String
Dim colCArray(1 To 26) As String
Dim x As Long, y As Long, z As Long
Dim fndCnt As Long
x = 1
For Each colB In Sheets(1).Range("B2:B27")
    colBArray(x) = colB.Value
    x = x + 1
Next colB
y = 1
For Each colC In Sheets(1).Range("C2:C27")
    colCArray(y) = colC.Value
    y = y + 1
Next colC
For x = 1 To 26
       y = 1
        If colCArray(x) <> colBArray(x) Then
            Do Until colCArray(y) = colBArray(x)
                y = y + 1
            Loop
            If y > x Then z = x - y
            If y < x Then z = x - y
            Set fndrng = Sheets(1).Range("C2:C27").Cells.Find(colCArray(x)).Offset(0, 1)
            fndrng.Value = z
        End If
Next x
End Sub

[luinox86]

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.

[Mordred]

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!

[snb]

I'm also interested in VBA:

Sub snb()
  [c1:c100] = [IF(b1:b100="","",row(B1:B100)-MATCH(b1:b100,A$1:A$100,0))]
End Sub

[Mordred]

Hot diggity snb,

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

Very cool!