I have two sheets. Result needed as in sheet1 Required Results
column as depicted below. The results are populated by checking the values in sheet2.
Noun Modifier Required Results Name1 Value1 Name2 Value2 Name3 Value3 Name4 Value4 Name4 Value4
ABRASIVE BELT ABRASIVE BELT : 5in X 2in TYPE WAFER WIDTH LENGTH 5in THICKNESS 2in DIAMETER 2m
ABRASIVE BELT ABRASIVE BELT : 11in X 6in X 3m TYPE LUGGED WIDTH 11in LENGTH 6in THICKNESS 3in DIAMETER 3m
ABRASIVE BELT ABRASIVE BELT : 12in X 7in X 3m TYPE LUGGED WIDTH 12in LENGTH 7in THICKNESS 3in DIAMETER 4m
Sheet2
Noun Modifier Attribute Name fill
ABRASIVE BELT TYPE 0
ABRASIVE BELT WIDTH 1
ABRASIVE BELT LENGTH 2
ABRASIVE BELT THICKNESS 3
ABRASIVE BELT DIAMETER 0
ABRASIVE ROD TYPE 0
ABRASIVE ROD LENGTH 1
I will explain the update process by taking first row as the example.
- ABRASIVE word from sheet1 is searched in sheet2 Noun column.
- If Matches corresponding next value BELT from sheet1 is searched in sheet2 Modifier column.
- If both the values matches column
Name1
value TYPE should be searched in sheet2Attribute Name
column. Iffill
column is 0 for the corresponding row, then no need to populate the value in sheet1. Here in sheet 2 WIDTH, LENGTH, THICKNESS the values are 1 infill
column. Therefore I have concatenated WIDTH, LENGTH, THICHNESS values as 5in X 2 in. IN first row Width is blank.
So referring to Sheet two, I need to populate the dimensions. I have around 10K records in sheet1 and 20K reference data in Sheet2. I tried applying several vlookup formulas, but I could not achieve it. Please help.
No comments:
Post a Comment