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

arkaran

New Member
Joined
Jan 4, 2018
Messages
4
My sheet 1 (Raw Data) looks like this

ExperienceHeight
P1BestTallest
P2WorstTallest
P3WorstTall
P4Good Short
P5BadTall
P6WorseShort
P7BestShorter
P8BadShortest
P9WorseShorter
P10WorstShortest

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


My sheet 2 (Rating Scheme) looks like this

Rating Score01234
ExperienceBestGood BadWorseWorst
HeightTallestTallShortShorterShortest

<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.

ExperienceHeight
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?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

j_unsuitable

Board Regular
Joined
Oct 28, 2015
Messages
235
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,784
Office Version
2013
Platform
Windows
Try this
Code:
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

New Member
Joined
Jan 4, 2018
Messages
4
Try this
Code:
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

New Member
Joined
Jan 4, 2018
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,929
Messages
5,447,361
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top