Solved

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

Posted on 2014-03-04
9
670 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

863 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

27 Experts available now in Live!

Get 1:1 Help Now