Returning An Array Based on Conditions

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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,273
Messages
5,836,329
Members
430,421
Latest member
Natas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top