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

[arkaran]

My sheet 1 (Raw Data) looks like this

[TABLE="width: 289"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Experience[/TD]
[TD]Height[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD]Best[/TD]
[TD]Tallest[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]Worst[/TD]
[TD]Tallest[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]Worst[/TD]
[TD]Tall[/TD]
[/TR]
[TR]
[TD]P4[/TD]
[TD]Good [/TD]
[TD]Short[/TD]
[/TR]
[TR]
[TD]P5[/TD]
[TD]Bad[/TD]
[TD]Tall[/TD]
[/TR]
[TR]
[TD]P6[/TD]
[TD]Worse[/TD]
[TD]Short[/TD]
[/TR]
[TR]
[TD]P7[/TD]
[TD]Best[/TD]
[TD]Shorter[/TD]
[/TR]
[TR]
[TD]P8[/TD]
[TD]Bad[/TD]
[TD]Shortest[/TD]
[/TR]
[TR]
[TD]P9[/TD]
[TD]Worse[/TD]
[TD]Shorter[/TD]
[/TR]
[TR]
[TD]P10[/TD]
[TD]Worst[/TD]
[TD]Shortest
[/TD]
[/TR]
</tbody>[/TABLE]


My sheet 2 (Rating Scheme) looks like this

[TABLE="width: 466"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Rating Score[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Experience[/TD]
[TD]Best[/TD]
[TD]Good [/TD]
[TD]Bad[/TD]
[TD]Worse[/TD]
[TD]Worst[/TD]
[/TR]
[TR]
[TD]Height[/TD]
[TD]Tallest[/TD]
[TD]Tall[/TD]
[TD]Short[/TD]
[TD]Shorter[/TD]
[TD]Shortest[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 285"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Experience[/TD]
[TD]Height[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.