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!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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