Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then,
return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Here is one way:
=N(COUNTIF(A$1:A$10,C1)0) -- Cheers, Shane Devenshire "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Try this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,B1:B10,0)))) if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. If Joe Schmidt shows up more than once in A1:A10 and at least once in B1:B10 each instance in A1:A10 will be counted. Biff "adodson" wrote in message ... I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
One interp & play, presuming that what you're really after is to extract the
uniques list of names within A1:B20 In A11: =IF(B1="","",B1) Copy A11 down to A20 In D1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In E1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) )) Select D1:E1, copy down to E20. Col E returns the list of unique names within A1:B20, all neatly bunched at the top. And if you also want the count of the unique names extracted in col E: In F1, copy down to F20: =IF(E1="","",COUNTIF(A:A,E1)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Thank you for the extensive answer, but I need it to be contained in one cell
since this is in a report format. "Max" wrote: One interp & play, presuming that what you're really after is to extract the uniques list of names within A1:B20 In A11: =IF(B1="","",B1) Copy A11 down to A20 In D1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In E1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) )) Select D1:E1, copy down to E20. Col E returns the list of unique names within A1:B20, all neatly bunched at the top. And if you also want the count of the unique names extracted in col E: In F1, copy down to F20: =IF(E1="","",COUNTIF(A:A,E1)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
This will work for now. I tend to want to troubleshoot potential future
problems which is were the unique portion came in. Thanks. I'm still open for a single cell formula that would accomplish the original request if you think of one. :) "T. Valko" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,B1:B10,0)))) if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. If Joe Schmidt shows up more than once in A1:A10 and at least once in B1:B10 each instance in A1:A10 will be counted. Biff "adodson" wrote in message ... I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
In one cell:
{=SUM(N(NOT(ISERR(1/(COUNTIF(A1:A20,B1:B20)0)))))} An array formula, so don't type the outer {}'s just press Shift Ctrl Enter to enter the formula. -- Thanks, Shane Devenshire "adodson" wrote: Thank you for the extensive answer, but I need it to be contained in one cell since this is in a report format. "Max" wrote: One interp & play, presuming that what you're really after is to extract the uniques list of names within A1:B20 In A11: =IF(B1="","",B1) Copy A11 down to A20 In D1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In E1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) )) Select D1:E1, copy down to E20. Col E returns the list of unique names within A1:B20, all neatly bunched at the top. And if you also want the count of the unique names extracted in col E: In F1, copy down to F20: =IF(E1="","",COUNTIF(A:A,E1)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
maybe i am confused but let's try anyway
D1 = Joe Schmidt =SUM((A1:A10=B1:B10)*(A1:A10=D1)*{1}) hit ctrl-shft-enter.. -- ***** birds of the same feather flock together.. "adodson" wrote: Thank you for the extensive answer, but I need it to be contained in one cell since this is in a report format. "Max" wrote: One interp & play, presuming that what you're really after is to extract the uniques list of names within A1:B20 In A11: =IF(B1="","",B1) Copy A11 down to A20 In D1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In E1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) )) Select D1:E1, copy down to E20. Col E returns the list of unique names within A1:B20, all neatly bunched at the top. And if you also want the count of the unique names extracted in col E: In F1, copy down to F20: =IF(E1="","",COUNTIF(A:A,E1)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Try this:
=SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
So, you only want to count the *uniques* in column A that also appear in
column B? Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(N(FREQUENCY(IF((ISNUMBER(MATCH(A1:A10,B1:B10, 0)))*(ISNUMBER(A1:A10)),MATCH(A1:A10&"",A1:A10&"", 0)),MATCH(A1:A10&"",A1:A10&"",0))0)) ............A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 The result based on the above example is 2. Biff "adodson" wrote in message ... This will work for now. I tend to want to troubleshoot potential future problems which is were the unique portion came in. Thanks. I'm still open for a single cell formula that would accomplish the original request if you think of one. :) "T. Valko" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,B1:B10,0)))) if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. If Joe Schmidt shows up more than once in A1:A10 and at least once in B1:B10 each instance in A1:A10 will be counted. Biff "adodson" wrote in message ... I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Well THAT sure didn't work as soon as I put some duplicates in.
*********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
I tested this using numbers and so the formula has an expression that tests
for numbers. (ISNUMBER(A1:A10)) This change will work for text or numbers or both (array entered): =SUM(N(FREQUENCY(IF((ISNUMBER(MATCH(A1:A10,B1:B10, 0)))*(A1:A10<""),MATCH(A1:A10&"",A1:A10&"",0)),MA TCH(A1:A10&"",A1:A10&"",0))0)) Biff "T. Valko" wrote in message ... So, you only want to count the *uniques* in column A that also appear in column B? Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =SUM(N(FREQUENCY(IF((ISNUMBER(MATCH(A1:A10,B1:B10, 0)))*(ISNUMBER(A1:A10)),MATCH(A1:A10&"",A1:A10&"", 0)),MATCH(A1:A10&"",A1:A10&"",0))0)) ...........A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 The result based on the above example is 2. Biff "adodson" wrote in message ... This will work for now. I tend to want to troubleshoot potential future problems which is were the unique portion came in. Thanks. I'm still open for a single cell formula that would accomplish the original request if you think of one. :) "T. Valko" wrote: Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,B1:B10,0)))) if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. If Joe Schmidt shows up more than once in A1:A10 and at least once in B1:B10 each instance in A1:A10 will be counted. Biff "adodson" wrote in message ... I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
No prob. Thanks for the feedback. On retrospect, think my original take
on your intent was perhaps a bit wayward <g. Can see that you've got quite a number of other responses to try since, besides Biff's follow ups. It's good if you would take the time to post some feedback to *all* responders who offer you their thoughts on your problem. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Jan 24, 1:11 am, adodson wrote: Thank you for the extensive answer, but I need it to be contained in one cell since this is in a report format. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
OK....pretty sure I got it this time:
For 2 lists (A1:A100 and B1:B100) This formula returns the count of unique items from Col_A that are in Col_B =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&"")) It is durable against text, numbers, and blanks. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well THAT sure didn't work as soon as I put some duplicates in. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Try it on this data set:
............A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22............. Then put another 0 in B7. Also seems to get tripped up on formula blanks. Biff "Ron Coderre" wrote in message ... OK....pretty sure I got it this time: For 2 lists (A1:A100 and B1:B100) This formula returns the count of unique items from Col_A that are in Col_B =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&"")) It is durable against text, numbers, and blanks. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well THAT sure didn't work as soon as I put some duplicates in. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Thanks, Biff....
I ran into the "formula blanks" problem a little while ago and adjusted the formula....but wasn't sure it would be an issue. As you pointed out..it is. This one is the latest in a series of final formulas : \ =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) Note: The only potential issue I can see with that one is that it wont match a formula blank in Col_A against a formula blank in Col_B. Although, that's probably a bonafide feature (since formula blanks are intended to be blanks, right). Of course, error values are an issue in general. Your thoughts? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22............. Then put another 0 in B7. Also seems to get tripped up on formula blanks. Biff "Ron Coderre" wrote in message ... OK....pretty sure I got it this time: For 2 lists (A1:A100 and B1:B100) This formula returns the count of unique items from Col_A that are in Col_B =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&"")) It is durable against text, numbers, and blanks. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well THAT sure didn't work as soon as I put some duplicates in. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Try it on this data set:
............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 Or, this one: ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22..........0 Biff "Ron Coderre" wrote in message ... Thanks, Biff.... I ran into the "formula blanks" problem a little while ago and adjusted the formula....but wasn't sure it would be an issue. As you pointed out..it is. This one is the latest in a series of final formulas : \ =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) Note: The only potential issue I can see with that one is that it wont match a formula blank in Col_A against a formula blank in Col_B. Although, that's probably a bonafide feature (since formula blanks are intended to be blanks, right). Of course, error values are an issue in general. Your thoughts? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22............. Then put another 0 in B7. Also seems to get tripped up on formula blanks. Biff "Ron Coderre" wrote in message ... OK....pretty sure I got it this time: For 2 lists (A1:A100 and B1:B100) This formula returns the count of unique items from Col_A that are in Col_B =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&"")) It is durable against text, numbers, and blanks. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well THAT sure didn't work as soon as I put some duplicates in. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Hey, Biff....I'm a bit puzzled by your post.
This formula =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) .....returns 2 for this one, which is correct (10 and 22 are hits) .............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 ....and returns 3 for this one, also correct (10, 22 and 0 are hits) .............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22..........0 Are you saying it works? or am I missing something? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 Or, this one: ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22..........0 Biff "Ron Coderre" wrote in message ... Thanks, Biff.... I ran into the "formula blanks" problem a little while ago and adjusted the formula....but wasn't sure it would be an issue. As you pointed out..it is. This one is the latest in a series of final formulas : \ =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) Note: The only potential issue I can see with that one is that it wont match a formula blank in Col_A against a formula blank in Col_B. Although, that's probably a bonafide feature (since formula blanks are intended to be blanks, right). Of course, error values are an issue in general. Your thoughts? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22............. Then put another 0 in B7. Also seems to get tripped up on formula blanks. Biff "Ron Coderre" wrote in message ... OK....pretty sure I got it this time: For 2 lists (A1:A100 and B1:B100) This formula returns the count of unique items from Col_A that are in Col_B =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&"")) It is durable against text, numbers, and blanks. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well THAT sure didn't work as soon as I put some duplicates in. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
Ok, I figured out what the problem was. See these screencaps:
http://img153.imageshack.us/img153/9...sdiverr2od.jpg http://img186.imageshack.us/img186/5...esnoerr4zf.jpg You'll notice in the first screencap the result of the formula is a #DIV/0! error and in the 2nd screencap the result is correct. Notice the range in the formula used in the first screencap and the range used in the formula in the 2nd screencap. Although I don't know this to be fact, I think this type of formula has a problem with the used range. I mentioned this in a thread several months ago and Bob Phillips verified that he also experienced this before. In the first screencap the formula references exceed the current used range. In the 2nd screencap I changed the references to be within the current used range. Try this test. I'm using Excel 2002 on WinXP (all updates current) Open a *new fresh empty* workbook. Enter this formula in B1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) The result is 0 as you'd expect since the referenced range is empty. Now, start entering data in A1 and on down one cell at a time. The formula returns #DIV/0! until an entry is made in A10. This also sets the used range referenced in the formula. Now, if you clear the contents of the range and then start entering new data the formula works as expected. Another one of Excel's "features"! So, your formula does in fact work and I like it!!!!!!!!! Biff "Ron Coderre" wrote in message ... Hey, Biff....I'm a bit puzzled by your post. This formula =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) ....returns 2 for this one, which is correct (10 and 22 are hits) ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 ...and returns 3 for this one, also correct (10, 22 and 0 are hits) ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22..........0 Are you saying it works? or am I missing something? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 Or, this one: ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22..........0 Biff "Ron Coderre" wrote in message ... Thanks, Biff.... I ran into the "formula blanks" problem a little while ago and adjusted the formula....but wasn't sure it would be an issue. As you pointed out..it is. This one is the latest in a series of final formulas : \ =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) Note: The only potential issue I can see with that one is that it wont match a formula blank in Col_A against a formula blank in Col_B. Although, that's probably a bonafide feature (since formula blanks are intended to be blanks, right). Of course, error values are an issue in general. Your thoughts? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22............. Then put another 0 in B7. Also seems to get tripped up on formula blanks. Biff "Ron Coderre" wrote in message ... OK....pretty sure I got it this time: For 2 lists (A1:A100 and B1:B100) This formula returns the count of unique items from Col_A that are in Col_B =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&"")) It is durable against text, numbers, and blanks. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well THAT sure didn't work as soon as I put some duplicates in. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare columns, count matches
I'd opt for the dynamic named range.
I "discovered" this setting up a template. I needed to extract uniques from a range and couldn't figure out why it didn't work. I knew the formula was correct. As soon as I used the dynamic range it worked as expected. I think I'll put this formula in my stash. It's now in mine! Good job! Biff "Ron Coderre" wrote in message ... Biff I ran your scenario and (of course) ran into the DIV/0 error. I only see 2 ways around it (neither very attractive): 1)Dynamic Range Names Example: Name: rngListOne Refers to: =OFFSET(Sheet8!$A$1,0,0,MAX(INDEX((Sheet8!$A$1:$A$ 20<"")*ROW(Sheet8!$A$1:$A$20),0)),1) 2)This hideous thing: =SUMPRODUCT((COUNTIF(A1:INDEX(A1:A20,MAX(INDEX((A1 :A20<"")*ROW(A1:A20),0))),B1:INDEX(B1:B20,MAX(IND EX((B1:B20<"")*ROW(B1:B20),0)))&"")0)/COUNTIF(B1:INDEX(B1:B20,MAX(INDEX((B1:B20<"")*ROW (B1:B20),0))),B1:INDEX(B1:B20,MAX(INDEX((B1:B20<" ")*ROW(B1:B20),0)))&"")*(B1:INDEX(B1:B20,MAX(INDEX ((B1:B20<"")*ROW(B1:B20),0)))<"")) The best pre-emptive solution is to just make sure the used range includes the referenced range. Thanks for taking the time to help me work through this one. I think I'll put this formula in my stash. *********** Best Regards, Ron XL2002, WinXP "T. Valko" wrote: Ok, I figured out what the problem was. See these screencaps: http://img153.imageshack.us/img153/9...sdiverr2od.jpg http://img186.imageshack.us/img186/5...esnoerr4zf.jpg You'll notice in the first screencap the result of the formula is a #DIV/0! error and in the 2nd screencap the result is correct. Notice the range in the formula used in the first screencap and the range used in the formula in the 2nd screencap. Although I don't know this to be fact, I think this type of formula has a problem with the used range. I mentioned this in a thread several months ago and Bob Phillips verified that he also experienced this before. In the first screencap the formula references exceed the current used range. In the 2nd screencap I changed the references to be within the current used range. Try this test. I'm using Excel 2002 on WinXP (all updates current) Open a *new fresh empty* workbook. Enter this formula in B1: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) The result is 0 as you'd expect since the referenced range is empty. Now, start entering data in A1 and on down one cell at a time. The formula returns #DIV/0! until an entry is made in A10. This also sets the used range referenced in the formula. Now, if you clear the contents of the range and then start entering new data the formula works as expected. Another one of Excel's "features"! So, your formula does in fact work and I like it!!!!!!!!! Biff "Ron Coderre" wrote in message ... Hey, Biff....I'm a bit puzzled by your post. This formula =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) ....returns 2 for this one, which is correct (10 and 22 are hits) ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 ...and returns 3 for this one, also correct (10, 22 and 0 are hits) ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22..........0 Are you saying it works? or am I missing something? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.....................22 7........22..........0 Or, this one: ............A..........B 1........10..........0 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22..........0 Biff "Ron Coderre" wrote in message ... Thanks, Biff.... I ran into the "formula blanks" problem a little while ago and adjusted the formula....but wasn't sure it would be an issue. As you pointed out..it is. This one is the latest in a series of final formulas : \ =SUMPRODUCT((COUNTIF(A1:A20,B1:B20&"")0)/COUNTIF(B1:B20,B1:B20&"")*(B1:B20<"")) Note: The only potential issue I can see with that one is that it wont match a formula blank in Col_A against a formula blank in Col_B. Although, that's probably a bonafide feature (since formula blanks are intended to be blanks, right). Of course, error values are an issue in general. Your thoughts? *********** Regards, Ron XL2002, WinXP "T. Valko" wrote: Try it on this data set: ............A..........B 1........10............. 2........22.........10 3........10.........10 4........17.........30 5........10.........22 6.........0..........22 7........22............. Then put another 0 in B7. Also seems to get tripped up on formula blanks. Biff "Ron Coderre" wrote in message ... OK....pretty sure I got it this time: For 2 lists (A1:A100 and B1:B100) This formula returns the count of unique items from Col_A that are in Col_B =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.000 1,1)/COUNTIF(B1:B100,B1:B100&"")) It is durable against text, numbers, and blanks. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Well THAT sure didn't work as soon as I put some duplicates in. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Try this: =SUMPRODUCT(CEILING(COUNTIF(A1:A100,B1:B100)*0.001 ,1)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "adodson" wrote: I'm looking to compare two lists (a1:a10 compared to b1:b10, unnamed). Then, return the count of original matches between the two lists. For example, if Joe Schmidt shows once in a1:a10 and twice in b1:b10, then count it only once. Any advice greatly appreciated? Thank in advance. P.S. = this will be incorporated into an If statement. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare the 1st 9 digits in two columns looking for duplicates | Excel Worksheet Functions | |||
how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
COUNT INFORMATION IN COLUMN B ONLY IF A1 MATCHES K1 COUNT THAT CE. | Excel Worksheet Functions | |||
Compare Columns | Excel Discussion (Misc queries) | |||
compare columns of different worksheets | Excel Discussion (Misc queries) |