•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to autofill data from a row to a column on a different sheet in the same folder?
Our take
Are you struggling to autofill data from rows to columns in Excel, particularly when working with exported questionnaire results? If you've spent hours trying to find a solution, you're not alone. The challenge lies in efficiently organizing responses from multiple respondents into individual sheets, making them more user-friendly. The goal is to list questions in the first column and corresponding answers in the adjacent columns. If you're seeking a streamlined solution that avoids tedious manual entry, let’s explore how to achieve this effectively.
I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs.
Hoping someone has the solution I'm looking for and is willing to share..
I've exported questionnaire results from Mentimeter to Excel.
The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something.
I would like to make this more user-friendly by:
1) putting each respondent's answers in their own sheet in the folder, and
2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'.
Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3.
The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on.
What I'm looking for would ideally display answers in !AnswerA3 through to A80.
When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want.
When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem.
I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that.
Would anyone be able to provide me with the correct solution for doing this?
I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally....
Thank you Excel wizards! :)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Formatting question for automating data entryIm going to try to articulate what I need and if it’s possible to do inside excel. At my job I have to record the amount of patrons using our facilities. and specify what particular services are being used. at the end of each quarter. (3 month period) I must tally up all the numbers and provide a total for each aspect of our facility as well as the total overall. For example. 1st quarter numbers. 100 patrons used theatre. 250 patrons used Game room 450 patrons used computer lab so on and so forth. Now that you have the gist in your head. Imagine a spreadsheet where the first form is just a data entry sheet. it’s essentially just a box that never changes. You input the numbers for the week, and that data gets automatically moved to a different cell that has the total amount. so that at the end of the quarter I can easily see my total without having to backtrack or tediously add. if anyone has some insight on how I can do this Please reach out. If you have any questions about my wording or understanding exactly what I mean please also reach out. If you read all this I appreciate your time. submitted by /u/Beneficial-Yard-9006 [link] [comments]
- Sync or map data of two automated columns to the filtering systems of other columnsContext: The automated columns are C (Assigned Codes) and D (Positions). These were transferred from one workbook to another, which is this sheet you're seeing. I used the dynamic array filter function because it has to update in real time, as instructed by my manager. Example, my formula for column C: =FILTER([practicing.xlsx]Sheet1!B:B,[practicing.xlsx]Sheet1!B:B<>"",""") Thus, once the source workbook has more data, it can automatically show in this sheet. Reasons for not using alternatives: Power Query - it's not entirely automatic due to the load every x minutes, and the source workbook has to be closed for it to load in the destination workbook. Power Automate - blocked by my company The Problem: Column C and D aren't linked with the filtering systems of Column A (country) and Column B (Leader Assigned). For example, if the country USA is filtered/selected, then its assigned codes and positions should show. The issue is that their country code (starts with "US") and position, IT, are placed in different rows. If the USA is selected, it will only show rows C3-C4 & D3-D4, which is incorrect. https://preview.redd.it/ursej4pq8jxg1.png?width=916&format=png&auto=webp&s=272aa9d905e6d9e5277c70accc67e2614cc03dbd What I'm looking for: My assigned codes and positions already contain formulas (dynamic array filter function), so using another formula for these columns or in one cell can't be done (I suppose). Is there any way to map the C and D columns to the filtering systems for columns A and B? What I tried doing: Advanced filter - it adds a whole new table, but this sadly isn't what I'm looking for with my data. I want to just use the columns that I have now Custom filter - used the text filter -> begins with. It helps with filtering columns C and D for sure, but it doesn't remap the rows, so the data for columns A and B will appear inaccurate. Please let me know if I am also doing something wrong with what I've tried or done. Thank you in advance, and let me know if anything is unclear. This would really mean a lot to me. I am also open to chatting more! :)) submitted by /u/jeankrstein [link] [comments]
- Best way to automate result filtering?Help- I'm just starting to learn about automation within Excel and I'm not sure what I even really need to be asking. The short version is, I need to be able to dynamically sort out blank columns based on filter results for another column. More specifically, one of my job dutirs is pulling and distributing customer satisfaction survey results across our system. Someone along the line decided the best way to do this was with a single, massive survey on Survey Monkey, with location based filters automatically giving the customers site specific questions. What this means for me is that every week I'm using views within the survey monkey analysis screen to pull results individually for each location, then unzipping and naming them, before finally being able to send them out to the various teams. If I pull the entire survey with no views applied, there are a ton of columns, with each location having a certain block of columns containing results. E.g. location a results are in columns d-f, location b results are in columns g-i... Etc. What I would like to do is pull the entire block of results and then use some sort of automation to sort results automatically, taking this from an hours long process down to a matter of minutes. Should I be looking at power query? Macros? Is this functionality built in to an existing tool, like pivot tables or something? Any direction on where I should be going to learn more is appreciated. submitted by /u/Ok_Perception3325 [link] [comments]
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#row zero#data cleaning solutions#financial modeling with spreadsheets#big data management in spreadsheets#conversational data analysis#rows.com#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#AI formula generation techniques#enterprise-level spreadsheet solutions#AI-driven spreadsheet solutions