Results 1 to 3 of 3

Thread: List distinct items from column and their total number of occurrences

  1. #1
    Neophyte ragomes's Avatar
    Join Date
    Apr 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 for Enterprise v.2101

    List distinct items from column and their total number of occurrences



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    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.

    Question 1-
    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?
    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. 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?

    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.

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

    Question 2 - Help with Formula
    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:

    (country) | (# days)

    US | 2
    Mexico | 3
    Colombia | 1

    I have attached a sample spreadsheet with the above challenge.
    Please help.
    Thank you very much!
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,981
    Articles
    0
    Excel Version
    365
    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!
    Attached Files Attached Files
    Last edited by p45cal; 2021-04-30 at 10:55 PM.

  3. #3
    Neophyte ragomes's Avatar
    Join Date
    Apr 2021
    Posts
    3
    Articles
    0
    Excel Version
    365 for Enterprise v.2101
    Thank you! This worked.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •