match columns and rows from one sheet to another sheet

[saravanan1981]

I have 2 sheets "Main" and "Received"
Both the Sheets has contains details of family as per the heading
Family id will be unique for one particular family.

I want to compare the details of family (Column wise and row wise in respective) in sheet "Main" with sheet "Received"
If particular family details are matching in both the sheets, then in Sheet "Main" in Column "J" (Status) I wants result as all the details area matched.

For Example
Family code "053221" in main sheet is Matching with Received sheet. All details area matching Like family id, Members name, age, DOB etc. Hence in status column Marked as "ALL DETAILS MATCHED"

Family code "023372" in main sheet is not Matching with Received sheet. The Difference is in name of the second family member. Hence in status column Marked as "NOT MATCHED"

Family code "019340" in main sheet is not Matching with Received sheet. The Difference is in total number of family member. Hence in status column Marked as "NOT MATCHED"

Matching Criteria to Mark status
a) first check family id

b) based on family id Check total Number of Family members
if b is matching in both the sheets then move to next criteria, if not matching then mark status as details are not matched.

c) based on family id Check total Name of all Family members
if c) is matching both in both sheets then move to next criteria, if not matching then mark status as details are not matched.

d) based on family id Check date of birthe of the Particular family member in both the sheets.
if d) is matching both in both sheets then move to next criteria, if not matching then mark status as details are not matched.

in same way match all other criteria like age, gender, relation and income group.

If all the details are Matched then give status as " ALL Details are Matched"

please find attached sheet for example. Differences are marked in red colour

[JBeaucaire]

No vba is needed, you can achieve this in realtime by adding a KEY column to both sheet concatenating all of these key values into a single easy-test string. In column J of both sheets:

=B2&"-"&C2&"-"&D2&"-"&E2&"-"&F2&"-"&G2&"-"&H2

Then in the STATUS column K of Main:

=IF(ISNUMBER(MATCH($J2, Received!$J:$J, 0)), "ALL DETAILS", "NOT") & " MATCHED"

[LeoTaxi]

little bit different with VBA

Please Login or Register  to view this content.

[saravanan1981]

hi,
The code shows results in only first 13 rows and thats too with wrong result.

for example in main sheet row number 2 to 6 exactly matching with row number 2 to 6. then also result shows not matched.

Please help

[saravanan1981]

Hi,
Thank you very much for your code.

I have asked for a vba code, because the formula work in all cases.

For example in sheet main refer row number 7 and 8
7 is matched and 8 is not matched. So the actual result should be as in column L.


refer row number 18 to 23
18 and 19 is matched and rest are not matched. So the actual result should be as in column L.

In short if any one of the family member is not matched then entire family rows to be marked as not matched

[LeoTaxi]

See if this 1 is better

[saravanan1981]

leo Great!!!!

It works. Thank you so much for the code.

Can you help me with one more code.

In same type of sheet can you provide code to identify the family details in the same sheet instead of two different sheets.

Please find attached sheet for examples.

leo Great!!!!

It works. Thank you so much for the code.

Can you help me with one more code.

In same type of sheet can you provide code to identify the family details in the same sheet instead of two different sheets.

Please find attached sheet for examples

For Example:-

Details of all family members of Family id 053221 is exist in the sheet for three times. Hence the first match is Markes as “MATCH FOUND” in “status” column in Column J

First it is found in row number 2 to 6 – Marked as “MATCH FOUND”

then found in 28 to 32 – Marked as “No Match FOUND” because there is a difference in age of one family member.

Then found in row no. 38 to 42 – Marked as “Duplicate Match found” because it is exactly matching with Row no. 2 to 6 (in all details)

So first Matching family to be marked as “MATCH FOUND” and Second Exactly Matching family to be Marked as “ DUPLICATE MATCH FOUND”

[saravanan1981]

Hi Leo,

Can you make small change in the code. Just to Pull the Status columns in Both The Sheets "Main" and "Received"

Need status in Both the column. The status should be same in Sheet "Received" again respective columns as in Sheet " Main"

[LeoTaxi]

Like this ?

Kind regards
Leo