Solved

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

Posted on 2014-03-04
9
664 Views
Last Modified: 2014-03-23
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.
0
Comment
Question by:Dominator1025
  • 4
  • 4
9 Comments
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39904250
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
 

Author Comment

by:Dominator1025
ID: 39904354
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
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39904602
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
 

Author Comment

by:Dominator1025
ID: 39904652
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39904732
* 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
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39915828
Thanks eenookami!
0
 

Accepted Solution

by:
Dominator1025 earned 0 total points
ID: 39937356
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
 

Author Closing Comment

by:Dominator1025
ID: 39948402
I was needing to complete my task and this was the quickest solution since I did not receive further responses here.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now