I have data that has 2 columns....
A:A will be Names
B:B will be Year
I have a lot of data and some of it is repeated (it is race data that has multiple races per year for each athlete)
What I am looking to do is create a formula that will pull only 1 unique match of C1 and C2 and list in separate columns....
In my example column A,B is the actual data column I,J is what I need it to look like and column O,P is where I want the formulas to go to make the unique list.
Why not a pivot table?
cnak your sample shows a consistent 1 to 1 relationship names to years. Each athlete ran 4 races all in the same years.
Is this representative of the real data?
If you go the formula route it will likely make a difference if it is not.
Dave
not all the athletes will actually run in every race. I just simplified the data to a smaller data set.
Generally in each race there will be 30-40 athletes and apron 7 races per season these numbers will vary depending on the race. the majority will do all the races but there will be some that only do a few (or one). Some athletes may race only have one season...
hope this makes sense?
Then your sample is not representative of the real problem.
Please take the time to post an additional workbook that more closely represents what you are working with along with expected outputs. If it is as varied as that then formula may not be a reasonable approach and best to do the pivot table.
O2=IFERROR(INDEX(A$2:A$43,SMALL(IF(FREQUENCY(IF($A$2:$A$43&$B$2:$B$43<>"",MATCH($A$2:$A$43&$B$2:$B$43,$A$2:$A$43&$B$2:$B$43,0)),ROW(A$2:A$43)-ROW(A$2)+1),ROW(A$2:A$43)-ROW(A$2)+1),ROWS($A$2:A2))),"")
control+shift+enter
Copy across and down
why is Mike not on your list?
Last edited by CARACALLA; 09-07-2019 at 05:47 PM.
Formulas similar in thought to CARACALLA's only I took the unique groupings to be literal.
The attached uses three helper columns.
- in column L paste the list of names and applied 'Remove duplicates'
- in M2 and filled down this array formula (commit with Ctrl + Shift + Enter) to yield the unique counts of years for each athlete.
Formula:
Please Login or Register to view this content.- in N2 the cumulative count of the unique year counts.
Formula:Please Login or Register to view this content.
This in O2 and filled down to get unique grouping of each athleteand this array entered formula (CSE) in P2 and filled down yields the unique years for each athleteFormula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks