List distinct items from column and their total number of occurrences

ragomes

New member
Joined
Apr 29, 2021
Messages
3
Reaction score
0
Points
0
Excel Version(s)
365 for Enterprise v.2101
[FONT=&quot]Hi,[/FONT]
[FONT=&quot]My primary workbook has a tab dedicated to listing holidays from our team resources from several countries. As this list comes from a separate workbook, I used the Power Query import data option to bring the data into my primary workbook.[/FONT]

[FONT=&quot]Question 1-[/FONT]
[FONT=&quot]Is that the best way to sync data from an external workbook in Excel assuming I want to achieve something similar to what Google Sheets has named as "importrange" so that the data always syncs from source to destination workbook automatically?[/FONT]
[FONT=&quot]I noticed another way to sync data between two workbooks would be using the Copy/Paste Link functionality but I'm not sure this is the best way to do so. [/FONT][FONT=&quot]Also, using or the other methods above make any difference once I move the two files to SharePoint to share with other people within the company? Will the Power Query continue to work on SharePoint or should I use the Copy/Paste Link functionality instead?[/FONT]

[FONT=&quot]Anyways, as mentioned before I used Power Query and imported the holidays data from the source into my workbook, and that table now is loaded as a Power Query Table.[/FONT]

[FONT=&quot]The table has the following columns under the Holidays tab:[/FONT]
[FONT=&quot][column A] | [column B] | [column C][/FONT]
[FONT=&quot]Date | Holiday | Country[/FONT]

[FONT=&quot]Question 2 - Help with Formula[/FONT]
[FONT=&quot]I have a formula on another tab which, based on a start and end dates determined by two distinct cells, has to find all holidays that fall between the two dates (from the Holidays tab) and group them by country in such a way that the output of the formula must display the number of total holidays per country for the specific date range in each row as follows:[/FONT]

[FONT=&quot](country) | (# days)[/FONT]

[FONT=&quot]US | 2[/FONT]
[FONT=&quot]Mexico | 3[/FONT]
[FONT=&quot]Colombia | 1[/FONT]

I have attached a sample spreadsheet with the above challenge.
[FONT=&quot]Please help.[/FONT]
[FONT=&quot]Thank you very much![/FONT]
 

Attachments

  • Help_holidays _2.xlsx
    24.5 KB · Views: 9
In the attached, pivot table at cell A18 of the Formula-TTL Holidays p Country sheet, based on a further Power Query of your Prod_Squads_Teams_Holidays table on the Holidays sheet (you could extend your current Power Query query instead).
In case there are duplicate dates for the same country, I've added the data to the Data Model so I can use the Distinct Count that that affords, but instead of doing that I could have removed any duplicates in the Power Query and not bothered with the Data Model. [edit post posting: I note there aren't any duplicate dates per country anyway]
There's a timeline that will allow you to select the date range that you want to show in the pivot table.

This implies a response to your Q1 that you may as well continue with Power Query to grab the data in the first place!
 

Attachments

  • ExcelGuru11146Help_holidays _2.xlsx
    126.6 KB · Views: 4
Last edited:
Thank you! This worked.
 
Back
Top