Formula for Transposing Specific Columns
I am trying to figure out if there is a formula that can transpose specific columns in a Google sheet.
I have created a data entry tab for users to input data "Input (Data Entry)" (screenshot below), the yellow highlighted column names are the fields that need to be transposed in the output data set.
My goal is to have the data input transform into a formatted data set seen in the "Output (Dataset)" (screenshot below). The yellow highlighted column is the transposed column compiled from the columns in the "Input (Data Entry)" tab. Fields highlighted in orange are fields that are imported from the "Input (Data Entry)" tab, fields highlighted in blue are duplicated entries of the orange fields due to transposed values. Do note for Field 5A - Field 5C, these values essentially repeat thus why they are highlighted in orange.
I have attempted to link the sheet and use transpose on the specific columns but problems arises when I try to copy the formula downwards as the reference rows from the input sheet would jump in the formulas due to spacing created by the transposed values. Is there a formula workaround for this?
You can use REDUCE to process those three columns, and stack the retrieved other columns based on the rows of each cell:
=REDUCE({'Input (Data Entry)'!A1:F1,'Input (Data Entry)'!I1:M1},'Input (Data Entry)'!F2:H5,LAMBDA(a,v,
VSTACK(a,HSTACK(INDEX('Input (Data Entry)'!A:E,ROW(v)),v,INDEX('Input (Data Entry)'!I:M,ROW(v))))))
The first part are the headers, you can grab other cells or just type them inside: for example, change {'Input (Data Entry)'!A1:F1,'Input (Data Entry)'!I1:M1} with HSTACK("header 1, "header 2"....., "header 11")
- How can I remove consecutive duplicates from a range in Google Sheets?
- Using FILTER with multiple conditions on Google Sheet
- How to union ranges in google spreadsheets
- Formula for filtering list based off of key with multiple criteria in Google Sheets
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria
- How to use Apps script to move a lot of data rows at same time to different spreadsheet automatically base on time
- How to ignore empty rows when submitting to database
- Custom Menus Google Apps Script
- Google Sheets Index Sidebar of all tabs - add search functionality
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Google script Display array with rows of different sizes
- How to Delete Today's Google Calendar Entries in Google Apps Script
- List rows in array when the cell contain number in the string
- ImportXML XPath issue using Google Sheets on a simple web scraping query
- Return index of cell that contains match on two strings
- Conditional formatting if cell past date and another cell empty with arrayformula
- How to find the Second Space from the right in Google Sheets?
- Joining more than two WHERE statements in Query Language
- Python gspread create sheet if not exist
- Find if cell contents exist in array
- Google Apps Script: Finding an element in a Column and returning the corresponding row
- Active VS Effective User for Google Sheets
- Rearrange data in table format to rows
- I am not able to extract URL from these cell values using Google Apps Script
- How to count a specific word from a string in a cell from the entire column in google sheets
- Error returned by action script when calling an API with UrlFetchApp.fetch
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Replace duplicate row with new data in Google Sheets using Apps Script