Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
Compare 2 sets of data and return a true or false in a third column.
Can i use Pivots or lookups or any functions I need list 2 to look at list 1 and compare and let me know that ID 1 is mapped to the same Name & return true/false. What makes it complicated is that Some IDs have 2 names mapped to it & the names can be different. But even if 1 of the name is different, if it returns a false, it will be sufficient. List 1 List 2 ID Name ID Name 1 RW 1 RPS 2 ROP 2 CPM 3 RW 2 ROP 4 EPR 3 RW 5 DCE 4 DCE 5 RW 5 EPR 5 RW This is what the result should look like. ID Name 1 RPS False 2 CPM False 2 ROP False 3 RW True 4 DCE False 5 EPR False 5 RW False |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
I have considered list 1 to be in Column A:B and List2 in Column D:E
Column A Column B Column D Column E List1.ID List1.Name List2.ID List2.Name Formuls used in cell F2: =IF(ISERROR(VLOOKUP(D2,A:B,2,0)),"False",IF(VLOOKU P(D2,A:B,2,0)=E2,"True","False")) My Result: ID Name 1 RPS False 2 CPM False 2 ROP True (Different from one which you mentioned) 3 RW True 4 DCE False 5 EPR False 5 RW False -- Cheers Rodrick "Rajula" wrote: Compare 2 sets of data and return a true or false in a third column. Can i use Pivots or lookups or any functions I need list 2 to look at list 1 and compare and let me know that ID 1 is mapped to the same Name & return true/false. What makes it complicated is that Some IDs have 2 names mapped to it & the names can be different. But even if 1 of the name is different, if it returns a false, it will be sufficient. List 1 List 2 ID Name ID Name 1 RW 1 RPS 2 ROP 2 CPM 3 RW 2 ROP 4 EPR 3 RW 5 DCE 4 DCE 5 RW 5 EPR 5 RW This is what the result should look like. ID Name 1 RPS False 2 CPM False 2 ROP False 3 RW True 4 DCE False 5 EPR False 5 RW False |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
One guess n play to try out ..
Assume tables as posted within A1:E8, list 1 in cols A - B, list 2 in cols C - D Put in E2, array-enter* to confirm the formula: =ISNUMBER(MATCH(1,($A$2:$A$7=C2)*($B$2:$B$7=D2),0) ) Copy E2 down to the last row of data in List 2, ie to E8 *press CTRL+SHIFT+ENTER These will be the results returned: ID Name 1 RPS FALSE 2 CPM FALSE 2 ROP TRUE 3 RW TRUE 4 DCE FALSE 5 EPR FALSE 5 RW TRUE -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rajula" wrote: Compare 2 sets of data and return a true or false in a third column. Can i use Pivots or lookups or any functions I need list 2 to look at list 1 and compare and let me know that ID 1 is mapped to the same Name & return true/false. What makes it complicated is that Some IDs have 2 names mapped to it & the names can be different. But even if 1 of the name is different, if it returns a false, it will be sufficient. List 1 List 2 ID Name ID Name 1 RW 1 RPS 2 ROP 2 CPM 3 RW 2 ROP 4 EPR 3 RW 5 DCE 4 DCE 5 RW 5 EPR 5 RW This is what the result should look like. ID Name 1 RPS False 2 CPM False 2 ROP False 3 RW True 4 DCE False 5 EPR False 5 RW False |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
Hi Rodrick,
Thanks for the response. It certainly is going in the right direction. In the result as, for ID 2, its returns TRUE, But for ID 5, It returns false. My problem would me solved, its ID 2 also returned a FALSE. Is there a way. Bcos my actual data is quiet large. regards Rajula "Rodrick" wrote: I have considered list 1 to be in Column A:B and List2 in Column D:E Column A Column B Column D Column E List1.ID List1.Name List2.ID List2.Name Formuls used in cell F2: =IF(ISERROR(VLOOKUP(D2,A:B,2,0)),"False",IF(VLOOKU P(D2,A:B,2,0)=E2,"True","False")) My Result: ID Name 1 RPS False 2 CPM False 2 ROP True (Different from one which you mentioned) 3 RW True 4 DCE False 5 EPR False 5 RW False -- Cheers Rodrick "Rajula" wrote: Compare 2 sets of data and return a true or false in a third column. Can i use Pivots or lookups or any functions I need list 2 to look at list 1 and compare and let me know that ID 1 is mapped to the same Name & return true/false. What makes it complicated is that Some IDs have 2 names mapped to it & the names can be different. But even if 1 of the name is different, if it returns a false, it will be sufficient. List 1 List 2 ID Name ID Name 1 RW 1 RPS 2 ROP 2 CPM 3 RW 2 ROP 4 EPR 3 RW 5 DCE 4 DCE 5 RW 5 EPR 5 RW This is what the result should look like. ID Name 1 RPS False 2 CPM False 2 ROP False 3 RW True 4 DCE False 5 EPR False 5 RW False |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
Hi there,
Can I some some how get the values get Like this. Can i combine the 2 lists/data sets? ID List 1 List 2 List 1 Vs List 2 1 RW RPS False 2 ROP ROP True 2 CPM False 3 RW RW True 4 EPR DCE False 5 DCE EPR False 5 RW RW False 6 NOT False 6 RV False 6 RV False 6 ROP False 6 RW False Regards Rajula "Max" wrote: One guess n play to try out .. Assume tables as posted within A1:E8, list 1 in cols A - B, list 2 in cols C - D Put in E2, array-enter* to confirm the formula: =ISNUMBER(MATCH(1,($A$2:$A$7=C2)*($B$2:$B$7=D2),0) ) Copy E2 down to the last row of data in List 2, ie to E8 *press CTRL+SHIFT+ENTER These will be the results returned: ID Name 1 RPS FALSE 2 CPM FALSE 2 ROP TRUE 3 RW TRUE 4 DCE FALSE 5 EPR FALSE 5 RW TRUE -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rajula" wrote: Compare 2 sets of data and return a true or false in a third column. Can i use Pivots or lookups or any functions I need list 2 to look at list 1 and compare and let me know that ID 1 is mapped to the same Name & return true/false. What makes it complicated is that Some IDs have 2 names mapped to it & the names can be different. But even if 1 of the name is different, if it returns a false, it will be sufficient. List 1 List 2 ID Name ID Name 1 RW 1 RPS 2 ROP 2 CPM 3 RW 2 ROP 4 EPR 3 RW 5 DCE 4 DCE 5 RW 5 EPR 5 RW This is what the result should look like. ID Name 1 RPS False 2 CPM False 2 ROP False 3 RW True 4 DCE False 5 EPR False 5 RW False |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
Did you try the previous response to your original post?
Did it give you the expected results? As to your new setup/query, Try in D2: =IF(C2="","",C2=B2) Copy down The above will return results that tally 99.9% with those you posted, except for D8, where it'll return TRUE instead of your indicated FALSE. The consistent logic applied points to TRUE as being the correct return for D8, hence think you might have a typo in your D8's FALSE as posted. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
Hi Max,
I tried your previous formula. It works fine & gives the expected result. Thanks for the function. But taking it to the next level. I want to know what are the values in the lists. THats why i have posted another question whether i can combine the 2 lists. So that i know for given the IDs what are the names in the 2 different lists. Regards Rajula. "Max" wrote: Did you try the previous response to your original post? Did it give you the expected results? As to your new setup/query, Try in D2: =IF(C2="","",C2=B2) Copy down The above will return results that tally 99.9% with those you posted, except for D8, where it'll return TRUE instead of your indicated FALSE. The consistent logic applied points to TRUE as being the correct return for D8, hence think you might have a typo in your D8's FALSE as posted. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare 2 sets of data
"Rajula" wrote in message
... Hi Max, I tried your previous formula. It works fine & gives the expected result. Thanks for the function. Fine, that closes it for this thread, then. But taking it to the next level. I want to know what are the values in the lists. THats why i have posted another question whether i can combine the 2 lists. So that i know for given the IDs what are the names in the 2 different lists. This is a new query which I note you have since put in as a new posting 30 hours ago. But no responses so far, guess it must have proven quite tough to resolve. If I have any thoughts on that, I'll respond in your new posting. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show 3 sets of data per data point in a scatter plot | Charts and Charting in Excel | |||
how do i link up 2 sets of data into 1 set of data in excel | Excel Worksheet Functions | |||
help comparing two sets od data to find the odd data | Excel Worksheet Functions | |||
How do I compare 2 sets of data and highlight differences? | Excel Worksheet Functions | |||
How can I compare 2 sets of Social Security #'s and Identify dupes | Excel Discussion (Misc queries) |