Related Posts
She’s a real bugger...
Chief
Additional Posts in Excel Genius
Visual Storyteller
Anyone know what these badges are?
Still don't have xlookup. #fomo
Would anyone be willing to teach me vlookup?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Subject Expert
There are a couple of things here:
1. Never concatenate things (or at least never without a separator) to use as a match key
1.1 concatenate with separator is better, but (i) deciding what to do with duplicated and (ii) use =INDEX(FILTER()) is better, computationally speaking
2. Although a pivot (no matter the type) are different objects, all the cells there are treated the same as any other cell - using =GETPIVOTDATA() or directly referencing the cell should not result in different values
2.1 - ofc, the advantage of using =GETPIVOTDATA() is that if you change the layout of the pivot, it will keep the result you initially intended
3. I never recommend using pivots (although power pivot is better) - they are buggy, not reliable, they are harder to audit, etc.; however, assuming you want to go with it, the recommended path would be to export the data and reference it as a table - you won't get automatic updates such as with formulas (although you can turn it on at the cost of computational resources)
If you could provide a picture (WITH DUMMY DATA PLS) and detail a bit more what you want to do, we could work together on it
Subject Expert
Not necessarily, but that’s a big issue - honestly, how many times that you concatenated you ‘ve checked for that edge case?
I don’t recommend concatenating it because it is faster (for the computer) to check different fields rather than a whole string (at least it is if you don’t organize it and use binary search or any optimized search algo), but for the average user I don’t think he/she should worry about it
Coach
You can add a column in your input data that is doing the concatenation meant to be the key (just make sure that key would be unigue).
Then add this in the "rows"of your pivot table, and remove the subtotals on it of course.
That's not very clean but it does the job
I was thinking if I could create a key within the pivot itself then I could replace the VLookup with a GetPivotData
Try XLOOKUP
Alright so I did find some pages confirming that xlookup is slower but the difference there is so marginal that by the time it would present a noticable issue you would either
A.) Be better off using power query because you are handling a massive amount of data
Or
B.) Need to call IT because if xlookup is going that slowly and you are not working with a large dataset, something else is slowing you down.
So it might be marginally slower but it has plenty of advantages