energman58
Well-known Member
- Joined
- Oct 25, 2010
- Messages
- 553
Hello
I have a spreadsheet which tracks people against trips with names down the left side and the upper 3 rows contain places - which are repeated more or less randomly
In the body of the table each cell is either blank (if the person did not make that trip) or contains a number 1,2 or 3 - if it is 1 that person only visited the place in row 1 of that column on that day, if it is 2 the places in rows 1 & 2 and if 3 all 3. What I am trying to do is develop a dashboard showing who has visited the largest number of different places in the last year.
So what I want to do is create an array for each person containing all the places they visited - from that I can easily extract the number of unique visits and from that make a league table. But how to do that in a formula? I am trying to avoid a VBA solution which I can build if necessary.
I have spent a day racking my brain - I can get functions to return arrays so i am sure it is possible but I cant get my head around how to do this!
Many thanks!
I have a spreadsheet which tracks people against trips with names down the left side and the upper 3 rows contain places - which are repeated more or less randomly
In the body of the table each cell is either blank (if the person did not make that trip) or contains a number 1,2 or 3 - if it is 1 that person only visited the place in row 1 of that column on that day, if it is 2 the places in rows 1 & 2 and if 3 all 3. What I am trying to do is develop a dashboard showing who has visited the largest number of different places in the last year.
So what I want to do is create an array for each person containing all the places they visited - from that I can easily extract the number of unique visits and from that make a league table. But how to do that in a formula? I am trying to avoid a VBA solution which I can build if necessary.
I have spent a day racking my brain - I can get functions to return arrays so i am sure it is possible but I cant get my head around how to do this!
Many thanks!
