Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
Hello,
I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
If your data is in adjacent columns (eg Col_A and Col_B)
Try this in Col_C: =--IF(COUNTA(A1:B1)=2,A1<B1,0) If the columns are separated, perhaps this: =IF(AND(A1<"",E1<""),--(A10<E1),0) In either case... Copy the formula down as far as you need. You'd only need to sum the test column. Alternatively, you might consider this kind of single-formula approach to count unmatched, non-blank items: =SUMPRODUCT((A1:A20<"")*(E1:E20<"")*(A1:A20<E1: E20)) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "pm" wrote: Hello, I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
Thanks guys! Ron, I tried yours because it was shorter. So it looks like
you are using counta to first judge if there are inputs in both cells. If yes, then it is doing A1<B1. What is this function exactly? It seems to be working great now, but I've never seen that notation before. Is A1<B1 just a way to write "different"? "Ron Coderre" wrote: If your data is in adjacent columns (eg Col_A and Col_B) Try this in Col_C: =--IF(COUNTA(A1:B1)=2,A1<B1,0) If the columns are separated, perhaps this: =IF(AND(A1<"",E1<""),--(A10<E1),0) In either case... Copy the formula down as far as you need. You'd only need to sum the test column. Alternatively, you might consider this kind of single-formula approach to count unmatched, non-blank items: =SUMPRODUCT((A1:A20<"")*(E1:E20<"")*(A1:A20<E1: E20)) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "pm" wrote: Hello, I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
Not equal to
-- Regards, Peo Sjoblom "pm" wrote in message ... Thanks guys! Ron, I tried yours because it was shorter. So it looks like you are using counta to first judge if there are inputs in both cells. If yes, then it is doing A1<B1. What is this function exactly? It seems to be working great now, but I've never seen that notation before. Is A1<B1 just a way to write "different"? "Ron Coderre" wrote: If your data is in adjacent columns (eg Col_A and Col_B) Try this in Col_C: =--IF(COUNTA(A1:B1)=2,A1<B1,0) If the columns are separated, perhaps this: =IF(AND(A1<"",E1<""),--(A10<E1),0) In either case... Copy the formula down as far as you need. You'd only need to sum the test column. Alternatively, you might consider this kind of single-formula approach to count unmatched, non-blank items: =SUMPRODUCT((A1:A20<"")*(E1:E20<"")*(A1:A20<E1: E20)) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "pm" wrote: Hello, I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
Also, what exactly do the two dashes before the if do?
"Ron Coderre" wrote: If your data is in adjacent columns (eg Col_A and Col_B) Try this in Col_C: =--IF(COUNTA(A1:B1)=2,A1<B1,0) If the columns are separated, perhaps this: =IF(AND(A1<"",E1<""),--(A10<E1),0) In either case... Copy the formula down as far as you need. You'd only need to sum the test column. Alternatively, you might consider this kind of single-formula approach to count unmatched, non-blank items: =SUMPRODUCT((A1:A20<"")*(E1:E20<"")*(A1:A20<E1: E20)) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "pm" wrote: Hello, I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
Hi,
The < is the "does not equal" operator in Excel. Structured like this: =A1<B1 If A1 does not equal B1, the expression returns TRUE. If A1 equals B1, the expression returns FALSE. When you apply an arithmetic operator to a boolean value (True/False), Excel converts TRUE to 1 and FALSE to 0. In the formula I posted the Dbl-minus forces that conversion. So.... Where =A1<B1 would return TRUE or FALSE... This version =--(A1<B1) returns 1 or 0 Does that help? Post back if you have more questions *********** Regards, Ron XL2003, WinXP "pm" wrote: Thanks guys! Ron, I tried yours because it was shorter. So it looks like you are using counta to first judge if there are inputs in both cells. If yes, then it is doing A1<B1. What is this function exactly? It seems to be working great now, but I've never seen that notation before. Is A1<B1 just a way to write "different"? "Ron Coderre" wrote: If your data is in adjacent columns (eg Col_A and Col_B) Try this in Col_C: =--IF(COUNTA(A1:B1)=2,A1<B1,0) If the columns are separated, perhaps this: =IF(AND(A1<"",E1<""),--(A10<E1),0) In either case... Copy the formula down as far as you need. You'd only need to sum the test column. Alternatively, you might consider this kind of single-formula approach to count unmatched, non-blank items: =SUMPRODUCT((A1:A20<"")*(E1:E20<"")*(A1:A20<E1: E20)) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "pm" wrote: Hello, I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
Great. Explains everything. Thanks again!
"Ron Coderre" wrote: Hi, The < is the "does not equal" operator in Excel. Structured like this: =A1<B1 If A1 does not equal B1, the expression returns TRUE. If A1 equals B1, the expression returns FALSE. When you apply an arithmetic operator to a boolean value (True/False), Excel converts TRUE to 1 and FALSE to 0. In the formula I posted the Dbl-minus forces that conversion. So.... Where =A1<B1 would return TRUE or FALSE... This version =--(A1<B1) returns 1 or 0 Does that help? Post back if you have more questions *********** Regards, Ron XL2003, WinXP "pm" wrote: Thanks guys! Ron, I tried yours because it was shorter. So it looks like you are using counta to first judge if there are inputs in both cells. If yes, then it is doing A1<B1. What is this function exactly? It seems to be working great now, but I've never seen that notation before. Is A1<B1 just a way to write "different"? "Ron Coderre" wrote: If your data is in adjacent columns (eg Col_A and Col_B) Try this in Col_C: =--IF(COUNTA(A1:B1)=2,A1<B1,0) If the columns are separated, perhaps this: =IF(AND(A1<"",E1<""),--(A10<E1),0) In either case... Copy the formula down as far as you need. You'd only need to sum the test column. Alternatively, you might consider this kind of single-formula approach to count unmatched, non-blank items: =SUMPRODUCT((A1:A20<"")*(E1:E20<"")*(A1:A20<E1: E20)) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "pm" wrote: Hello, I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to count differences between columns
To expand on my earlier post....
Excel does not automatically consider boolean values (TRUE/FALSE) to be numbers. You need to coerce them into becoming numbers. That is done by applying an arithmetic operator to them. For example: If A1:=TRUE Then B1: =SUM(A1) returns 0 But all of these return 1: B1: =SUM(+A1) B1: =SUM(A1/1) B1: =SUM(A1*1) B1: =SUM(--A1) However, the first 3 of those formulas might be confused as an attempt to do a calculation. Consequently, experienced Excel users understand that a Double-Unary (--) indicates that we are forcing a conversion, not peforming a calculation. It works this way: -TRUE becomes -1 --TRUE becomes 1 (because the negative of a negative number is a positive number) Yikes...I sound like a Jr. High School math teacher. Does that make sense? *********** Regards, Ron XL2003, WinXP "pm" wrote: Also, what exactly do the two dashes before the if do? "Ron Coderre" wrote: If your data is in adjacent columns (eg Col_A and Col_B) Try this in Col_C: =--IF(COUNTA(A1:B1)=2,A1<B1,0) If the columns are separated, perhaps this: =IF(AND(A1<"",E1<""),--(A10<E1),0) In either case... Copy the formula down as far as you need. You'd only need to sum the test column. Alternatively, you might consider this kind of single-formula approach to count unmatched, non-blank items: =SUMPRODUCT((A1:A20<"")*(E1:E20<"")*(A1:A20<E1: E20)) Adjust range references to suit your situation. Is that something you can work with? *********** Regards, Ron XL2003, WinXP "pm" wrote: Hello, I have a list of 12,000 rows separated into two columns. In each row, the two columns either match or don't. So I've made a third column that is just a simple "if" function in each row that puts a "1" in if the two columns differ. I then use a "countif" at the bottom to tally the # of different rows. The problem I am running into is that some of the rows have one column with an empty cell (no data). So for this row I would have data for one column, but not both. Obviously according to my functions, excel will count these as different. I would like these rows to not be counted as different. Does anyone know how to exclude rows where one of the columns has no data from being counted as "different"? As always, many thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare two columns contents for differences | Excel Worksheet Functions | |||
Find the differences in 2 columns | Excel Discussion (Misc queries) | |||
Differences between two columns. | Excel Discussion (Misc queries) | |||
Compare 2 columns to see differences | Excel Worksheet Functions | |||
Counting differences in numbers across columns | Excel Worksheet Functions |