1) How to Enclose Excel Data within Quotes and append a comma after that.
Input : Rajesh
Desired Output :- 'Rajesh',
If the data is present in Column A2 then the formula is :- ="'"&A2&"',"
Useful in SQL Queries.
2) How to
a) check if a data from Table B is existing in Table A
b) highlight the rows in Table B containing the data.
Table A:
Input : Rajesh
Desired Output :- 'Rajesh',
If the data is present in Column A2 then the formula is :- ="'"&A2&"',"
Useful in SQL Queries.
2) How to
a) check if a data from Table B is existing in Table A
b) highlight the rows in Table B containing the data.
Table A:
| Order Number (A) | Location(B) | Code(C) | Found Status (D) |
|---|---|---|---|
| 0081-8589551-0003 | 0003 | X | 0 |
| 0081-8589551-0004 | 0004 | Y | 0 |
| 0081-8589551-0005 | 0005 | Z | 0 |
Table B:
| Found in Tables(F) | Location(G) |
|---|---|
| 0081-8589551-0003 | IFACE Table |
| 0081-8589551-0004 | IFACE Table |
Base Formula is : =IFERROR(VLOOKUP(A2,$F$2:$G$66,1,FALSE),0)
A2 - is the column to be searched.
$F2:$G$66 - is the area of the data set where our search key exist which is within Table B.
1 - 1 is the first column of Data set.
FALSE - When Value is False that means we are searching exact match.
IFERROR() - is used because Lookup function at times returns #N/A value. This function will convert that to a value 0
Base formula for conditional Formatting is :
Formula :- =IFERROR(VLOOKUP($A2,$F$2:$G$66,1,FALSE),0) <> 0
$A2 means dynamic referencing.
$F$2:$G$66 is example of Fixed Referencing.
Area where formatting rule is Applied to:-
=$A$2:$D$1869 (Data Set of Table A)
