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.

Improve this question
5
  • @TimWilliams suggested a beautiful method sometime ago. Please do a search in SO. If I find that link, then I will post it here. Commented Jan 31, 2014 at 13:53
  • 1
    Found it :) See THIS Commented Jan 31, 2014 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? Commented Jan 31, 2014 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 Commented Jan 31, 2014 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. :) Commented Jan 31, 2014 at 14:35

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

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
hmm.. i just editted, let me check to see if it works for columns... it should.
Damn.. it does not.. kay hang on.
How much do you know of VBA? if I give you code, are you able to adapt it to your particular situation?
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.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.