обновлена функция ReversePivotTable, поэтому я могу указать количество столбцов и строк заголовка
Sub ReversePivotTable()
' Before running this, make sure you have a summary table with column headers.
' The output table will have three columns.
Dim SummaryTable As Range, OutputRange As Range
Dim OutRow As Long
Dim r As Long, c As Long
Dim lngHeaderColumns As Long, lngHeaderRows As Long, lngHeaderLoop As Long
On Error Resume Next
Set SummaryTable = ActiveCell.CurrentRegion
If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
MsgBox "Select a cell within the summary table.", vbCritical
Exit Sub
End If
SummaryTable.Select
Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
lngHeaderColumns = Application.InputBox(prompt:="Header Columns")
lngHeaderRows = Application.InputBox(prompt:="Header Rows")
' Convert the range
OutRow = 2
Application.ScreenUpdating = False
'OutputRange.Range("A1:D3") = Array("Column1", "Column2", "Column3", "Column4")
For r = lngHeaderRows + 1 To SummaryTable.Rows.Count
For c = lngHeaderColumns + 1 To SummaryTable.Columns.Count
' loop through all header columns and add to output
For lngHeaderLoop = 1 To lngHeaderColumns
OutputRange.Cells(OutRow, lngHeaderLoop) = SummaryTable.Cells(r, lngHeaderLoop)
Next lngHeaderLoop
' loop through all header rows and add to output
For lngHeaderLoop = 1 To lngHeaderRows
OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderLoop) = SummaryTable.Cells(lngHeaderLoop, c)
Next lngHeaderLoop
OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1) = SummaryTable.Cells(r, c)
OutputRange.Cells(OutRow, lngHeaderColumns + lngHeaderRows + 1).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
OutRow = OutRow + 1
Next c
Next r
End Sub
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'url.com/id=userIDmadeUPofletterandnumbers&em=MemberType' urlValue UNION ALL
SELECT 'url.com/id=asd1221231sf&em=studentMember'
)
SELECT REGEXP_EXTRACT(urlValue, r'id=(\w+)') id, urlValue
FROM `project.dataset.table`
Row id urlValue
1 userIDmadeUPofletterandnumbers url.com/id=userIDmadeUPofletterandnumbers&em=MemberType
2 asd1221231sf url.com/id=asd1221231sf&em=studentMember
r
в начало Regex заставляет его работать - я не вижу ссылку на него в документации – Mobile Bloke 15 July 2018 в 00:36raw strings
, вы можете проверить их здесь – Mikhail Berlyant 15 July 2018 в 04:31