If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Compare two different size lists
(I searched, but the key words yield way too many non-related hits) I have two lists, A10:B400 and C10300. All the sets of data in CD (a name and a number respectively) are in AB, but AB, obviously, has more sets of data than CD. Is there a function, which I can enter in E10:E400, for example, that will compare each value in A with _all_ the values in C, and if the A value is not in C, it will write the missing value(s) in E (and F), in the same row as the A 'source'? What I trying to find are the sets of data in AB that are not in CD. Please let me know if I didn't make my query clear enough... Thanks alex -- Ingeniero1 ------------------------------------------------------------------------ Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029 View this thread: http://www.excelforum.com/showthread...hreadid=466041 |
#2
|
|||
|
|||
one way:
E10: =IF(COUNTIF(C:C,A10),"",A10) F10: =IF(E10="","",B10) In article , Ingeniero1 wrote: (I searched, but the key words yield way too many non-related hits) I have two lists, A10:B400 and C10300. All the sets of data in CD (a name and a number respectively) are in AB, but AB, obviously, has more sets of data than CD. Is there a function, which I can enter in E10:E400, for example, that will compare each value in A with _all_ the values in C, and if the A value is not in C, it will write the missing value(s) in E (and F), in the same row as the A 'source'? What I trying to find are the sets of data in AB that are not in CD. Please let me know if I didn't make my query clear enough... Thanks alex |
#3
|
|||
|
|||
JE, That works great! I thought that I was well familiar with the 'COUNTIF' function, but had never used it with this specific notation - {C:C,A10}, as in {E10: =IF(COUNTIF(C:C,A10),"",A10)} I can see what it does, but when you get a minute (or two) could you briefly explain it? Thanks X2 Alex -- Ingeniero1 ------------------------------------------------------------------------ Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029 View this thread: http://www.excelforum.com/showthread...hreadid=466041 |
#4
|
|||
|
|||
The conditional:
COUNTIF(C:C,A10) returns a positive number if the contents of cell A10 are found at least once in column C ("C:C" is equivalent to "C1:C65536"), 0 if not. XL interprets zero values as FALSE, non-zero numbers as TRUE. =IF(T/F,"",A10) will return a null string if the conditional is TRUE, the contents of A10 if not. So =IF(COUNTIF(C:C,A10),"",A10) will return a null string if the value in A10 is found in column C one or more times, otherwise the contents of A10 are returned. In article , Ingeniero1 wrote: I thought that I was well familiar with the 'COUNTIF' function, but had never used it with this specific notation - {C:C,A10}, as in {E10: =IF(COUNTIF(C:C,A10),"",A10)} I can see what it does, but when you get a minute (or two) could you briefly explain it? |
#5
|
|||
|
|||
Excellent explanation! What I learned: • C:C = C1:C64636 • That "COUNTIF" can return "0=True" and "0=False", and not just 'counts' • The use of "True" and "False" within "IF(test,if-true,if-false)" Thanks!!!!!! Alex -- Ingeniero1 ------------------------------------------------------------------------ Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029 View this thread: http://www.excelforum.com/showthread...hreadid=466041 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
mdb file size increases 50% adding one record | Michael W | Database Design | 2 | May 25th, 2005 12:01 PM |
using formula to compare two text lists that are not alike and ma. | Maggie | Worksheet Functions | 7 | April 14th, 2005 06:52 PM |
compare data in two lists to find matching entries | Chris(new user) | General Discussion | 1 | March 19th, 2005 10:49 PM |
how do i change the font size on drop down cell validation lists | ddanbury | General Discussion | 2 | October 4th, 2004 08:33 PM |
Need help on changing text size | Luke Sineath | Page Layout | 7 | June 11th, 2004 05:53 AM |