Creating a table similiar to Pivot that displays the data rather than making calculations

**Kfisher** · 02-12-2013, 03:40 PM

As the title says, I am trying to create a workbook that has two pages. On the 'Data' page there will be raw data pasted in from a different report that spits out results in excel format. On the 'Results' page I want to be able to display only the pertinant information from the 'Data' page. The raw data has something like 50-60 columns (all with headers) and I only want to look at 5-6 of them. I basically want to do what a pivot table does but instead of putting out a calculation in the values part I just want it to display the actual data.

Therefore a user could paste in the new raw data, switch the Results worksheet and sort the data by as many different parameters (values in certain columns on the raw data) as I want. Just like a pivot table. Then in the values it puts out the exact values that are in other worksheet. Example:

'Data' work sheet laid out like this:

Program..Theme..Contract..Aug..Sept..Oct
AAA........BBB.......CCC.......50....60.....40
AAB........BBC.......CCD.......30....20.....50
AAA........BBB.......CCE.......40....10.....20
AAB........BBC.......CCC.......20....00.....80

On the results sheet:

Program Here the user can select any program that shows up in the program column on the Data sheet
Theme Here the user can select any theme that shows up in the theme column on the Data sheet

Contract..Aug..Sept..Oct
Blah.........xx......xx......xx Here the values for that program or theme (or both) are displayed.

Any ideas? Thanks for the help!

**Melvinrobb** · 02-12-2013, 04:02 PM

Would creating a column in the far left of the raw data called "ID" and have a unique ID for each entry help?
You then could make that ID the "Row labels", and any columns you wanted to have displayed can be put in the "Column Labels".
I might not be understanding you correctly, but I think that should work.

**Kfisher** · 02-12-2013, 05:49 PM

I see what you are saying but the problem with that is the report (that provides the raw data) produces around 2500 rows per report. I want the user to just be able to copy and paste the whole excel sheet into the Data sheet without having to enter anything else. Then have the Results sheet show only certain columns (14-15 of the total 67 columns) and only show the rows with values that match the filters/searches at top.

**Melvinrobb** · 02-12-2013, 06:14 PM

Is something like this what you are looking for? Paste the report in column B, and the COUNTIF function in column A will find the rows that meet the criteria you enter in the second sheet.

**Kfisher** · 02-14-2013, 12:04 PM

Yes that is very close to what I need, just on a much larger scale. How did you go about creating it so that it would display only the data you filtered for on the other page?

Thanks so much!

**Melvinrobb** · 02-14-2013, 12:11 PM

The countif function in column A only shows a number if the criteria are met.

Formula:

Please Login or Register  to view this content.

In basic terms, the formula above does the following: If both B3 = the selected program and c3 = the selected Theme, then add 1 to the max value in the rows above, so each match is a unique number(1,2,3,.....).
You can then find the cells you want using INDEX & MATCH, as you have unique identifiers for each row and column.

If it still doesn't make sense, do some research on the COUNTIFS and INDEX & MATCH functions.

**Kfisher** · 02-14-2013, 01:25 PM

I think I'm following you on how to formulas work. One last question, is it possible to display all rows matching only one of the critera? Say I wanted to see all values for Program C, not just program C theme Y?

**Melvinrobb** · 02-14-2013, 01:32 PM

"Wildcards" will work.
You could edit for the formulas so that if, for example, the theme entry cell is blank, it automatically searches for a "*", but right now it would make your formulas even more confusing, which I would avoid until you have a solid understanding of them.
Right now, if you manually type a "*" in an entry cell, it will essentially ignore that filter.

Creating a table similiar to Pivot that displays the data rather than making calculations

LinkBack

Thread Tools

Rate This Thread

Display

Creating a table similiar to Pivot that displays the data rather than making calculations

Re: Creating a table similiar to Pivot that displays the data rather than making calculati

Re: Creating a table similiar to Pivot that displays the data rather than making calculati

Re: Creating a table similiar to Pivot that displays the data rather than making calculati

Re: Creating a table similiar to Pivot that displays the data rather than making calculati

Re: Creating a table similiar to Pivot that displays the data rather than making calculati

Re: Creating a table similiar to Pivot that displays the data rather than making calculati

Re: Creating a table similiar to Pivot that displays the data rather than making calculati

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions