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

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.
Dominator1025Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dominator1025Connect With a Mentor Author Commented:
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!
0
 
Lawrence BarnesCommented:
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
0
 
Dominator1025Author 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.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Lawrence BarnesCommented:
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
0
 
Dominator1025Author 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?
0
 
Lawrence BarnesCommented:
* 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
0
 
Lawrence BarnesCommented:
Thanks eenookami!
0
 
Dominator1025Author Commented:
I was needing to complete my task and this was the quickest solution since I did not receive further responses here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.