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:
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:
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 )
Last edited: