| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 data
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing 2 data
If the only concern is space; then in master data sheet cell C1 enter the
formula =IF(COUNTIF(Sheet2!A:A,SUBSTITUTE(A1," ",)),"Exist","") -- Jacob (MVP - Excel) "reza" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing 2 data
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 (MVP - Excel) "reza" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing 2 data
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 "Jacob Skaria" wrote: 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 (MVP - Excel) "reza" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing 2 data
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 (MVP - Excel) "reza" wrote: 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 "Jacob Skaria" wrote: 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 (MVP - Excel) "reza" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing 2 data
jacobbbbbb..........
thank you so much....now i can save my time.... "Jacob Skaria" wrote: 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 (MVP - Excel) "reza" wrote: 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 "Jacob Skaria" wrote: 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 (MVP - Excel) "reza" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing 2 data
Jacob...
still have problem...maybe better for me to send my file to you. can i have your email... thanks "Jacob Skaria" wrote: 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 (MVP - Excel) "reza" wrote: 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 "Jacob Skaria" wrote: 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 (MVP - Excel) "reza" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing 2 data
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.. -- Jacob (MVP - Excel) "reza" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing two columns of data and return unique data in another co | Excel Discussion (Misc queries) | |||
| Comparing data | Excel Discussion (Misc queries) | |||
| help comparing two sets od data to find the odd data | Excel Worksheet Functions | |||
| comparing lists of data to remove duplicate data | Excel Discussion (Misc queries) | |||
| Comparing data in two columns and highlighting the data | Excel Worksheet Functions | |||
Linear Mode
