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.

As I am a newbie when it comes to Excel (and the entire Microsoft Office suite, to be honest), I spent a lot of time browsing for a solution to this issue - how to get a one-to-many rows table out of a normalized table - and since I'm posting this, it's obvious I didn't find a proper answer.

To be more clear, say the initial normalized table is the one presented below:

How the table initially looks like

And the resulted table should look like this:

How the table should look like

Now, for a table with a few rows, the answer is quite obvious and a bit inefficient:

  1. Sort the column which contains cells with same value;
  2. Manually select the groups of cells with same value and right click on 'Merge & Center' button (see picture below).
  3. Repeat Step 2 for all of the identified groups of duplicated cells within that column.

The 'Merge & Center' approach

The challenge is to obtain the same result for a table with large ammount of data (~6k rows), using Excel 2010. Obviously, the solution presented above is far from being efficient.

Any thoughts on this? I would really appreciate your help.

Many thanks,

Lavinia

share|improve this question

migrated from stackoverflow.com Sep 13 '12 at 0:07

This question came from our site for professional and enthusiast programmers.

3 Answers 3

Have you had a look at pivot tables? This would appear to do exactly as you want.

The first thing to do is to ensure that you have a header row in your data table.

Then select any cell in your data range and go to the Insert tab, and choose Pivot Table.

Accept the defaults and click OK. This will open up a new pivot table and you'll want to put Field1 and Field2 in the "rows" section (field 1 first).

Then you just need to change some formatting options:

In the Pivot Table tools tab (which has now appeared as you're in the pivot table), click the Design tab and in the Layout group choose Report Layout / Show in Tabular Form

Again in the same Pivot Table Tools / Design / Layout choose Subtotals / Do not Show Subtotals

Again in the same Pivot Table Tools / Design / Layout choose Grand Totals / Off for rows and columns

Finally, right click anywhere in your data table and choose Pivot table Options then in the first (Layout & Format) tab tick the box that says Merge and center cells with labels

You can then re-use this pivot table to point at a new data set when it becomes available (Pivot Table Tools / Options / Change Data Source)

EDIT: Just to show you what the final output looks like. This took me less than twenty clicks:

Pivot table result

share|improve this answer

You can use the SubTotal option, located under the Data tab on the ribbon. I have recreated your spreadsheet as shown below:

Subtotal

If I just highlight the data, click on the Subtotal button, and set the options as below:

Options

I get the following output, it's not exactly what you are after but it it may help you out:

Output

share|improve this answer

Jon proposed a good attempt to this, but as you stated, that you are "new" to MS Office, I'll propose you some different approaches.

You should question yourself or the requestand of this task, if Excel is the right tool for it, because it is more of an Access/SQL task.

If it has to be Excel, you could also use the Subsum function on the Datatab. This is not as powerful as a PivotTable, but basically very similar functionality.

share|improve this answer

Your Answer

 
discard

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