•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Helping a coworker- How to return a row with three criteria in an existing sheet?
Our take
If you're looking to help a coworker extract data from an existing sheet using multiple criteria, you've come to the right place. To return values from specific columns based on item numbers, consider leveraging Power Query (PQ) for an efficient solution. This tool can transform your data, allowing you to populate a new workbook with the required values in a structured manner. By following detailed steps, you can seamlessly return item numbers and quantities, making data management simpler and more effective.
Hey all , I KNOW there's an answer staring me in the face. I played around with PQ but didn't see what I liked.
My coworker has data that looks like this
In a new workbook, she wants to take this above data and return the number in C1 (and D1, and E1, etc.) in column A. But not just pasting vertically, returning it for every line of item#s. So Order A, Order A, Order A, etc.
She also wants the item# returned in column B of the new book. Short of copying and pasting, I don't know a good way to do that.
Finally, once she has those we can do an old fashioned lookup to get the qty to return in Column C, we're good there.
Any thoughts?
And please don't just say "PQ" and leave. If your answer is PQ, please give detailed instructions and advice.
Editing to add output required:
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to autofill data from a row to a column on a different sheet in the same folder?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! :) submitted by /u/Lost_Mud2097 [link] [comments]
- Power Query - Manual Entry and Multiple UsersI have a file with details of open purchase orders. PO numbers are in the rows and there are several columns with various PO details. It is linked via PQ to two other files. It is used by multiple buyers and I'd like to dumb it down as much as possible. I need to be able to do two things that I haven't figured out yet: Each buyer is responsible for updating delivery ETAs on their open purchases. These are updated at different times for a each vendor throughout the week. In the shared file that we previously used, buyers had a sheet for each vendor and we used VLOOKUPS to pull data from these sheets. Obviously, that doesn't work with a PQ chart. Is there a solution to this that I'm not thinking of? I need a column where buyers can manually add notes to each cell. submitted by /u/free-range-human [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]
- Fill out a ranking using data from other pagesHi everyone, I'm not a particularly frequent excel user, and I'm making a silly spreadsheet documenting the reviews my Australian friend is giving to British crisps. Each crisp has a picture on the home page, which when clicked takes you to the individual review of the crisp. She has ranked each crisp with different categories and given it an overall rating. I would love for each "overall rating" to go to a final page on the spreadsheet which shows all the crisps in ranking order. Now, of course I could just go through and type them in, changing them each time she reviews a new crisp, but is there a way excel can take this information with a formula? For example, on each individual crisps page the overall rating is cell D8. If a quaver was rated 8.5, a pombear was rated 6 and a mini cheddar was rated 7, could excel automatically put them in order of quaver, mini cheddar, pombear on my final ranking page? I appreciate this is a very long winded way of getting it across but it's a bit niche and so I wanted to try and make it clear! (It's much easier to just show someone a video of the spreadsheet but I don't think I can do that here!) Thanks!! submitted by /u/puzzled_puzzler12 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#financial modeling with spreadsheets#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#row zero#row#criteria#item#