•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
I want to transpose 78 equal columns of data into one long stack with a space in between.
Our take
If you're looking to transpose 78 equal columns of data into one long stack with spaces in between, you're not alone. Many users encounter challenges when trying to automate this process in Google Sheets. While functions like INDEX and OFFSET can be useful, they may not always yield the desired results, especially with larger datasets. Fortunately, there are effective strategies to achieve your goal, whether you stick with Google Sheets or switch to Excel.
I have a set of data that looks like this (78 sets):
I would like it to look like this:
I have tried variations of INDEX and OFFSET but can't seem to get it to work smoothly and automatically for 78 sets of this data.
I am currently using Google Sheets (for ease of portability) but I also have Excel so could switch it across if the functionality is not there in Google.
Any help would be greatly appreciated.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Hot folder data sorting.My goal is to create a hot folder that I am able to continuously drop data sheets into. Then have excel transfer a few specific cells ie.( totals, names )into a uniform row on a new table/sheet. •all data sheets will be formatted the exact same and information will be in the same positions across all of them •data sheets will be added daily •each data sheet needs to create its own respective line representing information from only that sheet •some of the cells needed are a result of total formulas in the data sheet •no information besides the specific cells should reflect in the new table (Photos in comments) P1- example of the data sheets used with the cells circled that are wanted to be transferred. P2- example of what it should somewhat look like when presented in the table/sheet submitted by /u/Notsuspicious444 [link] [comments]
- How to handle data from different sources when columns are in different orders?I regularly get CSV exports from multiple clients. Each client uses their own column order. One puts names in column A and dates in column B, another swaps them. Manually rearranging every time is driving me crazy. What's your go-to method for standardizing columns from different sources? Power Query seems powerful but I'm not sure where to start. I've tried INDEX/MATCH with header lookups, but it gets messy when column names vary slightly. Also open to VBA solutions if they're reusable. Any tips or templates you'd recommend? submitted by /u/biggy_boy17 [link] [comments]
- How to move selection without moving selected data?I'm working with a very large data set (>50,000 values), and need an efficient way to index and create charts from a selected range. What works is CTRL+F for the name of a well (B2 shown here) which then allows me to select all cells named B2. What I want to be able to do now is to move the selection frame over to just select those 724 cells but ONLY in columns D&E. Excel lets me select B through E via Shift+Arrows. How can I only move the selection pane without moving/pasting data over? TIA. https://preview.redd.it/b4vlmr4s4vug1.png?width=2879&format=png&auto=webp&s=6fbe5539eef3ebbe820569a09c95959ab45e71ff https://preview.redd.it/5bii8s4s4vug1.png?width=2874&format=png&auto=webp&s=ec70f1611e884ffc812eabfdc73843e284eb32e1 https://preview.redd.it/glq2ur4s4vug1.png?width=2878&format=png&auto=webp&s=990755d70452fb92246c7e9ed6d1fb5c973e397b submitted by /u/OnewhoSortsNew [link] [comments]
- Reorganizing a table by making the entries in original column as the columns of the new tableI am trying to automatically, or with minimal effort as possible since it will be a routine task, reorganize a data table by making the entries of one column as the columns in the new table. I appreciate any help! Here is an example of what I mean So in the above example, each run becomes a column with the respective compound results. The amount of compounds in each run can vary, and new data sets would need to be processed. Ideally, you could copy and paste a new set below the original data and have it reorganized as well. I've made some attempts with this in the past, such as using xlookup in a separate table for each compound, but its always been clunky. Thanks in advance for the help! Edit: On Office 365 submitted by /u/ConnorF42 [link] [comments]