Pulling list of Unique Names from Refresh-able Table between Date Range (Or Month Number)

SirGruffles

New Member
Joined
Jul 23, 2018
Messages
16
Hello! Thanks for any help in advance:

I'm attempting to create a "dynamic invoice" of sorts, with names being pulled from a "refresh-able" table.
By that term, I mean an excel table that (I'm assuming) is pulling from an SQL query and just needs the "refresh all" button on the Data tab to be hit.

Here is an example of my table, with the headers in col A row 1, continuing down to K row 5 at the bottom:
areaArea_nameEmpIDEmp_NameOperationHru_UsedLab_TypePrdateMonthWorkCellDefaultArea
27​
Shipping
1002​
Patrick PentagonShipping Amazon Orders
8​
Direct
5/1/2020 0:00​
5Doesn't Matter
15​
Shipping
1001​
Spongebob CirclepantsShipping Amazon Orders
8​
Direct
5/5/2020 0:00​
5Doesn't Matter
12​
Repairs
1003​
Eugene LobsterManaging
8​
Direct
4/30/2020 0:00​
4Doesn't Matter
15​
Shipping
1001​
Spongebob CirclepantsShipping Amazon Orders
8​
Direct
5/6/2020 0:00​
5Doesn't Matter

The goal is to create a formula that will pull all of the *unique* names from a list that meet the following criteria:
1) Must be under the "Shipping" Area_Name
2) Must have a Prdate between 05/01/2020 - 05/31/2020 OR Must have a Month value = 5,
3) Can't have repeat names, IE the *unique* part

The reason for this need to craft an "automated" datasheet for some of my operations leaders in order to verify billing hours within a month.
I also want to make it as.... accessible as possible for the less....tech-savvy. Being able to tell them to simply "Refresh All" would be a great benefit.

At the end of this basic example, the results should show:
Patrick Pentagon
Spongebob Circlepants

Please let me know if I'm missing any important points.

Thanks for your help!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try this array formula.
(I hid some columns)

Dante Amor
ABDIL
1areaArea_nameEmp_NameMonthResult
227ShippingPatrick Pentagon5Patrick Pentagon
315ShippingSpongebob Circlepants5Spongebob Circlepants
412RepairsEugene Lobster4 
515ShippingSpongebob Circlepants5 
Hoja3
Cell Formulas
RangeFormula
L2:L5L2=IFERROR(INDEX($D$2:$D$6,MATCH(0,IF(($B$2:$B$6="Shipping")*($I$2:$I$6=5),COUNTIF(L$1:L1,$D$2:$D$6),""),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,478
Messages
5,636,559
Members
416,923
Latest member
jarri

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