Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a "database" in one sheet of an excel workbook. Specifically I have roughly 130 columns, each of which contains a unique value for each row. I.E. each row contains an "entry", with the different quantities of that entry recorded in the columns.

One of these columns gives the entry name. For ease lets say these are people, e.g. "Alex, Damien and Danesh". However each person may have multiples entries (rows).

What I want is a way to display certain columns only of all entries for one person only, in a different format, in a different sheet (lets call this Sheet2).

Here's the especially tricky bit, I want to set this up in such a way as to have the entries in Sheet2 change if new names are added, e.g. say at some point "Clarissa" also has entries in the database.

I'm currently considering different ways to do this, but have yet to find one that looks best. I'm keen to avoid VBA as this workbook already has a few scripts, and it will eventually be handed over to someone who can't program, so my manager wants as few macros as possible.

So in summary the question is this:

How do I copy into another worksheet, all entries corresponding to a particular name, noting that (a) the number of names may change and (b) there is a different number of entries per name?

For reference I am using Excel 2010.

Any ideas?

Thanks in advance.

share|improve this question
    
What exactly are you currently considering? What are you issues with each option you're considering? Without understanding what you've already determined, how could we try to assist you with a solution? –  Raystafarian May 1 at 13:39
    
Well my first thought was some kind of dynamic pivot table, but pivot tables doesn't seem right for this as they summarise data (count, sum etc) rather than just display the values. –  vbastrangledpython May 1 at 13:40
    
My Second thought was using some kind of the INDEX function with MATCH on the names (e.g. Danesh) to pull out all entries. The current issue with this is with having a dynamically updating list of names in the database. –  vbastrangledpython May 1 at 13:40

2 Answers 2

up vote 0 down vote accepted

Your instinct was right, use a Pivot Table. You don't have to use the summarization functionality of pivots at all.

  1. Just add every column you want shown as a row label

  2. In Report Layout= "Show in Tabular Form", also in same section, set it to "Repeat All Item Labels"

  3. Leave "Values" section of pivot blank

Note-This will be quite painful with 130 columns, but without VBA or manual copy-paste...

share|improve this answer
    
Perfect! Many thanks for your help. –  vbastrangledpython May 1 at 14:04

I would convert the data range into a Table (Insert ribbon, Table). Then you can filter the data in place, using the Filter buttons that will become visible on each column header.

share|improve this answer
    
Unfortunately what I require is a way to query this database such that on a separate sheet, specific columns of the range (these columns will always be the same) are displayed in a different format, with only certain rows being shown (these rows being all those which meet a certain criteria of the one of the columns, e.g. all rows that have the value "France" in the column "Country". Unfortunately it is not possible to edit the data in place each time. –  vbastrangledpython May 6 at 15:37
    
Using the Filter buttons in the column headings does not edit the data - it just restricts the visible rows. –  Mike Honey May 6 at 23:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.