User Profile
Jigyasa
Copper Contributor
Joined Dec 06, 2022
User Widgets
Recent Discussions
Re: Is it best practice to add helper columns or is there a better way to vlookup?
Harun24HR Hi I am on Excel 365. I've attached a demo file. The exception sheet is a separate sheet in my actual case and there is a macro that runs to create sheet 3. I am wondering if I am following the best practice method to achieve this.1.9KViews0likes0CommentsIs it best practice to add helper columns or is there a better way to vlookup?
I need to lookup pay rate exceptions from a separate sheet. On the separate sheet, I have four tables: 1. Client names, days of the week, and job type 2. Second table with days of the week, job type and location 3. Third table with Days of the week and job type 4. Fourth table for Standard rates including days of the week and job type How I have done this is a macro creates three new tables, creating combinations of: 1. Combo 1: Client + Days of the week + Job Type 2. Combo 2: Days of the week + Job Type + Location 3. Combo 3: Days of the week + job type I have also added these combo columns on the separate sheet, and am using a vlookup with nested if to check in hierarchy of Table 1, then 2, then 3, and if it matches none of the exceptions then table 4 Standard rates. The result is a very long if statement with many vlookups which if it matters looks kind of like this: =IF([@Combined]=IFERROR(VLOOKUP([@Combined],Table 1,1,0),0), IFERROR(VLOOKUP([@Combined],Table 1,7,0),0),IF([@[Combined_ rate_job_location]]=IFERROR(VLOOKUP([@[Combined_ rate_job_location]],Table 2,1,0),0),IFERROR(VLOOKUP([@[Combined_ rate_job_location]],Table 2,6,0),0),IF([@[Combined_ rate_job_std]]=IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 3,1,0),0),IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 3,6,0),0),IF([@[Combined_ rate_job_std]]=IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 4,1,0),0),IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 4,6,0),0),10.1))))" It works fine but I want to make sure I'm following best practices and I'm not sure if there is a better way to do this. I tried to use AND statements but it ended up looking even more complicated. So I'm just looking for advice to make sure I'm learning the right way.2.1KViews0likes2CommentsRe: Best way to combine 400 small tables in Excel?
Hi, The only issue with this is, my tables look like this https://ibb.co/MBB7NBX and each worksheet is monthly data. So when I upload them individually to a query, I'm extracting the first column name to create a client column and adding a new field to include which month the table is from. The month information is in the table name, example: clientABC_july22, but I have not been able to import. So essentially the reason I'm uploading each table individually is so I can include the client name and month of the table into the data so that I can differentiate this data when I eventually append it all into one big table.2.4KViews0likes1CommentRe: Best way to combine 400 small tables in Excel?
I have 27 sheets in the same workbook and I'm not quite sure what you mean by the same name as Excel doesn't allow me to have two tables with the same name. As for columns, they are mostly similar but have one difference of each client's names which I need to extract.2.5KViews0likes4CommentsBest way to combine 400 small tables in Excel?
I have about 27 worksheets with 15 smaller tables on each worksheet that I need to connect into one major table to eventually create a dashboard. I used Power Query to do this. So far I've reached 170 queries and it takes a very long time to refresh. I cannot change the main tables as it is easier for everyone else to view and they update the data in these tables throughout the month. I'm looking for an alternative, would it really be faster if I simply tried to make a master table individually referencing the value in each table. I thought power query method would be more efficient. Please let me know if there is an alternative I haven't thought of yet?2.7KViews0likes7CommentsMoving average versus exponential forecast, which is better for predicting respondents?
Hello, My dataset includes monthly data of about 12 months that shows a funnel including, number of respondents, number of people who showed up at the event, and number of people who signed up to join for future events. I would like to set up a forecast so that we can set a target of let's say 80 new joiners and it would show us how many people we would need to achieve this target at each stage based on previous data. Currently, I've gone with taking the last two months running average to get these numbers. But as I'm not too familiar with the forecast functions, I'm not sure if that is a better method for such data. I just want to make sure I'm following best practices. Please advise if I can do this in a better way.453Views0likes0CommentsCan I get forecast.ets to update according to new data added to the table?
I've got a table with monthly number of application data. And another one where I've used linear forecasting to forecast let's say Jan, Feb, and Mar of 2023 applications. However, I think exponential forecasting is more suited for this. Previously when I entered December's data, it would update the forecast for Jan, Feb, and March accordingly with the new data entered (linear). But when I switched to forecast.ets, the values remain static. Is there any way I can make sure the forecasts update with the new values entered so that I don't have to manually update it every month?740Views0likes0Comments
Recent Blog Articles
No content to show