?
Solved

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

Posted on 2014-03-04
9
Medium Priority
?
693 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

764 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