How it Works1 min ago
Excel formula question
4 Answers
I have one set of data that covering a year records where certain events have taken place all over a city. This includes columns for location data. Including postcodes. I also have another spreadsheet that contains all postcodes for a defined area of the city. I need to work out how many of the events in data set 1 occurred within the defined area in data set 2. Normally I'd do this on a GIS system but it's broken. Is there a way of running a formula that will look at the postcode in dataset 1, see if it is in the list for dataset 2 and return a yes or no answer. Some postcodes in dataset 1 will appear more than once as there has been more than 1 event there in a year? PS I've got a really tight deadline
Answers
1 to 4 of 4
Best Answer
No best answer has yet been selected by Lillabet. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
You could use the countif function.
In a new column on sheet 1 insert
=(COUNTIF($X$n1:$X$n2,Yn3))
=(COUNTIF($X$n1:$X$n2,Yn4))
=(COUNTIF($X$n1:$X$n2,Yn5))
etc.
Fill Down the whole column.
Where X and n1 is the cell reference of the first cell in your column of postcodes in your second sheet and X and n2 is the the cell reference of the last cell in your column of postcodes in your second sheet. The dollar signs fix the cell references so that the range stays consistent.
Yn3, Yn4, Yn5 should be the first, second & third (and so on) cell references of your postcodes in sheet 1.
The function will then return the number of times that a postcode in the column on sheet 1 appears in the column on sheet 2.
In a new column on sheet 1 insert
=(COUNTIF($X$n1:$X$n2,Yn3))
=(COUNTIF($X$n1:$X$n2,Yn4))
=(COUNTIF($X$n1:$X$n2,Yn5))
etc.
Fill Down the whole column.
Where X and n1 is the cell reference of the first cell in your column of postcodes in your second sheet and X and n2 is the the cell reference of the last cell in your column of postcodes in your second sheet. The dollar signs fix the cell references so that the range stays consistent.
Yn3, Yn4, Yn5 should be the first, second & third (and so on) cell references of your postcodes in sheet 1.
The function will then return the number of times that a postcode in the column on sheet 1 appears in the column on sheet 2.
Sorry, I just tried to follow that and got the response VALUE in each and every cell.
Maybe I'm doing it wrong - I'm really not an excel expert.
On sheet 1 the postcodes are in column G and sheet 2 they are in column A. If EuanC or anyone else can help put the formula into what I actually need to put in the top cell before filling down the column that would help. I understand about the $ sign fixing the cell reference.
The other thing I would have thought was that I would have to do the counting on sheet 2 where each postcode appears only once. Because surely if the postcode LS227FJ (for example) appears 10 times in the list in dataset 1 and I fill in the above formula correctly then it will tell me ten times that it appears 10 times?
Maybe I'm doing it wrong - I'm really not an excel expert.
On sheet 1 the postcodes are in column G and sheet 2 they are in column A. If EuanC or anyone else can help put the formula into what I actually need to put in the top cell before filling down the column that would help. I understand about the $ sign fixing the cell reference.
The other thing I would have thought was that I would have to do the counting on sheet 2 where each postcode appears only once. Because surely if the postcode LS227FJ (for example) appears 10 times in the list in dataset 1 and I fill in the above formula correctly then it will tell me ten times that it appears 10 times?
1 to 4 of 4
