Pivot Table only from filtered data (Date grouping issue)

[mcastillo]

Hello everyone,

Your site has come in handy too often not to register despite my concerns with being an Excel amateur. Figured i could at least post to attempt to get help with a problem i have been facing.

I have a query table that generates a few hundred thousand results, one of my vlookup formulas on the far right side is looking up posting dates. There are some records which for various reasons do not generate results, which ultimately generate a "#N/A" result. My rows in the pivot table must be the dates field and it's necessary to group by month and years but the "#N/A" results seem to be preventing me from performing a simple group. Manually deletion has proven quite time consuming, given the amount of empty results in the system and the amount of time it takes for the query to refresh is 15-20 min as it is due to the amount of data i am pulling from different tables. I was hoping that the pivot table options would have a way to only be based off the filtered results? Currently, even if i apply filters in my query, the generated pivot table still seems to be pulling all results, including the items i have attempted to filter out. I also cant simply configure my query table to omit blank date results because the date is not part of the original table, its a vlookup to another table which also offers no way of being pre-filtered to help omit these blank results in the first place. Any help will be greatly appreciated.

Thanks

 

[Fazza]

welcome

First thought - just putting it out there - is to not have the query table results in a worksheet. Just go straight to a pivot table & make it from both the database AND the looked up table. This dataset would only have the data you want.

If you want to keep the query table results, then when making the pivot table filter out the unwanted records. Maybe change the VLOOKUP formula to something like
=IFERROR(VLOOKUP(as it is now),99999)
Then the unwanted dates have value 99999
Now you can either put them into the pivot table and filter them out, or even filter them out before the pivot table. This can be done with something like

SELECT *
FROM yourtable
WHERE YourDateField <> 99999

HTH. Regards, Fazza

 

[mcastillo]

Thank you for taking the time to respond! These are great suggestions! I will test them out asap!

 

[mcastillo]

Tried both options and it did clear out my errors with dates but my pivot table still refuses to let me group the dates. I get a clean table with all dates presented individually but when i attempt to group them by month and year i still get the same error. There must be something else throwing it off. The only thing i have noticed and may very well be the reason why its doing this is that I am actually bringing in the date field using a vlookup from another table and every time i have tried to group dates that i vlookup from other tables i get the same error, but if i create a pivot table directly from the source table it works just fine. Even though the vlookup results on the second table appear exactly the same as the source it wont work, but i have a hunch its because of this...

 

[mcastillo]

Thank you again for your help. Figured it would be rude not to take a moment to update the thread and mark it as resolved. Thank you again for your suggestions. Turns out our PMS provider, our hotel reservations system (ResortSuite) perfomed a sloppy job in cleaning out some reservation records that were supposed to also get deleted which was causing major inconsitancies between the 2 tables i was attempting to link and ultimately view in a basic pivot table. After some back and forth they realized the issue, cleaned up our data and now the dates group. Regardless, thank you for your help and suggestions, I'm sure those will also come in handy during one of my next speed bumps.

 

[Fazza]

Thanks for the update. Great to know you sorted it out. All the best. Regards, Fazza