I had been looking around for examples but could not find one.
I need to match data between 2 workbooks / sheet on a cell (column) and only needs to return a value of either "OK" or "UNMATCH".
In WB 1, will be me database, I named it Inv Master List and the workbook that I need to do the checking will be call as Checking.
In Column G of my CHECKING, I will need to look for the DN on column A to match with the DN in Inv Master List, we will then match the data on row 2 with the data of the same DN on the CHECKING workbook. Only when all fields are the same, then it will return a value "OK", and so long as 1 field is not the same, it will return a value of "Unmatch". The reason for doing so is these 2 reports are from 2 different parties and we need to check if data from both party tally.
Inv Master List Sample:
<tbody>
</tbody><colgroup><col><col><col><col><col span="4"></colgroup>
The CHECKING workbook sample:
<tbody>
</tbody><colgroup><col><col><col><col span="4"><col span="8"></colgroup>
I need to match data between 2 workbooks / sheet on a cell (column) and only needs to return a value of either "OK" or "UNMATCH".
In WB 1, will be me database, I named it Inv Master List and the workbook that I need to do the checking will be call as Checking.
In Column G of my CHECKING, I will need to look for the DN on column A to match with the DN in Inv Master List, we will then match the data on row 2 with the data of the same DN on the CHECKING workbook. Only when all fields are the same, then it will return a value "OK", and so long as 1 field is not the same, it will return a value of "Unmatch". The reason for doing so is these 2 reports are from 2 different parties and we need to check if data from both party tally.
Inv Master List Sample:
| DO | DN | DNDate | JobNo | FL | DN | TF | WH |
| XLYGDD99901 | DN13-01-00001 | 2/1/2013 | M12-08273-NSA | 240 | 15 | 5 | 3 |
| YRTFH887877 | DN13-01-00002 | 2/1/2013 | M12-08273-NSA | 60 | 14 | 5 | 1 |
| UGJFHW98715 | DN13-01-00003 | 2/1/2013 | M12-08248-DLC | 40 | 15 | 0 | 1 |
| UPGKFN54212 | DN13-01-00004 | 2/1/2013 | M12-08249-HSK | ||||
| DUFHBG98412 | DN13-01-00005 | 2/1/2013 | M12-08249-HSK | ||||
| DUFGH126542 | DN13-01-00006 | 2/1/2013 | M12-08288-YOK |
<tbody>
</tbody><colgroup><col><col><col><col><col span="4"></colgroup>
The CHECKING workbook sample:
| Completed | DN Date | FL | DN | TF | WH | Checking | ||||||||
| DN13-01-00001 | 2/1/2013 | 230 | 15 | 5 | 3 | Unmatch | How put a formula on G2 to check if: | |||||||
| DN13-01-00002 | 2/1/2013 | 60 | 10 | 5 | 1 | Unmatch | A1 here matches with Inv Master List Column B | |||||||
| DN13-01-00003 | 2/1/2013 | 40 | 15 | 0 | 1 | OK | and C1 equals to Inv Master List Column E, D equals to Inv Master List Column F, | |||||||
| 330 | How to insert auto sum and with total lines and bold | |||||||||||||
| In another words, when the DN matches, we also need to match the values for FL, DN, TF and WH in the same row as the DN. | ||||||||||||||
| Only if all matches then, show "OK", so long as 1 field don't match, will show as Unmatch | ||||||||||||||
| Question 2: How to auto sum if I have the following: | ||||||||||||||
| Completed | DN Date | FL | DN | TF | WH | |||||||||
| DN13-01-00005 | 2/1/2013 | 200 | 15 | 5 | 3 | |||||||||
| DN13-01-00006 | 2/1/2013 | 60 | 10 | 5 | 1 | |||||||||
| DN13-01-00008 | 2/1/2013 | 40 | 15 | 0 | 1 | |||||||||
| 300 | How to insert auto sum with this type of lines and bold if this data are in the same worksheet with blank rows? | |||||||||||||
| Completed | DN Date | FL | DN | TF | WH | |||||||||
| DN13-01-00099 | 2/1/2013 | 230 | 5 | 3 | 1 | |||||||||
| DN13-01-00101 | 2/1/2013 | 50 | 10 | 2 | 1 | |||||||||
| DN13-01-00140 | 2/1/2013 | 20 | 15 | 0 | 1 | |||||||||
| 300 |
<tbody>
</tbody><colgroup><col><col><col><col span="4"><col span="8"></colgroup>
