CODEWITHSUNDEEP
vbaexcelexcel-formula
EKet
EKet

Reputation: 7314

Excel row comparison to columns functions/vba

Problem: I have data that looks like this and I want to compare the rows to the column values. so is(Table1.t1 = Table2.t1)? kind of thing. 

Table 1
A B
x t1
x t2
x t3
x t4

Table 2

A B  C  D  E
x t1 t2 t3 t4

Can I do this in Excel with functions or VBA

Upvotes: 2

Views: 3405

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

If in A1:B8, you have

x   t1
x   t2
x   t3
x   t4
y   u1
y   u2
y   u3
y   u4

and in A11:E12, you have

x   t1  t2  t3  t4
y   u1  u2  u3  u4

then put this formula in C1 and fill down

=B1=OFFSET(INDEX($A$11:$A$12,MATCH(A1,$A$11:$A$12,FALSE),1),0,COUNTIF($A$1:A1,A1),1,1)

It will return TRUE if the data matches, and FALSE if it doesn't.

Upvotes: 0

BradC
BradC

Reputation: 39946

If I'm understanding your question, there are a few ideas that might lead you in the right direction:

  • If you just want to "flip" the data in one of the two tables so you can compare the tables directly, just do a Copy, Paste Special, and check the "Transpose" button. If you want to do this with VBA, just record it as a macro and see what it records.
  • If you want to use VBA, I think that using Cells(row#,col#) instead of Range() is the key.

So lets assume your two tables are in B10:B20 (vertical) and C2:L2 (horizontal). The loop to compare them might look something like:

Sub CompareFlippedTables
    Dim i as integer
    For i = 1 to 10
        If Cells(i + 10,2).Value = Cells(2, i + 3).Value Then
           'Do something useful'
        End If
    Next i
End Sub

Upvotes: 1

Related Questions