Excel to match the values within two worksheets

Question

i need a macro/formula to compare the two cell range in a worksheet and return the corresponding value in the cell.

eg sheet: in worksheet 1(sheet1)

AGE Description proof
1   8   
2   9   
3   2   
4   9   
5   5   
6   6   
7   0   
8   1   
9   2   
10  1

sheet2:

dates   1st 2nd 3rd 4th 5th 6th

0         1  1  2   2   3   3
1         2  3  5   6   7   8   
3         4  6  7   8   9   7 
5         6  7  8   9   10  11
7         8  9  10  11  12  13
10       11 12  13  15  16  18
12       13 15  16  18  19  21
15       16 30  31  45  46  60
30       31 33  34  37  38  40

from the above example if my range needs my expectation (eg in sheet1 Age: 1 and description: 8 then it should compare it with sheet2 and paste the value as 6th in sheet1 proof column). like these i need to fill all the fields

Your data is impossible to read. Can you explain what we are looking at? What are the column headings? In the first set, is the first column (2, 3, 4...) the row number or is it a value? Is the 2nd column (9. 2. 9...) Description? I have no idea about the 2nd set of numbers as they certainly exceed what seems to be 6 column heads? — Wayne G. Dunn
hi wayne G. Dunn, i am looking for a macro/formula that needs to match the data from sheet 1 looks like this AGE Description 1 3 2 9 3 2 4 9 5 5 6 6 7 0 8 1 9 2 10 11 then i need to compare the values if 1 and 3 exists in sheet2 then it should return the value present in sheet2 dates 1st 2nd 3rd 4th 0 1 1 2 2 1 2 2 3 3 2 3 5 6 7 3 4 5 6 7 5 6 7 8 9 7 8 9 10 11 here it should return the value as 3rd — vicky
I fixed your formatting so we can at least see the columns and rows. Now you need to give a VERY SPECIFIC example. You say if 'Age: 1 and description: 8', which I believe is "Find row with Age = 1 in ColA, then use the value found in ColB (which is 8). I have no clue what row or column you search next, or what you look for. You say 'compare it with sheet2' but you don't say if you are comparing the Age (1) in a Date column, or the Description (8). Please write the logic you are trying to implement. — Wayne G. Dunn
i need to compare the value from sheet2 in sheet2 it takes 1 from dates and then search the row for the value of 8 then it returns the value 6th — vicky
Is the following correct: Take the first value in AGE column (1) and find row in Sheet2 with a (1) in the DATE column.; then look in that row in sheet2 and see if you find a value of (8) [yes, it is in column '6th']; so in Sheet1, column PROOF, you enter '6th'???? If not, I give up. After that, NONE of your other combinations will match (no rows in Sheet2 with AGE of 2, 4, 6, 8 or 9) and no match on DESCRIPTION for the other AGES. ?????? — Wayne G. Dunn

in_user in_user · Accepted Answer · 2014-12-18T07:17:30

As far as I understand your problem, you are

first matching sheets1.age with sheets2.dates and if you found a match then
You are searching Sheets1.description in sheets2.1st to Sheets2.6th and if you found a mtach then
You are selecting selecting *th(column name) from sheet2 and putting it into sheets1.proof.

I found only 1 match i.e. for the first observation

Below is the code

Sub checkNmatch()

Calculating the lastrow of sheet1 and sheet2 for automation

lastRowSheet1 = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lastRowSheet2 = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To (lastRowSheet1 - 1)
 For j = 2 To (lastRowSheet2 - 1)

Checking below if sheets1.age matches with sheets2.dates

   If Worksheets("Sheet1").Cells(i, 1) = Worksheets("Sheet2").Cells(j, 1) Then

If match found then searching Sheets1.description in sheets2.1st to Sheets2.6th

   For k = 2 To 7
   If Worksheets("Sheet1").Cells(i, 2) = Worksheets("Sheet2").Cells(j, k) Then

If match found then copying the column name of Sheet2 into Proof of sheet1

      Worksheets("Sheet1").Cells(i, 3) = Worksheets("Sheet2").Cells(1, k)       
   End If
          Next
        End If
     Next
    Next
    End Sub

Excel to match the values within two worksheets

1 Answers