Oct 14, 2021 · Thank you for the advise, however the aggregate options you've provided don't work for me / don't perform the expected operation >> what I got is 2 sheets - one with 3 columns which are populated manually, another one shall only accumulate data from the first sheet into its own 3 columns (e.g. after every refresh of the query).

Mar 19, 2024 · I have data like this: Columns A and B are my data. I want to aggregate the data in columns A and B for every integer value in A by averaging the values in column B. So for every value in A that starts with a 17 I want an average of the 4 corresponding values in B and so on. Here is what I have tried.

Jul 1, 2013 · Want to try this a different way. I took the data/responses from each eval and put them into a single spreadsheet. Then I created a master spreadsheet call aggregate evals (see the image). Now I want to take all of the data from the subsequent evals and carry it over into the corresponding cells on my aggregate spreadsheet.

Apr 4, 2015 · With your sample data on the active worksheet starting with Id in A1, tap Alt+F8 to open the Macros dialog and Run the macro. Results from concatenate_and_transpose_to_delim_string: Results from concatenate_and_transpose_into_columns:

Sep 17, 2012 · Try SUMIF formula and hide your original data sheet. If you need it really hidden you can protect the workbook with a password so users can't unhide, or at very least you can use VBA against the sheets Visible property, by setting it to xlVeryHidden .

Sep 25, 2015 · To do it with the GUI: select the table -> power query -> excel data -> from table -> select the column 'region' -> transform -> pivot column -> values column: mytext -> advanced options: don't aggregate. If you change the original source and right click refresh on the output table, the output gets refreshed. –

May 3, 2018 · Consider running SQL directly on the Excel workbook if using Excel for PC since it can connect to the Jet/ACE Engine (Windows .dll files). Then, run the well-known and used aggregate query. Below assumes you have a worksheet named OUTPUT. Be sure to adjust strSQL for actual Criteria and Col# names and SheetName.

Aug 23, 2019 · I am doing quotations in Excel for my company and I am trying to find a way to do 2 things: aggregate several quantities of the same product based on its reference number; return several values for the same references (i.e. different prices) I was thinking about using VLOOKUP but I cannot find a way to make it sum or return multiple values.

Apr 18, 2011 · More generally, this is a problem I encounter a lot. I use SQL for a lot of data analysis, and use aggregate functions frequently. And yet, if I have my data in a spreadsheet rather than in a database table, I find it extremely difficult to do this type of analysis (without needing to clutter my spreadsheet with masses of working areas).

Jan 14, 2021 · I prepared a survey and recipients had to fill-out an excel file. I have now a hundred of excel files with the very same structure, but different entries. For instance, for recipient_b we have: Each excel file is named after the recipient. For instance: recipient_a_survey.xlsx; recipient_b_survey.xlsx, etc. I would like to obtain a dataframe ...