If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
matching Data
Hi! Experts
I have 2 sheets in a workbook.Sheet1 is a master sheet with Customer code and there Sale.And Sheet2 is the sheet which has only Customer code. Note: Its just a Example i have total 1924 data. Now in Sheet 1 there are some Customer code which is Repeated some times 2 and 3 or even 4 times. In Sheet 2 Same as sheet 1 there are some data which is also Repeated. Now i how can i know which Customer Code in sheet 2 Missing from Sheet1 I have Colored Some Data in sheet 2 which is not match with Sheet1 For Example. In Sheet 1 221000000232 is repeated 2 times but in Sheet2 has only 1 times. http://www.savefile.com/files/2079929 Attached is the Example of my Question Could anybody Tell me how can i do that. Thanks in Advance Hardeep kanwar |
#2
|
|||
|
|||
matching Data
I havent seen the excel file. But the below formula should help..The below
code compares the number of instances of customercode in Sheet1 colA and Sheet2 colA...and incase of mismatch 'MISMATCH' is marked Sheet1 cell B1 = =IF(COUNTIF(A:A,A1)=COUNTIF(Sheet2!A:A,A1),"","MIS MATCH") If this post helps click Yes --------------- Jacob Skaria "Hardeep kanwar" wrote: Hi! Experts I have 2 sheets in a workbook.Sheet1 is a master sheet with Customer code and there Sale.And Sheet2 is the sheet which has only Customer code. Note: Its just a Example i have total 1924 data. Now in Sheet 1 there are some Customer code which is Repeated some times 2 and 3 or even 4 times. In Sheet 2 Same as sheet 1 there are some data which is also Repeated. Now i how can i know which Customer Code in sheet 2 Missing from Sheet1 I have Colored Some Data in sheet 2 which is not match with Sheet1 For Example. In Sheet 1 221000000232 is repeated 2 times but in Sheet2 has only 1 times. http://www.savefile.com/files/2079929 Attached is the Example of my Question Could anybody Tell me how can i do that. Thanks in Advance Hardeep kanwar |
#3
|
|||
|
|||
matching Data
Gr8 Jacob Skaria
Works like a Treat Thanks a Lot for saving my time Thanks Again "Jacob Skaria" wrote: I havent seen the excel file. But the below formula should help..The below code compares the number of instances of customercode in Sheet1 colA and Sheet2 colA...and incase of mismatch 'MISMATCH' is marked Sheet1 cell B1 = =IF(COUNTIF(A:A,A1)=COUNTIF(Sheet2!A:A,A1),"","MIS MATCH") If this post helps click Yes --------------- Jacob Skaria "Hardeep kanwar" wrote: Hi! Experts I have 2 sheets in a workbook.Sheet1 is a master sheet with Customer code and there Sale.And Sheet2 is the sheet which has only Customer code. Note: Its just a Example i have total 1924 data. Now in Sheet 1 there are some Customer code which is Repeated some times 2 and 3 or even 4 times. In Sheet 2 Same as sheet 1 there are some data which is also Repeated. Now i how can i know which Customer Code in sheet 2 Missing from Sheet1 I have Colored Some Data in sheet 2 which is not match with Sheet1 For Example. In Sheet 1 221000000232 is repeated 2 times but in Sheet2 has only 1 times. http://www.savefile.com/files/2079929 Attached is the Example of my Question Could anybody Tell me how can i do that. Thanks in Advance Hardeep kanwar |
Thread Tools | |
Display Modes | |
|
|