jaykilleen

(Haystack as text, Needle as text) as number =>
List.Count(Text.Split(Haystack, Needle)) - 1

(Rec as record, Lambda as function) as record =>
let
    Keys = Record.FieldNames(Rec),
    Values = Record.FieldValues(Rec),
    Renamed = List.Transform(Keys, each Lambda(_, Record.Field(Rec, _))),
    Recorded = Record.FromList(Values, Renamed),
    Return = Recorded

jaykilleen

(Haystack as text, Needle as text) as number =>
List.Count(Text.Split(Haystack, Needle)) - 1

jaykilleen

/*
Taken and cribbed from blog.datalicious.com/free-download-all-australian-postcodes-geocod

May contain errors where latitude and longitude are off. Use at own non-validated risk.
*/

let
  Source =  Table.FromList(
    {

jaykilleen

Option Explicit

'Call this method from your main VBA module using 'Call init' or 'Call init.init' (if placed in a module called 'init')

'Declare the activeworkbook and any other related workbooks required
Public wb_1 As Workbook 'this is your activeworkbook where this VBA will be hosted
Public wb_2 As Workbook

'Delcare the worksheets that you will be interactive with
Public ws_1 As Worksheet

jaykilleen

Public ws_unique As Worksheet
Public ws_copy_to As Worksheet
Public ws_table1 As Worksheet
  
Public lr_table1 As Long
Public lr_unique As Long

Sub init()
  Set ws_unique = Sheets("unique")
  Set ws_copy_to = Sheets("copy_to")

jaykilleen

'copy the content of this gist into a new module in VBA (get to VBA by pressing Alt+F11)
'you then have access to the functions below from anywhere else in your workbook VBA scripts
'these scrips can be found at Gist on Github at https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0

Option Explicit

Function get_last_row(sheetname As String, column_number As Integer) As Long

  get_last_row = Sheets(sheetname).Cells(Rows.Count, column_number).End(xlUp).row

jaykilleen

'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `find_in_array("dummy text", some_array)
'Where some_array might be something like ["camp","light bulb", "dummy text")
'This function will return TRUE if it finds the value in the array
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module

Function find_in_array(FindMe As String, myArray() As String)

  Dim C As String

jaykilleen

'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `get_row_number("dummy text", 1)
'The integer '1' will tell this to do a ctrl+f on column A
'When it finds the first value it will return the row number of that value
'Best used on columns that contain unique values
'You might need to change the xlPart to xlWhole if you are going for an exact match
'You could also extend this function to pass in a boolean to look for whole or partial value and replace xlPart with the value passed in
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module.

jaykilleen

'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `get_column_number("header column name", 15, TRUE)
'This will look in row 1 (usually where your headers are on a table) for a value and return the column number
'If your headers start on a different row (ie you have slicers above your table) you can pass in the row number
`This will match a partial word by default, pass in TRUE if you want to match by the whole word
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module.

Function get_column_number(sheet As Worksheet, value As String, Optional row As Variant, Optional whole As Boolean) As Integer
 

jaykilleen

'Just copy the function below into a new module
'I usually call this module 'functions'
'You can then call anywhere in your scripts `get_last_row(ThisWorkbook.Sheets("sheet_1"), 1)
'It will then find you the last row of column A
`Change the interger for the second argument to use any other column
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module.

Option Explicit

Function get_last_row(sheetname As String, column_number As Integer) As Long