Normalize Google Sheet table (1st step)
I want to convert following table in Google Sheet:
| Process | Tool A | Tool B |
|---|---|---|
| Process 1 | Tool 1 | Tool 2 |
| Process 2 | Tool 1 | |
| Process 3 | Tool 2 | Tool 3 |
into following table:
| Process | Tool A | Tool B |
|---|---|---|
| Process 1 | Tool 1 | |
| Process 1 | Tool 2 | |
| Process 2 | Tool 1 | |
| Process 3 | Tool 2 | |
| Process 3 | Tool 3 |
I understand that there are various options (Google Sheets QUERY() function, etc), but I am unfortunately stuck. I will highly appreciate any advise on this issue.
Solution
You may try:
=reduce(A1:B1,B2:C,lambda(a,c,vstack(a,if(c="",tocol(,1),{index(A:A,row(c)),c}))))
- Combine overlapping date ranges
- How to use values from CTE in ON CONFLICT UPDATE
- pyspark statistical window function keeps calculating NULL values
- sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible'
- Sum a record with its previous not-null ordered by timestamp
- Prevent redundant results in SQL query
- SQL Query in Snowflake - Calculate Days of Supply
- How can I find out what FOREIGN KEY constraint references a table in SQL Server?
- How to extract column names from SQL query using Python
- Select on self-referencing table where all chains meet a condition
- SQL query to check if a name begins and ends with a vowel
- In a join, fields with null values are not getting shown but in normal query they are
- MySQL JSON_OBJECT() datetime format
- How to find overlapping records and select the latest record?
- SQL query for returning rows with only certain values in list but not others?
- Why does my query fail to convert a varchar value to int?
- How do I insert a record into a Postgres DB based off the MAX of one column?
- How to auto insert Current DATE in SQL with Java / Hibernate
- IsDate Function in SQL evaluates invalid dates as valid
- SQL Server - Value passes ISDATE() but fails to CAST as DATE or DATETIME
- Snowflake Regex to Camel case file name
- SQL Server DATEDIFF round up the YEAR difference. How to round it down?
- Path table to HTML treeview in SQL Server
- SUM and Decimal precision
- Choosing simple products and variable products separately in WooCommerce tables
- collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"
- Separating fields in the same column
- SQL filter for same values in different columns
- Add multiple conditional values to one column
- How do I repeat a SQL query over a set of records?