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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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
12,979
Office Version
  1. 2013
Platform
  1. 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,129,467
Messages
5,636,456
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top