0

Can anyone point me at a function or code that can compare a range of cells to a range of cells in another sheet and return a value. The lists have the same format:

Example:

Compare Sheet1 A1 B1 C1 D1 to Sheet 2 A1 B1 C1 D1 and return Sheet 2 E1 value

In my head it's like a multiple column VLOOKUP.

WGS
  • 13,969
  • 4
  • 48
  • 51
user3257536
  • 13
  • 2
  • @TimWilliams suggested a beautiful method sometime ago. Please do a search in SO. If I find that link, then I will post it here. – Siddharth Rout Jan 31 '14 at 13:53
  • 1
    Found it :) See [THIS](http://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet/19396257#19396257) – Siddharth Rout Jan 31 '14 at 13:55
  • 1
    do you mean return the answer to the comparison (equal, not equal) in sheet2 E1? or just return whatever is in sheet2 E1 regardless? what are you hoping to achieve with the comparison? are you looking to see if they match exactly? are you looking to find which columns dont match, or which ones gave the greater value? – user1759942 Jan 31 '14 at 13:56
  • on sheet 2 - E1 has a job role name. If the four fields from sheet 1 match the very same values in sheet 2 then Sheet 2 E 1 is returned to Sheet 1 - this sounds like matching binary in my head – user3257536 Jan 31 '14 at 13:59
  • OP, would you kindly edit your question and include your data there? It is not the practice here to put your data in the comments section. Kindly put ***everything*** that's the issue with your question in your post above. This way, we don't refer to comments while helping you out answer your problem. :) – WGS Jan 31 '14 at 14:35

1 Answers1

1

If you can move the job code to E2 then this, adapted from the link siddharth posted will work:

 Dim a As Application
 Set a = Application

    If Join(a.Transpose(a.Transpose(Sheets(1).Rows(1).Value)), chr(0)) = _
   Join(a.Transpose(a.Transpose(Sheets(2).Rows(1).Value)), chr(0)) Then


MsgBox (Sheets(2).Cells(2, "E"))
End If

Change the rows as necessary, and instead of msgbox, save it as a variable or however you want.

Edit

the following will work also:

Dim a As Application
Set a = Application
Dim rngA As Range
Dim rngB As Range

Set rngA = Range("A1", "D1")
Set rngB = Range("A2", "D2")


If Join(a.Transpose(a.Transpose(rngA.Value)), chr(0)) = _
   Join(a.Transpose(a.Transpose(rngB.Value)), chr(0)) Then


    MsgBox (Sheets(2).Cells(2, "E"))
End If

provided that the ranges to compare are only 1 row each. if either range spans more than one row it wont work.

Edit2

Sub getValuesOnRow()
  Dim sourceRange As Range
  Dim targetRange As Range

  Set sourceRange = ActiveSheet.Range(Cells(1, 1), Cells(5, 1))
  Set targetRange = ActiveSheet.Cells(7, 1)

  sourceRange.Copy
  targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,     Transpose:=True

End Sub

Sub Compare()
Dim a As Application
Set a = Application
Dim rngA As Range
Dim rngB As Range


Set rngA = Sheets(1).Range("A1", "A6")
Set rngB = Sheets(2).Range("A1", "A6")


If Join(a.Transpose(a.Transpose(Sheets(1).Rows(7).Value)), chr(0)) = _
   Join(a.Transpose(a.Transpose(Sheets(2).Rows(7).Value)), chr(0)) Then


    Sheets(1).Cells(6, "A").Value = Sheets(2).Cells(6, "A")
End If



End Sub

So that there is 2 methods. the first, takes a particular column, in the example, column 1, and the first 5 cells, and puts it on 1 row. in the example, row 7. Then the transpose method, takes the row and compares it. so the first method can be used to get a particular column on a row, and the second can be used to compare 2 rows and return a particular cell if the comparison is true.

user1759942
  • 1,322
  • 4
  • 14
  • 33
  • thanks but that actually wont work for me as I have multiple rows that have different job IDs - so I cant move to E2 I have about 30 jobs on sheet 2 that are identified by '1' or Null in 5 cells - so i need to compare Sheet1:A1:A5 to Sheet2:A1:A5 and if all cells matche it then returns the value Sheet2:A6 to Sheet1:A6 - if that doesnt match then compare Sheet1:A1:A5 to Sheet2:B1:B5 and so on. hope this has described it better....cheers all – user3257536 Jan 31 '14 at 14:17
  • hmm.. i just editted, let me check to see if it works for columns... it should. – user1759942 Jan 31 '14 at 14:19
  • Damn.. it does not.. kay hang on. – user1759942 Jan 31 '14 at 14:21
  • How much do you know of VBA? if I give you code, are you able to adapt it to your particular situation? – user1759942 Jan 31 '14 at 14:27
  • Ok, so the 2 methods will do exactly what is needed in conjunction with eachother. You'll need to either loop it or manually edit and run them for each column, your pick. – user1759942 Jan 31 '14 at 14:38
  • Awesome the first version works great !!!!!! I just need to add the loop :) though will try this myself and learn THANKS ALL – user3257536 Jan 31 '14 at 14:46
  • @user3257536: Kindly mark this answer as accepted if you found it helpful. This is how we say thanks in SO. :) – WGS Jan 31 '14 at 14:49
  • Thanks BK but I doubt they saw that or will be back unless they runs into more problems. least they did say thank you though :) – user1759942 Jan 31 '14 at 14:54
  • found it! was toooooo obvious! – user3257536 Jan 31 '14 at 15:43