Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Compare List
Please help, I have two lists in column, no in the same order, List1 List2 a c b z c h d r g g I want to know the elements of the list 1 that are not in the list2 (a, b, d) and the element od the list2 that are not in the list1 (z,h,r) Exits any formula for it? Thanks -- mc-iii ------------------------------------------------------------------------ mc-iii's Profile: http://www.excelforum.com/member.php...o&userid=10709 View this thread: http://www.excelforum.com/showthread...hreadid=277809 |
#2
|
|||
|
|||
Hi
Check out Chip Pearson's website for ideas on working with duplicates http://www.cpearson.com/excel/duplicat.htm Cheers JulieD "mc-iii" wrote in message ... Please help, I have two lists in column, no in the same order, List1 List2 a c b z c h d r g g I want to know the elements of the list 1 that are not in the list2 (a, b, d) and the element od the list2 that are not in the list1 (z,h,r) Exits any formula for it? Thanks -- mc-iii ------------------------------------------------------------------------ mc-iii's Profile: http://www.excelforum.com/member.php...o&userid=10709 View this thread: http://www.excelforum.com/showthread...hreadid=277809 |
#3
|
|||
|
|||
Check out Chip Pearson's web site on the subject:
http://www.cpearson.com/excel/duplicat.htm -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "mc-iii" wrote in message ... Please help, I have two lists in column, no in the same order, List1 List2 a c b z c h d r g g I want to know the elements of the list 1 that are not in the list2 (a, b, d) and the element od the list2 that are not in the list1 (z,h,r) Exits any formula for it? Thanks -- mc-iii ------------------------------------------------------------------------ mc-iii's Profile: http://www.excelforum.com/member.php...o&userid=10709 View this thread: http://www.excelforum.com/showthread...hreadid=277809 |
#4
|
|||
|
|||
In what follows, I'll try to sell a different formula system... Let A2:B7 house the sample you provided: {"List1","List2";"a","c";"b","z";"c","h";"d","r";" g","g"} C1 must house a 0. C2: Count1 C3, copied down: =IF((A3<"")*ISNA(MATCH(A3,$B$3:$B$7,0)),LOOKUP(9. 99999999999999E+307,$C$1:C2)+1,"") D1 must house a 0. D2: Count2 D3, copied down: =IF((A3<"")*ISNA(MATCH(B3,$A$3:$A$7,0)),LOOKUP(9. 99999999999999E+307,$D$1:D2)+1,"") E1, copied to F1: =LOOKUP(9.99999999999999E+307,C3:C7) E2 and F2 house the labels: Not in List2 and Not in List1 E3, copied across to F3 then down: =IF(ROW()-ROW(E$3)+1<=$E$1,INDEX(A$3:A$7,MATCH(ROW()-ROW(E$3)+1,C$3:C$7)),"") The results area, E1:F5, will now show: {3,3; "Not in List2","Not in List1"; "a","z"; "b","h"; "d","r"} mc-iii Wrote: Please help, I have two lists in column, no in the same order, List1 List2 a c b z c h d r g g I want to know the elements of the list 1 that are not in the list2 (a, b, d) and the element od the list2 that are not in the list1 (z,h,r) Exits any formula for it? Thanks -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=277809 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a dynamic list | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) | |||
Creating a dynamic list | Excel Worksheet Functions | |||
Creating a list from an existing list. | Excel Worksheet Functions |