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
|
|||
|
|||
Finding matches in two columns
I have two columns of years. Column A has 27 years from 1911 to 2007
indicating a particular year a house was built. Column B has all years from 1800 to 2009 use to plot a graph. In column C I want to find if any of the years from 1800 through 2009 matchs one of the 27 years in column A and return a 1 if that year matches or a 0 if it does not. With the results in column C I can graph all years on the x axis and which one of them had a house built. Erik (a Word and Excel 2007 user) |
#2
|
|||
|
|||
Finding matches in two columns
One way using COUNTIF
In C2: =IF(B2="","",IF(COUNTIF(A:A,B2),1,0)) Copy down to the last row of data in col B. Joy? hit the YES below -- Max Singapore --- "Erik" wrote: I have two columns of years. Column A has 27 years from 1911 to 2007 indicating a particular year a house was built. Column B has all years from 1800 to 2009 use to plot a graph. In column C I want to find if any of the years from 1800 through 2009 matchs one of the 27 years in column A and return a 1 if that year matches or a 0 if it does not. With the results in column C I can graph all years on the x axis and which one of them had a house built |
#3
|
|||
|
|||
Finding matches in two columns
Max, You are a genius and many thanks. Would you also be so kind to explain
what is going on using IF and COUNTIF? Thanks, Erik -- Erik (a Word 2007 user) "Max" wrote: One way using COUNTIF In C2: =IF(B2="","",IF(COUNTIF(A:A,B2),1,0)) Copy down to the last row of data in col B. Joy? hit the YES below -- Max Singapore --- "Erik" wrote: I have two columns of years. Column A has 27 years from 1911 to 2007 indicating a particular year a house was built. Column B has all years from 1800 to 2009 use to plot a graph. In column C I want to find if any of the years from 1800 through 2009 matchs one of the 27 years in column A and return a 1 if that year matches or a 0 if it does not. With the results in column C I can graph all years on the x axis and which one of them had a house built |
#4
|
|||
|
|||
Finding matches in two columns
This is the base IF formula: IF(COUNTIF(A:A,B2),1,0)
COUNTIF(A:A,B2) returns the number of times that the value in B2 is found within col A. If B2 is found once, you'd get 1 as the return, 2 if twice and so on. Then .. the above used within the IF construct: IF(COUNTIF(A:A,B2),1,0) the IF will evaluate any number* greater than zero as TRUE, zero as FALSE *ie the number returned by the COUNTIF -- Max Singapore --- "Erik" wrote: Max, You are a genius and many thanks. Would you also be so kind to explain what is going on using IF and COUNTIF? Thanks, Erik -- Erik (a Word 2007 user) |
#5
|
|||
|
|||
Finding matches in two columns
the IF will evaluate any number* greater than zero
as TRUE, zero as FALSE *ie the number returned by the COUNTIF That's correct in the context of this particular application. To be more specific... IF will evaluate *any number other than 0* as TRUE, zero as FALSE. Text will return an error. =IF(-1 = TRUE =IF(0 = FALSE =IF(0.00000000025 = TRUE =IF(-0.03897 = TRUE =IF(1E100 = TRUE =IF("text" = #VALUE! -- Biff Microsoft Excel MVP "Max" wrote in message ... This is the base IF formula: IF(COUNTIF(A:A,B2),1,0) COUNTIF(A:A,B2) returns the number of times that the value in B2 is found within col A. If B2 is found once, you'd get 1 as the return, 2 if twice and so on. Then .. the above used within the IF construct: IF(COUNTIF(A:A,B2),1,0) the IF will evaluate any number* greater than zero as TRUE, zero as FALSE *ie the number returned by the COUNTIF -- Max Singapore --- "Erik" wrote: Max, You are a genius and many thanks. Would you also be so kind to explain what is going on using IF and COUNTIF? Thanks, Erik -- Erik (a Word 2007 user) |
Thread Tools | |
Display Modes | |
|
|