Hi all,
Been scratching my head & searching for a solution on this. Can't work it out.
I have a table of data representing competitors at an event. Over time the event attracts new entrants, and loses some. I'm trying to compare year on year to see who has come back year on year based on the year prior, and a separate comparison to see who has competed who was there in the first year the event ran. The table looks like the table below. The numbers highlighted in yellow are the results I want.
I did a conditional format for duplicates to find these but I can't do that for the number of sheets I need. I'm looking for a way either VBA or sumproduct type comparison formula that compares the columns and finds the duplicate values.
First row is obviously column compared with column to it's left. Second row compares column with the original entrants in A column.
Any help appreciated.
DJ
Been scratching my head & searching for a solution on this. Can't work it out.
I have a table of data representing competitors at an event. Over time the event attracts new entrants, and loses some. I'm trying to compare year on year to see who has come back year on year based on the year prior, and a separate comparison to see who has competed who was there in the first year the event ran. The table looks like the table below. The numbers highlighted in yellow are the results I want.
Club patronage 2017 - 2022.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | 2017 | 2018 | 2019 | 2020 | 2021 | ||
2 | Dave | Dennis | Jane | Jane | Derek | ||
3 | Jess | Dave | Dave | Aaron | Bill | ||
4 | Bobby | Bobby | Dennis | Darren | Jess | ||
5 | Damien | Jess | Bobby | Adam | Bobby | ||
6 | Liz | Liz | Aaron | Liz | Liz | ||
7 | Darren | Amy | Xavier | Xavier | |||
8 | Adam | ||||||
9 | |||||||
10 | |||||||
11 | Compare | Those that returned from year before | |||||
12 | 2017-18 | 2018-19 | 2019-20 | 2020-21 | |||
13 | 4 | 3 | 2 | 2 | |||
14 | |||||||
15 | Compare | those that returned compared to first year | |||||
16 | 2017-18 | 2017-19 | 2017-20 | 2017-21 | |||
17 | 4 | 2 | 1 | 3 | |||
Sheet11 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A2:A8,E2:E9 | Cell Value | duplicates | text | YES |
I did a conditional format for duplicates to find these but I can't do that for the number of sheets I need. I'm looking for a way either VBA or sumproduct type comparison formula that compares the columns and finds the duplicate values.
First row is obviously column compared with column to it's left. Second row compares column with the original entrants in A column.
Any help appreciated.
DJ