Reformat Spreadsheet 1

[eao]

So I've got a spreadsheet that looks something like this:

Name Role

Eric 1
Eric 2
Bob 1
Bob 2
Bob 3
Steve 1
Steve 3

I want this spreadsheet to look like this:

Name Role
Eric 1, 2
Bob 1, 2, 3
Steve 1, 3

How can I do this? Thanks.

 

[AnotherHiggins]

First question: Why?

The way your data is currently stored is a much better design and will make it much easier to report on.

For example: If you just want to display something like your second example as a report, you can do so simply with a pivot table.

But leave the original data as is!

For the Pivot Table:
[ul]
[li]Select all data, including headers, that you want summarized[/li]
[ul][li](In your example, this would be A1:B8)[/li][/ul]
[li]Go to Data > PivotTable and PivotChart Report[/li]
[li]Click Next[/li]
[li]Click Next[/li]
[li]Click Layout[/li]
[li]Drag Name to the Row area[/li]
[li]Drag Role to the Column area[/li]
[li]Drag Role to the Data area[/li]
[li]Click OK[/li]
[li]Click Options[/li]
[li]Uncheck Grand totals for columns[/li]
[li]Uncheck Grand totals for rows[/li]
[li]Click OK[/li]
[li]Click Finish[/li]
[/ul]

NOTE: Each role is in a separate column, rather than being in a single column separated by commas.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[eao]

Excellent, I am one step closer.

Now, is there a way I can replace the 1's with the value of the column heading, and then write a formula to concatenate all of the values into the format I am looking for?

 

[eao]

Bump. Still looking for some help on this one. Thanks.

 

[bkpchs237]

eao,
I agree with anotherhiggins. Your data is setup in a fashion that is actually easier to handle than what you propose. However, to accomplish the task you describe I propose the following.
Assuming your values listed start in Row 2 and continue downward, sort on the Names column, then on the Numbers column, both in Ascending order.

Then put the following formula in C2:
=IF(A2=A1,C1&","&B2,B2)
and copy that formula down the column as needed.

Then put the following formula in D2:
=IF(A2=A3,"",B2)
and copy that formula down the column as needed.

After the results are displayed go to Row 1 and choose Data, AutoFilter, Nonblanks. Now select all displayed values and choose, Edit, GoTo, Special, visible cells only, Edit, Copy and then on another worksheet or below all the data displayed, choose Edit, Paste.

Hope this helps.