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

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

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

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

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

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
---

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
---

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
---