User Profile
Jpalaci1
Brass Contributor
Joined Oct 13, 2020
User Widgets
Recent Discussions
Automatic Task Assignment Based on Schedule
I kindly request assistance with creating a formula. I have a an employee table with employee names and start times (A1:B5). I have a second table with the day's tasks start times and their end times (D1:E17), all being 30 minutes to complete. I have a running count (G1:G17) of how many times an employee completed a task as the employee can only complete 4 total tasks and taken out of rotation and cannot be assigned anymore. I need a result like in H1:H17 that follows the schedule and assigns a free employee (column A) based on their start times (column B), the 30 minutes to complete the task (column D and E), and on their running count of no more than 4 (column G)Merit Increase Schedule Formula
I have a payroll file where I forecast out for the next five years how much base pay is with scenarios of merit earned by percentage. I have a working formula I created but wonder if there's an easier/more efficient way of completing this? All years/month before current year are just actuals and brought over Months 1-3 of current year takes merit of the previous year(s) Months 4-12 of current year takes merit of previous year(s) on top of proposed merit increase Future year(s)' 1-3 takes all previous year(s) merit increases Future year(s)' 4-12 takes merit of previous year(s) on top of prosed merit increase Essentially, months 1-3 should match months 4-12 and 4-12 then next year's 1-3 should be the same. I wonder if I could use a CHOOSE or some other function that will do addition of an increase more efficiently?Return value in IFS based on date
I want to show the spread of monthly spend on a timeline from January 2021 to December 2022. I used 1-24 to put the right value in the right month. I'm having trouble with the following: 1) I use =IFNA(IFS($B$5<=E3,"Stage 1",$C$5<=E3,"Stage 2",$D$5<=E3,"Stage 3"),"-") to place the costs in the correct cell/month. It works but I don't know how to start and end them; that is, on row 16 is how I want the result to be. Start on the month it should and end after it exceeds the value. I also run into the issue where say stages may overlap/occur on top of each other. How would I show the one that occurs over the other? 2) Is there an easier way to drag down the formula? I can drag across but then for each column I must unlock or change the absolute reference. I need to do this over multiple years so it would be ideal to be able to modify easier to drag across then down or vice versa. 3) Is this the best approach to this? 4) Could this be easier achieved in another way in Excel or in Power BI? This is sort of a spend gantt/visual who to see the monthly spread over a period of time is the goal.Return a value in formula so SUMIFS can subtotal
I have a formula I have a Pivot Table I use to sum data of costs and wish to use those values to forecast months without actuals. Column H is a helper column that helps to identify whether the values is a subtotal (just like I have a subtotal in the Pivot Table). Column Q looks at H and then uses a IFS with a nested SUMIFS to look at I and J to give me a running subtotal for the values I'm forecasting on. The formula works and I can copy and paste from it's spot in Q1 down the entire length of the Pivot Table. The formula works for cell Q6 $302,500. I get that by manually typing in what I want to forecast for that particular row. I noticed in Q10 doesn't work because Q7 to Q8 are manually typed values I want to forecast but if I leave Q9 with the zero that results from the formula my subtotal doesn't compute to just add the two I manually added together and take the 0. How can this be fixed? overwriting the 0 from the formula fixes it but I have about 250 rows and the copy paste values onto or typing a value in every cell isn't ideal. Is there any way to fix this?Subtotaling Next to Pivot Table
I have an output in a pivot table and wish to use this for forecasting future month based not the historical actuals from the pivot table. I believe I may have my solution but want to see if there's a more efficient way to do this. I roll forward the months (R5:Z5), create a unique lookup ID with a concatenation of Account # and Text (column K), and define in a different table (H2:I6). I use a XLOOKUP on the lookup to the table and add each value to get the correct value and it works but I must repeat each yellow subtotal line. This works as I just need to make one XLOOKUP and roll over the next month's. Not a big deal. I checked and it works. I do this over 450 rows and want this as new lines can be added and a simple SUM can be distorted over and then I'd have to manually fix each row due to the shift. Mapping in the table in columns H:I helps to dump in any new accounts that will appear as the year goes on. I fear so many XLOOKUPs will increase size and processing in the file. Is there an easier way to handle this for each yellow line?Power Query Append Repeatability Issue
I have a raw data source that has 350K rows. Each month around 25K-30K new lines are added. I have two tables of an old mapping and new (tables in G:H and J:K). I must show the result in a pivot (M:Q) and have my results but not sure how to make repeatable. Here's my process I hope to make simpler and repeatable: 1) Two different workbooks I dump the raw data. Each workbook has either old or new mapping. 2) New workbook: I use Power Query to import the data from both workbooks then append the two together and output into a table. 3) Second new workbook: Use Power Query and create connection only to the workbook in step 2 then output my desired results in the Pivot. My questions are: 1) Am I doing this right? Can I need to be able to do the analysis I did in my example but not sure if I can do the same in one workbook rather than two for the old vs new lookups. 2) Can my process be improved? Can this be improved using Access rather than Excel? I need to improve processing as to setup (steps 1-3) took 1 hour alone for Power Query to process and then to refresh twice a month.Database design questions
I have some questions on how to design my database: 1) I completed all work in Excel so I know what formulas and what output I want. Is it easier to do various transformations in a new that same table as the data? This screenshot is what my tables look like. Raw data is dumped in the A:D and E and F do transformation on the raw data dumped in then do lookups. Do I follow the same logic in my database? Make new tables and relationships? How would I take this from a flat to relational database? 2) I imported into Access the output I have in Excel though I only pasted just the 350K lines with no format/calculations/lookups, only just values and the database is already 203MBs size. Why? I know Access has a 2GB file size and worry if I need to make queries and add all the tables I need I can hit this limit quickly overtime. 3) Is Access my solutions for my issue? My Excel/file database freezes when I dump in new data because with the design it seems to have to go through so much in memory and lookups to the other tables. I process all I need then make a connection to the data in an Excel workbook so it can be used by the end-user and myself as it's output as a Pivot table. 4) I make a local file but wish to share with another colleague who can use the exact same design just with his local data. Do I send a file with a few lines of data then dump his in? Is this the best way to share a "shell" of a database so he can dump his data in the exact same way I do mine?Conditional Lookup Based on Two Different Tables to Produce Pivot
I need help making this pivot: Columns E:G lookup the bottom master lookup (K6:N9). I then thought I would make a second transfer lookup (top one in K1:R4) to fill in columns H:I based on =IFERRORVLOOKUP([@Name],Lookup_Transfer,5,0),[@Original Sector]), which works but then I needed it to be conditional of when it starts. I thought of a IF(AND where if the [@Month} from the Data table is is less than or equal to (I thought this was easiest but gladly take whatever allows my end result) End in column R and less than or equal to YEAR of column R to [@Year] in the Data table that way I get the correct values for columns H:I and then pivot to get the result I'm looking for. I get lost since how would I make it look to column R only for those who are in the transfer table. Also, is there a way to display a pivotable with only values looking forward? In my example I want years 2021 and 2022 to show months 5-12 for 2021 and month 1 for 2022 but without showing month 1 in 2021. Thank you for any help.Lookup/bring in values from data array into table
I want to take the data from an array and lookup based on the ID #, current month, and correct year but not sure how to get there. I put the answer of how it should be to the right of the table but not sure how to get it from the data on the right to the table on the left.
