comparing 2 data

  • Thread starter Thread starter reza
  • Start date Start date
R

reza

Dear All,

i have a master data from client and my data from data entry. Now i want to
compare from both data.
ie,
sheet1 (master data from client)
Names Age
reza aditia 25
bobby 19
Cathy Sharon 20
etc

now in sheet2 (my data from data entry)
names age
tony 25
bobby 19
rezaaditia 25
CathySharon 20

i can use countifs formula, but the problems is the names in both data
doesn't same. in sheet1 Reza Aditia using space and in sheet2 Rezaaditia w/o
space, and like cathy sharon...
is there someone have a solution for this case.
many thanks for ur help

sorry, bad english, Hope u understand with i mean

reza
 
If the only concern is space; then in master data sheet cell C1 enter the
formula

=IF(COUNTIF(Sheet2!A:A,SUBSTITUTE(A1," ",)),"Exist","")
 
Reza; if you want to compare the other way around


In Sheet2 cell C1 enter the formula

=IF(SUMPRODUCT(--(SUBSTITUTE(Sheet1!$A$1:$A$10," ",)=A1)),"Exist","")
 
Jacob Skaria...

thanks for quick response and good formula. it's really work.
i have an addition, hope you still can help me..
sheet 1
col a col b col c col d
new york reza aditia 5
chicago rina ardiati 4

sheet 2
newyork rezaaditia 5 correct
chicago rina ardiati 7 not correct

my achievement.
in col d, if the data from col a - c, same then col d will have values
"correct"
but if the data from col a - c, doesn't same then col d "not correct"

i really really hope than you can provide me you formula for me...
so many thanks if you can help me for this one.

thanks
 
Try the below in Sheet1 cell D1 and copy down as required

=IF(SUMPRODUCT(--(SUBSTITUTE(Sheet1!$A$1:$A$10," ",)=SUBSTITUTE
(A1," ",))*(SUBSTITUTE(Sheet1!$B$1:$B$10," ",)=SUBSTITUTE(B1," ",))*
(Sheet1!$C$1:$C$10=C1)),"Correct","Incorrect")
 
Jacob...

still have problem...maybe better for me to send my file to you.
can i have your email...

thanks
 
Dont have access to emails at this moment. I would suggest to post back
explaining the issue with sample data so that some one else also could look
into and suggest probably a better solution; and you get a quiicker response..
 
Back
Top