Group By - formula help & Power Query (Google Sheets former user noob in Excel)

%3CLINGO-SUB%20id%3D%22lingo-sub-2303618%22%20slang%3D%22en-US%22%3EGroup%20By%20-%20formula%20help%20%26amp%3B%20Power%20Query%20(Google%20Sheets%20former%20user%20noob%20in%20Excel)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2303618%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EMy%20primary%20workbook%20has%20a%20tab%20dedicated%20to%20listing%20holidays%20from%20our%20team%20resources%20from%20several%20countries.%20As%20this%20list%20comes%20from%20a%20separate%20workbook%2C%20I%20used%20the%20Power%20Query%20import%20data%20option%20to%20bring%20the%20data%20into%20my%20primary%20workbook.%3C%2FP%3E%3CP%3E%3CSTRONG%3EQuestion%201-%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIs%20that%20the%20best%20way%20to%20do%20it%20in%20Excel%20assuming%20I%20want%20to%20achieve%20something%20similar%20to%20what%20Google%20Sheets%20has%20named%20as%20%22importrange%22%20so%20that%20the%20data%20always%20syncs%20with%20the%20source%20from%20where%20it's%20pulling%20from%20automatically%3F%3C%2FP%3E%3CP%3EI%20noticed%20you%20can%20also%20use%20the%20Copy%2FPaste%20Link%20functionality%20to%20try%20and%20sync%20your%20data%20with%20the%20source%20workbook%2C%20but%20I'm%20not%20sure%20this%20is%20the%20best%20way%20to%20do%20so.%3C%2FP%3E%3CP%3EAlso%2C%20does%20it%20make%20any%20difference%20once%20I%20move%20the%20two%20files%20to%20SharePoint%20to%20share%20with%20other%20people%20within%20the%20company%3F%20Will%20the%20Power%20Query%20continue%20to%20work%20on%20SharePoint%20or%20should%20I%20use%20the%20Copy%2FPaste%20Link%20functionality%20instead%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyways%2C%20as%20mentioned%20before%20I%20used%20Power%20Query%20and%20imported%20the%20holidays%20data%20from%20the%20source%20into%20my%20workbook%2C%20and%20that%20table%20now%20is%20loaded%20as%20a%20Power%20Query%20Table.%3C%2FP%3E%3CP%3EThe%20table%20has%20the%20following%20columns%20under%20the%20Holidays%20tab%3A%3C%2FP%3E%3CP%3EDate%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Holiday%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCountry%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EQuestion%202%20-%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20on%20another%20tab%20which%2C%20based%20on%20a%20start%20and%20end%20date%20determined%20by%20two%20distinct%20cells%2C%20has%20to%20find%20all%20holidays%20that%20fit%20between%20the%20two%20dates%20from%20the%20Holidays%20tab%20and%20group%20them%20by%20country%20in%20such%20a%20way%20that%20the%20output%20of%20the%20formula%20must%20display%20the%20number%20of%20total%20holidays%20per%20distinct%20country%20that%20fall%20in%20between%20the%20start%20%26amp%3B%20end%20dates%20in%20each%20row%20as%20follows%3A%3C%2FP%3E%3CP%3E(country)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20(%23%20days)%3C%2FP%3E%3CP%3EUS%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%202%3C%2FP%3E%3CP%3EMexico%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%203%3C%2FP%3E%3CP%3EColombia%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20the%20results%20above%20must%20be%20output%20in%20two%20separate%20columns%20-%20one%20for%20the%20country%20and%20the%20other%20for%20the%20total%20%23%20of%20holidays%20that%20fall%20between%20the%20date%20interval%20provided.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20reference%2C%20the%20formula%20I%20had%20on%20Google%20Sheets%20was%20using%20the%20Query%20Formula%20and%20looked%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIFERROR%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Equery%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3E'Holidays'!B3%3AD%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20string%20%22%3E%22select%20D%2C%20count(D)%20where%20D%20is%20not%20null%20and%20date%20'%22%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26amp%3B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Etext%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EC9%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20string%20%22%3E%22yyyy-mm-dd%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26amp%3B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20string%20%22%3E%22'%20%26lt%3B%20toDate(B)%20and%20toDate(B)%20%26lt%3B%20date%20'%22%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26amp%3B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Etext%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EC10%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20string%20%22%3E%22yyyy-mm-dd%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26amp%3B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%20string%20%22%3E%22'%20group%20by%20D%20label%20count(D)%20''%20%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22number%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22None%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%203%20-%3C%2FP%3E%3CP%3EIf%20I%20didn't%20want%20to%20use%20Power%20Query%2C%20what%20would%20be%20a%20formula%20in%20Excel%20that%20can%20provide%20me%20with%20the%20same%20output%20demonstrated%20above%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20pls%20see%20sample%20Excel%20file%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2303618%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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 do it in Excel assuming I want to achieve something similar to what Google Sheets has named as "importrange" so that the data always syncs with the source from where it's pulling from automatically?

I noticed you can also use the Copy/Paste Link functionality to try and sync your data with the source workbook, but I'm not sure this is the best way to do so.

Also, does it 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:

Date               |      Holiday                     |     Country

 

Question 2 -

I have a formula on another tab which, based on a start and end date determined by two distinct cells, has to find all holidays that fit 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 distinct country that fall in between the start & end dates in each row as follows:

(country)         |  (# days)

US                   |    2

Mexico            |    3

Colombia        |    1

 

Note the results above must be output in two separate columns - one for the country and the other for the total # of holidays that fall between the date interval provided.

 

As a reference, the formula I had on Google Sheets was using the Query Formula and looked like this:

=IFERROR(query('Holidays'!B3:D, "select D, count(D) where D is not null and date '" & text(C9, "yyyy-mm-dd") & "' < toDate(B) and toDate(B) < date '" & text(C10, "yyyy-mm-dd") & "' group by D label count(D) '' ", 0),"None")

 

Question 3 -

If I didn't want to use Power Query, what would be a formula in Excel that can provide me with the same output demonstrated above?

 

P.S. pls see sample Excel file attached

 

Please help.

Thank you very much!

 

Thank you!

0 Replies