Excel Formula

[auzzie]

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.

[Zacs-Master]

The filter tool (looks like a funnel) allows you to search by..... Well, anything.

[ToraToraTora]

click the top line of the column you want click the "sort/filter" button then select filter. A little drop down arrow should appear on the column concerned, click that and all the values in that column will be displayed, you can select or not any value.

[auzzie]

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?

[Old_Geezer]

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.

[The Builder]

Auzzie, I think you're going to have to learn how to write a Macro in Excel.

It's a learning curve, but you don't need to be a coder. It's pretty straightforward.

[Canary42]

I'm not absolutely clear on your requirement, but I think that if you use the names as the other axis then use 1 or 0 in the cells as appropriate you could achieve what you want by using formulae.

[Canary42]

.... or Macros as The Builder suggests

[Peter Pedant]

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 !

[auzzie]

Thank you all. Looks like I'm going to be doing a bit of Googling on macro's!! Wish me luck.......

[ToraToraTora]

ok then you need a better way to display the data. Put the names down the left and then have a column for each date but mark with / or X or what ever characters appeal to you, so if you are looking for the positives use Y for Yes and . for no, so the Ys stand out.

[ToraToraTora]

you won't need a macro if you follow the above.

[THECORBYLOON]

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.

[ToraToraTora]

that's a very complex way of doing what I said at 13:22

[Peter Pedant]

refreshing read
makes a change from the daily booll-ucks about coronavirus - bring back the good olde days of Brexit

[Peter Pedant]

hey corby
I think Mike Girvin has a solution like that

are you using a dynamically expanding range? it looks like it - high powered stuff etc

[ToraToraTora]

do something like this
https://ibb.co/svf9dX9

[THECORBYLOON]

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.