Good morning,
I have a table over just two columns.
Column A is the list of years
Column B is the list of champions names
Some champions have won several years.
As an idea for a Dashboard item, I would like to extract a concatenated list showing the name and list of years they were champion.
Example:
Fred Smith: 1967, 1968, 1970
How would I do this please?
Thanks
Do you want Fred Smith and the years in one cell, or in two? (easier with 2...)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Glenn, two is what I had in mind.Originally Posted by Glenn Kennedy
1. named Range (CTRL F3 to view) to make one of the formulae shorter - called "List".
2.To return list of winner names (alphabetically), an array formula:
=IFERROR(INDEX(List, MATCH(MIN(IF(COUNTIF($D$1:D1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0)),"")
3. VBA code to concatenate an unknown number of values:
3. Array formula using the code to return the years, comma-space separated:
Please Login or Register to view this content.
=ConcatAll(IF(List=D2,$A$2:$A$20,""),", ")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Many thanks Glenn.
I'm aware of what to do with Array formulae.
This is VERY close to what I want to do.
What in the formula in column D, that lists the names, makes them appear in alphabetical order?
I'd quite like them to appear in the order in which they became champions.
Many thanks again.
Neil
Last edited by AliGW; 07-24-2017 at 07:27 AM. Reason: Unnecessary quotation removed.
Ambiguous request... ordered by the most recent year they became champions, or by the first???
So. My list in Column A is in the opposite order to yours, such that 1999 would be at the top and 2017 at the bottom
Thus, the concatenated list is correct when applying your formula.
It is the alphabetical list of Champions that I would like to list in the order that they became champions first.
In your example it would be
Fred Bloggs
Jeremy Corbyn
Darth Vader
etc.
Hope that makes sense.
Piggy backing on Glenn Kennedy's solution see if this does what you want.
Put a helper column in C.In D2 and filled down.Formula:
Please Login or Register to view this content.Then array enter in E2 and fill down.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Did this help?
Last edited by FlameRetired; 07-23-2017 at 10:28 PM.
Dave
Dave,
This is just what I wanted to do.
Many thanks for the assistance on this final part, and of course, many thanks to Glenn for the initial concept.
Neil
Dave,
I spoke too soon. Whilst this works well on Glenn's example, his column 'A' is in reverse year order to mine. IE, mine is
1950
1951
etc. etc.
I found I needed to change the formula in column D from 'LARGE' to 'SMALL' in the 'MATCH' part of the formula.
However, the formula in column C seems to read upward looking at duplicate names, so the list is out of order, finding the last instance of the name instead of the first.
I can't figure how to change that formula to reverse this.
Many thanks.
Last edited by AliGW; 07-24-2017 at 08:05 AM. Reason: Unnecessary quotation removed.
Neil - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
OK, sorry.
I took out the table, but thought it necessary to keep in the references to the formulae to make it more obvious to anyone reading what I was referencing.
Just to confirm, using Dave's 3 formulae, with this data:
1950 Fred Smith
1951 Bob Hope
1952 Brian May
1953 Brian May
1954 Bob Hope
1955 Bob Hope
I get the result
1 Fred Smith 1950
FALSE Brian May 1952, 1953
FALSE Bob Hope 1951, 1954, 1955
...where Bob Hope should come above Brian May
Thanks.
I'm a tad confused... with a modification to the array in D2, I think this is it:
=IFERROR(INDEX($B$2:$B$20,MATCH(0,INDEX(IF(SMALL($A$2:$A$20,ROWS($1:1)),COUNTIF($D$1:$D1,$B$2:$B$20)),0),0)),"")
Glenn,
That's exactly it. Many thanks for the modification.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks