Is this "Project" feasible or even doable in Excel???

[RichLN]

I have been tasked to do the following:
1.) Make a database of raw data from multiple different departments, which all have varying sets of data. In other words, they don't track the same metrics, so raw data comes to me in different column counts. This means a "master database would have to be made by references everything to a very messy, gigantic table that would probably be 100 columns long.
2.) Take that raw data and translate it to a userform whereby a manager can select the department, shift, and/or person they want to look at. Then select a date range to look at and it display just those selected things.
3.) Keep in mind employees can work in multiple departments throughout the day, but the "raw data" reports will only track that person in one single department. So the same person might appear on multiple raw data reports.
4.) Based on Employee codes and headcount, I would be adding about 30-50,000 lines of new data a month to the raw data list. So that's 360,000-600,000 lines a year.

Backstory behind all this:
Basically what happened is my bosses asked me to do a project that was SIMILAR to the above, then somebody showed them "RedPrairie" and they turned around and asked me to make them a "RedPrairie" in Excel...in 4 hours...so yeah the timeline definitely isn't happening but I'm trying to determine if this project is even feasible or doable in Excel? I'm thinking that the raw data list will quickly "bog down". I'm also thinking there might be other issues...

IF I had a raw data list that big, I MIGHT be able to make a pivot table that could select the department, employee, and/or shift using userforms and macros (b/c I more or less had something similar to it but haven't got part of it working b/c I can't get excel to pull the newest data from the bottom of the pivottable). But I don't know how to make Excel accept two user input dates and filter the pivottable for dates between those two?

Btw, if anyone knows an excel VBA that will pull the next to last row from a pivottable that gets updated daily with new info I'd appreciate it. I just don't know why the code I wrote isn't working right? I can possibly post that if needed.

To me, this 100% sounded like an Access project but internal circumstances that happened before I started working here pretty much bar me from using Access. So again, STUCK with Excel.

Thoughts or comments??? (Thank you in advance!)

 

[ghrain22]

I think you'd run into data limitations pretty quickly. Excel 2010+ maxes out at 1,048,576 rows.
https://support.office.com/en-my/ar...d-limits-1672b34d-7043-467e-8e27-269d656771c3

I agree with the sentiment that it should be done in a pivot table. You can filter with "between" dates with a pivotfilter, but if you wanted a more UI friendly approach, you'd have to write a macro that loops through the pivot, or maybe a slicer(which isn't as hard as it sounds, just possibly slow)?
An example looping through slicer cache:

Application.ScreenUpdating = False    
    'Remove existing filter
    ActiveWorkbook.SlicerCaches("Slicer_Category").ClearManualFilter


    Dim SI As SlicerItem
    For Each SI In ActiveWorkbook.SlicerCaches("Date").SlicerItems
        If SI.value < Range("$A$1") and SI.value>Range("$A$2") Then
            SI.Selected = True
        Else
            SI.Selected = False
        End If
    Next SI

In regards to appending data to your source, I would suggest a dynamic named range. Make a named range and have it count the number of column headers and the length of data. It would look something like this:
=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A), CountA(Sheet1!$1:$1))

This way the pivot source range never changes, the range is just recalculated.

 

[RichLN]

Thank you! I have made some fairly significant strides in the project thanks to your suggestion to use slicers. I am not doing anything that modifies them, but by simply showing them to the bosses was enough to do those portions. Thank you!

 

[SpillerBD]

You can source the data outside of Excel as one way around the row limit. You can use a txt file or Access (or others). There still is a caveat of the Pivot Table being an OLAP style Pivot Table which reduces certain functions.
Power Query provides some functionality of dealing with the source data (Query, merge append...) that may provide some useful functionality for you.
PowerPivotPro - Transforming your Business with Power Pivot and Power BI
Download Microsoft Power Query for Excel from Official Microsoft Download Center

Next to last row... Is that a Total or Subtotal row? You should be able to use a GETPIVOTDATA formula. (Though those can get a bit picky when trying to apply your own variables.)