How to create a "Pivot Table"-like grid with text instead of values

Dominator1025
Dominator1025 used Ask the Experts™
on
Is there a way to have the Item column in the attached file auto populate in a grid as shown on the goal tab.  There will be instances where a "Team" has more than one "Item" in a "Category", so it would need to allow for multiples there.

I have tried putting this in a cross-tab, but you can only choose a min or max on the text as far as I can tell, which will only return one value per category.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I will be able to post an example late tonight, but here is the gist.

On the grid that you want populated  both the first column and the first row, "when combined", should create a key that can be used on the data tab to lookup the contents of that you want in that cell.   You could also concatenate  the vlookups if you wanted multiple values within one cell.

For your grid:
So let's say that your fist column header (b1) was "Jan" and your first row label (b2) was "Accounting"

For the data that you are looking up to the first column (a) would have the key field (concatenated from your data elements) with the values:
Accounting-Jan-1 and the value "Payroll" in column B.
Accounting-Jan-2 and the value "Taxes" in column B.

In your lookup grid (watch the locking cell references) the formula to get both of these into cell (b2) would be:
=vlookup(concatenate($b2,"-",a$1,"-1"),data!A:B,2,false)[ctrl]+[enter]vlookup(concatenate($b2,"-",a$1,"-2"),data!A:B,2,false)
(typed on an iPad...will make corrections tonight if needed.)

Note that Ctrl + Enter wraps the second vlookup into the second cell line.  Also note that the lookups in this formula would have to be repeated (incrementing the "-3" for the sequence that you would want to find.  Also, there are character limits in Excel, so this may be best used for smaller reports.  Lastly the dashes are not needed...just makes it easier to read when looking at the data.

If you need I'll upload a sample tonight.  There are also many other ways/applications to do something similar...
LVBarnes

Author

Commented:
I think we might be on different paths.  In your example, I would want Payroll in B2 and Taxes in B3, where both would be Accounting for January.
Finally saw your example.  I've uploaded your file with the adjustments to your data and a grid on the data tab.  It is setup to handle up to 5 groups per cell (you can add more), just remember the IFERROR with each new one.

HTH
LVBarnes
TestFile.xlsx
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
LVB - thanks for the update, but I need the Category column to flex when a team has more than 1 item. Please see my "Goal" tab where this would be necessary in cells B3 and B4.

Is it possible to automate that?
* Via formulas...I don't think so, at least not without setting up the category and teams beforehand and then using a filter to only show the rows that have teams in them.
* Using a hybrid pivot table and formulas...maybe...but not suggested.
* I think most advanced users would use VBA to do this, but that is beyond my ken and I'm not sure if you want to go that route.

You could "request attention" and the Excel VBA Gurus would descend upon you quickly.  They can do just about anything through VBA.

LVBarnes
Thanks eenookami!
I have indexed my list and then created a multi-row formula in a grid for 2 rows, which I then copied down throughout my grid. This solved my problem. Thanks!

Author

Commented:
I was needing to complete my task and this was the quickest solution since I did not receive further responses here.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial