•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Pull in data from another sheet based on an individual without crashing Excel.
Our take
Are you tired of Excel freezing when pulling in data for individuals with numerous invoices? This common frustration can hinder your productivity and stall your projects. Fortunately, there are efficient solutions that leverage AI-native technology to streamline your data management without crashing your system. By exploring alternatives like INDEX/MATCH instead of traditional VLOOKUP, you can enhance performance and ensure smooth operation, even with extensive datasets. Read on to discover how to optimize your formulas and transform your spreadsheet experience for the better.
Hello!
I am working on a project that pulls in data to generate a statement for individuals. I have everything working except on the supplemental sheets. I have a formula pointed at a concatenate (name+row number). It allows me to pull invoice data. Issue is there are some people with over 500 invoices. In my current setup it is vlookup to the concatenate and ifna then blank. I have it working pulling in the invoice data in order. Issue is when I try to carry that formula down to row 400+ the excel just freezes. So for instance on row 302 there's data, but if I pull the formula for each column down the row, Excel freezes and it never allows me to go further with the formula I am using.
TLDR: Trying to find a solution to generate/pull in data to generate a sheet that has invoice data that doesn't crash Excel. Should I switch over to index/match or will that not help performance.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Request for improved methodI work in accounts payable for a company and took over some additional duties a few months ago. One of those duties is keeping a tracker/log of all bills that come in. A tracker in excel was handed over to me. While I’ve improved many things with this tracker so far, I’m looking to make a major change but unsure how to go about it. This tracker has 110k rows of data and has columns with data up to column “FZ”. New rows of data are added daily. Old rows are “archived” as soon as possible. I’m no excel pro, but can hold my own and have learned along the way. Issue: large dataset presents challenges with excel freezing and/or crashing Disclaimer: I cannot remove any rows or columns. Question: is there a better way to handle this data? Ie. tools in excel, using something other than excel, etc? submitted by /u/Visible-Question-786 [link] [comments]
- My Workbook Has Too Much Going On And Keeps Freezingtl;dr I have my workbook doing too much across several sheets and it freezes for too long, anything I can do to fix it? I am a Customer Services manager for a local pest control company, so I am in charge of the team that makes the schedules. In order to check for scheduling errors, I have found a way to generate reports from our CRM and use Excel to find errors. In the past, I simply generated these reports, sorted through the data myself and gotten the results I wanted. It was great proof of concept, but I wanted to automate it for my convenience and so others who don't know the process can use it when I am unavailable. I was able to make a workbook that would take a list of services due in a month and a list of services scheduled for a day, sort, combine, filter, etc. to produce a list of services scheduled that need another service with it, or where a reservice (extra service requested by a client) is scheduled when a maintenance (services automatically due periodically) is due, so the reservices should just be changed to the maintenance. The problem is that whenever I add/remove the data from those reports, Excel freezes for like 5 minutes while it tried to do all that work. It's better than nothing, but I'd like to find a way for it to not do that, at least not for more than a minute or so. Here is are some details of my Workbook: From our CRM, I generate a report of all services due this month and a report of all services scheduled for the next day. I delete irrelevant columns until I am left with these, in this order: For services due: Customer ID (number unique to each account) Last Name (customer name) First Name (customer name) Subscription Status (will always be "Active") Subscription type (text) Service Due (current due date for the service) For services scheduled: Customer ID (number unique to each account) Last Name (customer name) First Name (customer name) Serviced By (technician assigned to appointment) Service type (text) Scheduled for (date appointment is for) I take each report and put them each into their own sheet in my work book ("SubsDue" and "Scheduled"). Both sheets will have the same number of columns and are in this order to match up intentionally. There can be over 15000 total services due in a month, so I have the sheet work with A2:F200000 to ensure it all fits. Row 1 is always just column headers that do not change. There's a helper column on 2-3 sheets as well, uses COUNTIF to check for duplicate Customer ID's, which is important. I then have it sorting through the data for these sheets by using VSTACK and FILTER to add the data to a new sheet, which will then be used again by even more sheets. Ultimately, I will have 2 sheets at the end that will only have the erroneous services I'm looking for. The sheet does this just like I expect it to, it just freezes up for 5 minutes or so while doing it. Here are a couple examples of the formulas my sheets are using: =FILTER(VSTACK(Scheduled!$A$2:$F$200000),NOT(VSTACK(Scheduled!$E$2:$E$200000)="Reservice")) (Takes the data from another sheet and shows only the Reservices included in it) =FILTER(VSTACK(SubsDue!A2:A200000,ReservicesScheduled!A2:F200000),VSTACK(SubsDue!A2:A200000,ReservicesScheduled!A2:A200000)>0) (Combines the data from 2 sheets and excludes the empty rows form each of them) I've been using Excel at a basic level for years and have learned a little more about it recently, but I don't know what to do to help with this. I can only imagine that having a chain of multiple sheets simultaneously using these formulas on up to 400000 rows at a time is just too much for Excel to handle without freezing up. Any recommendations for how I can improve this? I'd appreciate any help and would love to learn from this. Excel version is 16.0.19725.20152 submitted by /u/Royal-Advantage2706 [link] [comments]
- Consolidate data from different worksheets with final information coming from a combination of the worksheetsHoping for some help on a problem I can't wrap my head around. I need to consolidate some information from 12 different tabs (one data pull per month) into one worksheet with some of the data needing to be overridden and some needing to stay. With the most recent pull of the data not necessarily being the information I want showing, I'm not sure how to proceed. I'm trying to find a way to create this and pass it along to someone else to just load data and it automatically puts out the result I'm looking for. For instance, I pull information from my system in January for the entire year (broken into monthly buckets) and all of that is perfect. My second data pull of the year in February isn't so clean. I need to see the numbers from January on any of the January data (because it zeros out in February in my system) but everything related to February through December needs to be from the most recent data pull. Third pull of the year in March, I need to see the numbers from January and February for the January and February data and then need the new info for March through December. Basically, by the time December rolls around, I need data from all 12 sheets. Hopefully this makes sense. Any thoughts on how I can accomplish this in Excel? submitted by /u/megd726 [link] [comments]
- 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]