Hi all,
This one has me puzzled like crazy
I just don't seem to get it right without writing a large book of code for all exceptions.
So here's the case:
I have a workbook with two sheets.
Sheet1 contains a large table (20.000 rows, 100 columns), but looks simplified like this:
The second sheet contains a reference table that looks (simplified) like this:
So from here i want some Excel-magic to happen.
This is to:
- Find exact values of column A, B and C from sheet1 in column A, B and C from sheet2
- Based on these values to return values for column E and F in Sheet1 from the Reference table in sheet 2
So the endresult in sheet 1 after running the code should be:
I have been able to make this work with workaround code that does not involve the reference table and looks like this:
Problem is that this way I have to include code for all possible combinations of columns A B and C.
Which is not an efficient way of working, requires a very large code + increases the chance of errors.
Especially given that the reference table will change frequently.
So hopefully any of you knows a way to integrate a comparison of the first three columns in both sheets to return these values from the reference table.
Any help is greatly appreciated. (and needed
)
This one has me puzzled like crazy
I just don't seem to get it right without writing a large book of code for all exceptions.
So here's the case:
I have a workbook with two sheets.
Sheet1 contains a large table (20.000 rows, 100 columns), but looks simplified like this:
![scr3sht1.jpg](/board/proxy.php?image=https%3A%2F%2Fs14.postimg.org%2Fb0cjdd0xt%2Fscr3sht1.jpg&hash=37120c545d011bfd9acd54b39c077734)
The second sheet contains a reference table that looks (simplified) like this:
![scr3sht2.jpg](/board/proxy.php?image=https%3A%2F%2Fs17.postimg.org%2Ffmu78g8v3%2Fscr3sht2.jpg&hash=221e6939c773db64bfe443732ecd1feb)
So from here i want some Excel-magic to happen.
This is to:
- Find exact values of column A, B and C from sheet1 in column A, B and C from sheet2
- Based on these values to return values for column E and F in Sheet1 from the Reference table in sheet 2
So the endresult in sheet 1 after running the code should be:
![finscren.jpg](/board/proxy.php?image=https%3A%2F%2Fs14.postimg.org%2Fky7jl1f0x%2Ffinscren.jpg&hash=e0f74380694923436da645c9dac70451)
I have been able to make this work with workaround code that does not involve the reference table and looks like this:
Code:
Dim i As Long
For i = 1 To 20000
With Sheets("sheet1")
If .Range("A" & i).Text = "Car" And _
.Range("B" & i).Text = "Green" And_
.Range("C" & i).Text = "5" _
Then _
.Range("E" & i).Value = "Group 1"
End With
Next i
Problem is that this way I have to include code for all possible combinations of columns A B and C.
Which is not an efficient way of working, requires a very large code + increases the chance of errors.
Especially given that the reference table will change frequently.
So hopefully any of you knows a way to integrate a comparison of the first three columns in both sheets to return these values from the reference table.
Any help is greatly appreciated. (and needed
![Smile :) :)](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)
Last edited: