If You Could Live In Another Decade,...
ChatterBank1 min ago
Excel Formula
17 Answers
I have five columns each named by year ie 15/16 to 19/20 all on one worksheet. Under these years are a host of names. These names may appear in all years and some will be new or only feature in one year. I want to be able to see which of these names appear under each year and those that do not. Is there a simple way to do this? I know only basic stuff in excel and would be grateful for any input.
Answers
1 to 17 of 17
Best Answer
No best answer has yet been selected by auzzie. 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.
Thank you for your replies, but it doesn't do what I want it to do.
15/16 16/17 17/18 18/19 19/20
ABC ABC ABC ABC
DEF fgh DEF DEF DEF
GHI GHI FGH FGH Fgh
Hope the above aligns itself properly. ABC is listed under 15/16, 16/17 and 18/19. DEF is listed under 15/16, 17/18, 18/19, 19/20 . What I need is a table to show me that ABC is listed under 15/16 through to 18/19 and that DEF is only listed under 15/16, 17/18,18/19 and 19/20. I have quite a few names to go through. I'm thinking that maybe a pivot table is called for but i'm a bit rubbish with them and not sure that it will work for just two categories ie year and name?
15/16 16/17 17/18 18/19 19/20
ABC ABC ABC ABC
DEF fgh DEF DEF DEF
GHI GHI FGH FGH Fgh
Hope the above aligns itself properly. ABC is listed under 15/16, 16/17 and 18/19. DEF is listed under 15/16, 17/18, 18/19, 19/20 . What I need is a table to show me that ABC is listed under 15/16 through to 18/19 and that DEF is only listed under 15/16, 17/18,18/19 and 19/20. I have quite a few names to go through. I'm thinking that maybe a pivot table is called for but i'm a bit rubbish with them and not sure that it will work for just two categories ie year and name?
No exoert, but: Perhaps if you use the COUNT command for each possible name, in a set oc cells under the column that would tell you what is where. You could set up a formula so you could enter a name in one cell and see the counts for it under each column ?
Although I suspect just glancing down and/or performing a search might best tell you.
Although I suspect just glancing down and/or performing a search might best tell you.
no I am not Mike Girvin....
but
get onto You Tube
access Excelisfun
and that is Mike Girvin site
and start at Magic Trick no 1.....
no
access this
magic trick 157 I THINK also does it
he has an internal search - and put in duplicates
this will front up about a hundred tasks
one of which will fit yours
I used this alot for excel and accounting [which I failed]
but it was deffo make a difference
and I am working froo the Magic Tricks got up to around 550 ( of a few thousand)
have fun !
but
get onto You Tube
access Excelisfun
and that is Mike Girvin site
and start at Magic Trick no 1.....
no
access this
magic trick 157 I THINK also does it
he has an internal search - and put in duplicates
this will front up about a hundred tasks
one of which will fit yours
I used this alot for excel and accounting [which I failed]
but it was deffo make a difference
and I am working froo the Magic Tricks got up to around 550 ( of a few thousand)
have fun !
If you copy and paste all the names in each column into a new column, you can then remove duplicates from that new column using the Remove Duplicates option
If you then use that as the reference column, you can then have a new column for each year and ask if the name in the reference column appears anywhere in the original columns.
Say you have columns A,B,C,D and E as your original information with Column A being your first year.
In columns M,N,O,P,Q and R you have the new information.
Column M has the unique names.
N is the first year, O the second etc.
In N2 use the formula
=COUNTIF(A:A,M2)
In O2 =COUNTIF(B:B,M2)
In P2 =COUNTIF(C:C,M2)
In Q2 =COUNTIF(D:D,M2)
In R2 =COUNTIF(E:E,M2)
Then autofill for all the other rows.
That will then return a "1" If the name is in that year or "0" if it's not.
You can then filter to look for the 1s in the list in each year.
If you then use that as the reference column, you can then have a new column for each year and ask if the name in the reference column appears anywhere in the original columns.
Say you have columns A,B,C,D and E as your original information with Column A being your first year.
In columns M,N,O,P,Q and R you have the new information.
Column M has the unique names.
N is the first year, O the second etc.
In N2 use the formula
=COUNTIF(A:A,M2)
In O2 =COUNTIF(B:B,M2)
In P2 =COUNTIF(C:C,M2)
In Q2 =COUNTIF(D:D,M2)
In R2 =COUNTIF(E:E,M2)
Then autofill for all the other rows.
That will then return a "1" If the name is in that year or "0" if it's not.
You can then filter to look for the 1s in the list in each year.
TTT, my method does it automatically as opposed to checking each name in five columns manually and then finding that name in a list and ticking it off.
PP, I haven't checked your link but it's a method I have used a fair few times before.
As the range is A:A, anything added to that column will be looked at.
There is also the Record Macro function if there are several steps to be undertaken repeatedly.
That could be used to copy columns, paste them and remove duplicates. Excel creates the macro behind the scenes and you can then assign that macro to a "button". Clicking on that "button" then carries out the actions in fractions of a second.
PP, I haven't checked your link but it's a method I have used a fair few times before.
As the range is A:A, anything added to that column will be looked at.
There is also the Record Macro function if there are several steps to be undertaken repeatedly.
That could be used to copy columns, paste them and remove duplicates. Excel creates the macro behind the scenes and you can then assign that macro to a "button". Clicking on that "button" then carries out the actions in fractions of a second.
1 to 17 of 17
