A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Compare two different size lists



 
 
Thread Tools Display Modes
  #1  
Old September 8th, 2005, 08:42 PM
Ingeniero1
external usenet poster
 
Posts: n/a
Default 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  
Old September 8th, 2005, 09:19 PM
JE McGimpsey
external usenet poster
 
Posts: n/a
Default

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  
Old September 9th, 2005, 02:39 PM
Ingeniero1
external usenet poster
 
Posts: n/a
Default


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  
Old September 9th, 2005, 03:44 PM
JE McGimpsey
external usenet poster
 
Posts: n/a
Default

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  
Old September 9th, 2005, 08:36 PM
Ingeniero1
external usenet poster
 
Posts: n/a
Default


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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.