Cross reference data from 2 different sheets to fill 3rd sheet

[arkaran]

My sheet 1 (Raw Data) looks like this

	Experience	Height
P1	Best	Tallest
P2	Worst	Tallest
P3	Worst	Tall
P4	Good	Short
P5	Bad	Tall
P6	Worse	Short
P7	Best	Shorter
P8	Bad	Shortest
P9	Worse	Shorter
P10	Worst	Shortest

<colgroup><col><col><col></colgroup><tbody>
</tbody>

My sheet 2 (Rating Scheme) looks like this

Rating Score	0	1	2	3	4
Experience	Best	Good	Bad	Worse	Worst
Height	Tallest	Tall	Short	Shorter	Shortest

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

I want my Sheet 3 (Rating scores) to use the data from the previous two sheets to fill numbers in the following blanks.

	Experience	Height
P1
P2
P3
P4
P5
P6
P7
P8
P9
P10

<colgroup><col><col><col></colgroup><tbody>
</tbody>

This is obviously a simplified example. I need to use this formula on a huge data set across multiple variables . What formula should I use?

 

[j_unsuitable]

Hey,
can you transposition the data in sheet 2? Make "Height" "Experience" and "Rating Score" column headers and then have the data in the rows? Then you could use a simple vlookup

 

[JLGWhiz]

Try this

Sub t()
Dim sh1 As Worksheet, sh3 As Worksheet, c As Range, fn As Range, i As Long, txt As String, r As Long
Set sh1 = Sheets(1)
Set sh3 = Sheets(3)
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        For i = 2 To 3
            txt = sh1.Cells(c.Row, i).Value
            Select Case txt
                Case "Best", "Tallest"
                    r = 0
                Case "Good", "Tall"
                    r = 1
                Case "Bad", "Short"
                    r = 2
                Case "Worse", "Shorter"
                    r = 3
                Case "Worst", "Shortest"
                    r = 5
            End Select
            Set fn = sh3.Range("A:A").Find(c.Value, , xlValues)
            If Not fn Is Nothing Then
                If i = 2 Then
                    fn.Offset(, 1) = r
                ElseIf i = 3 Then
                    fn.Offset(, 2) = r
                End If
            End If
        Next
    Next
End Sub

 

[arkaran]

Try this

Sub t()
Dim sh1 As Worksheet, sh3 As Worksheet, c As Range, fn As Range, i As Long, txt As String, r As Long
Set sh1 = Sheets(1)
Set sh3 = Sheets(3)
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        For i = 2 To 3
            txt = sh1.Cells(c.Row, i).Value
            Select Case txt
                Case "Best", "Tallest"
                    r = 0
                Case "Good", "Tall"
                    r = 1
                Case "Bad", "Short"
                    r = 2
                Case "Worse", "Shorter"
                    r = 3
                Case "Worst", "Shortest"
                    r = 5
            End Select
            Set fn = sh3.Range("A:A").Find(c.Value, , xlValues)
            If Not fn Is Nothing Then
                If i = 2 Then
                    fn.Offset(, 1) = r
                ElseIf i = 3 Then
                    fn.Offset(, 2) = r
                End If
            End If
        Next
    Next
End Sub

This doesn't use the data from sheet 2. I can't manually write a code for the actual data, as the sheet 2 in the actual data is huge.

 

[arkaran]

Hey,
can you transposition the data in sheet 2? Make "Height" "Experience" and "Rating Score" column headers and then have the data in the rows? Then you could use a simple vlookup

I tried that, but it's a case of nested vlookup and it's not working.