Forum Discussion

BNB2022's avatar
BNB2022
Copper Contributor
Dec 17, 2022

How to reference data by a name and a date

Hello,

          My organization sends out a ton of reporting to track various things, but none of it is centralized. I have a spreadsheet that collects a lot of this reporting and centralizes it. At present, getting the information is clunky for portions of it because you have to modify large pivot tables and then save Daily and MTD versions of the document to get it to correctly populate.

 

I'm trying to eliminate steps to make it more approachable to other users, and I'm stuck with how to get the Workbook to itemize certain information.

 

In the attached screenshot, I have labeled what the Source and Output is trying to achieve. Basically, each user has a Team List that is specific to them. I am trying to get this report to search pull information by name and date, eliminating the need to remove numerous dropdowns from the pivot table and then sort it down to yourself and the date and save duplicates, etc etc.

 

Currently it uses Vlookup, but I can't find a way to get it to look at the name and then the date to populate the information.

 

How do I get the Output table to display as shown below by referencing the Source table and Team list Table and Date?

 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 18, 2022

    BNB2022 

     

    Another alternative (attached) with Power Pivot after:
    - Filling the Source [Name] column with Power Query
    - Establishing a relationship between TeamList & Source tables

     

     

    Any in case what will always cause you problem(s) is the missing info. in the [Name] column of your Source table...

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 17, 2022

    BNB2022 

    An alternative could be Power Query. In the attached file you can enter data in the three blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse. Then select refresh.

     

    The layout of the tables in the screenshot is for illustration. You can drag the small blue dynamic tables to columns M and P for example. Then you can add thousands of rows to the large blue dynamic table.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 17, 2022

    Hi BNB2022 

     

    With 365:

    in I4:

    =LET(
  SourceCols, SEQUENCE(,COLUMNS(Source)),
  Names,      SCAN("",Source[Name],
    LAMBDA(seed,name, IF(ISBLANK(name),seed,name))
  ),
  TeamNames,  SORT(TeamList[Name]),
  Values,     REDUCE(0,TeamNames,
    LAMBDA(seed,name,
      VSTACK(seed,FILTER(CHOOSECOLS(Source, DROP(SourceCols,,2)), (Names=name)*(Source[Date]=TargetDate),0))
    )
  ),
  IFNA(VSTACK(FILTER(Source[#Headers], SourceCols<>2), HSTACK(TeamNames,DROP(Values,1))),0)
)

     

     

Resources