How to get a list of Distinct values from a Table that is itself the output form a query (variable length)

Reshaw

Board Regular
Joined
Mar 2, 2011
Messages
53
Apologies for the convoluted title but I couldn't think of a simpler way to put it without losing some fo the problem.

Background:
I have a sheet that populates two tables by running queries on an SQL database we operate from. It contains information relating to moving goods around a warehouse and we derive performance statistics from it.

The user enters a date into a certain cell and the query runs to fetch all the movement transactions for the day and night shifts working that day (theres a bit of manipulation to accomodate shift start times as well) ultimately it returns two tables that cover 12 hours worth of transactions each.

Depending on a large variety of factors, the number of rows returned varies massively from day to day. The inbuilt excel Table functionality copes with this very well.


Now to the problem:

I want to now do some fairly rudimentary counts and sums to generate the performance figures for each shift. The first thing I want to do is to work out who was working on that shift, a list of distinct values in the column storing the operative who generated the record. The best way I've found so far is via this formula:-

"=IFERROR(INDEX($B$6:$B$10,MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),0)),"") " - courtesy of myonlinetraininghub.com

This works very well for a normal data but doesn't seem to handle tables well, I get a zero output to the cell despite the Fx tool showing me the first name in the list. When I simply set the 'data' ranges to the fixed locations on the sheet I still get the zero's output. If I set the data ranges to the full column (e.g. N:N) it includes lots of blank rows which stop this formula from working as it cannot handle blank spaces.

My attempt at table notation in the above formula:-
=IFERROR(INDEX(Day_Shift_Stock_Movements_Filtered_by_Date[trn_user_full_name],MATCH(0,COUNTIF($G$4:G4,Day_Shift_Stock_Movements_Filtered_by_Date[trn_user_full_name]),0)),"")

Given the variable lengths of the tables and this formula's inability to handle blanks I am looking for a way to either modify this formula to work with veariable length tables or else an alternative method altogether.

Does anyone have any ideas how this might be achieved?

Thanks in Advance
Rob
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Forum statistics

Threads
1,144,522
Messages
5,724,830
Members
422,581
Latest member
samiak

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