Is it possible to have a VBA that can create match and cross match function? Thank you.
All matches are case insensitive.
There may be blank rows in sheet2 data, but they can be removed if absolutely necessary.
No gaps in the sheet3 data.
Look at column R cells below TITLE1 cell and above TITLE2 cell in "sheet2".
Ignore cells where the part of the string contains "shows", "show" or ":".
Ignore cells where the same row column Q cell value is NOT 5.
The cells in the column R which meet above conditions do the following.
Look in the columns B to Z in the "sheet3" where the headers in the row 1 match with one of the cells in the column P of sheet2.
See if the qualified cells in the column R of sheet2 match with any of the cells from rows 2-50 in the qualified columns in the sheet3. Partial match is fine - as long as column R string contains a text from qualified columns in sheet is considered a match.
If there is a match, then add row1 header of the matched column from sheet3 in the beginning of the column R cell in sheet2.
First match in the string is the match in case there are multiple matches.
Following is the example.
BEFORE:
AFTER:
All matches are case insensitive.
There may be blank rows in sheet2 data, but they can be removed if absolutely necessary.
No gaps in the sheet3 data.
Look at column R cells below TITLE1 cell and above TITLE2 cell in "sheet2".
Ignore cells where the part of the string contains "shows", "show" or ":".
Ignore cells where the same row column Q cell value is NOT 5.
The cells in the column R which meet above conditions do the following.
Look in the columns B to Z in the "sheet3" where the headers in the row 1 match with one of the cells in the column P of sheet2.
See if the qualified cells in the column R of sheet2 match with any of the cells from rows 2-50 in the qualified columns in the sheet3. Partial match is fine - as long as column R string contains a text from qualified columns in sheet is considered a match.
If there is a match, then add row1 header of the matched column from sheet3 in the beginning of the column R cell in sheet2.
First match in the string is the match in case there are multiple matches.
Following is the example.
| Book1 | ||||||
|---|---|---|---|---|---|---|
| B | C | D | E | |||
| 1 | English: | Marathi: | Hindi: | |||
| 2 | Star Wars | Pathare | Sholay | |||
| 3 | Superman | Don | ||||
| 4 | ||||||
Sheet3 | ||||||
BEFORE:
| Book1 | |||||
|---|---|---|---|---|---|
| P | Q | R | |||
| 1 | English: | Movies | |||
| 2 | Hindi: | Theater | |||
| 3 | English: | ||||
| 4 | TITLE1 | ||||
| 5 | 5 | Everyone does not like Star Wars. | |||
| 6 | 5 | Sholay has great reviews. | |||
| 7 | 5 | Superman and Don are great movies. | |||
| 8 | |||||
| 9 | TITLE2 | ||||
| 10 | Everyone does not like Star Wars. | ||||
| 11 | Sholay has great reviews. | ||||
| 12 | Superman and Don are great movies. | ||||
Sheet2 | |||||
AFTER:
| Book1 | |||||
|---|---|---|---|---|---|
| P | Q | R | |||
| 1 | English: | Movies | |||
| 2 | Hindi: | Theater | |||
| 3 | English: | ||||
| 4 | TITLE1 | ||||
| 5 | 5 | English: Everyone does not like Star Wars. | |||
| 6 | 5 | Hindi: Sholay has great reviews. | |||
| 7 | 5 | English: Superman and Don are great movies. | |||
| 8 | |||||
| 9 | TITLE2 | ||||
| 10 | Everyone does not like Star Wars. | ||||
| 11 | Sholay has great reviews. | ||||
| 12 | Superman and Don are great movies. | ||||
Sheet2 | |||||
