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've a worksheet of 35k rows of the kind in the first table below that I want to reshape as in the lower table. http://i.stack.imgur.com/Zc4u1.png So, I want to reshape this data in a way to get all the people within a specific GroupID in separate columns.

I suppose that I should add a counter for each row within specific GroupID? Also, I suppose these kind of issues are best addressed in a database?

Since I often have this kind of data, I need to be much quicker about solving it than I am now.


Not from OP. Clarification from Comment

GroupID is primary key.
Names may be repeated but > 70% are unique.
Sort order is unimportant.
GroupID with different Ins may be at most 10.
Effective maximum for GroupID/Ins/ID/Name/Sales blocks is around ~20k.

share|improve this question
    
Group ID is primary key. Names may be repeated but > 70% is unique. Sort is unimportant. Group ID with different Ins may be at most 10. Effective maximum is around ~20k. –  Velletti Sep 5 '12 at 7:41

2 Answers 2

I note with regret you have not progressed well with this question and hope you have a reached a solution regardless, but in case not I offer at least an interim answer. It is not highly automated for various reasons including that I do not know which version of Excel you are using and explaining complex formulae is tedious! Also, I note “I often have this kind of data” so perhaps a ‘proper’ solution would either be the database route or at least VBA if the data format is reasonably consistent and processing requirement repetitive. Though for VBA you would be more likely to get a good answer at StackOverflow.

So, the result I have is:

sample output re SU469870

Not the same order as in your example but you did mention the order was not important. Also I have a difference (highlighted) but that is because of a discrepancy between your input and output examples.

I achieved this with essentially the steps below, having assumed that data is in ColumnA:E with column labels in Row1:

  1. Sort A:F on GroupID.
  2. In F2 key =IF(A2=A1,"","b") and copy down.
  3. Filter F and select b.
  4. Copy visible to H1.
  5. Delete data visible in ColumnA:F
  6. Sort A:F on GroupID.
  7. Copy visible to M1
  8. Delete data visible in ColumnA:F
  9. Repeat steps 6-8 about 8 more times (stepping across 5 columns each time when pasting) - until there is no data left in A:F.
  10. Delete A:G, final column and any other cells containing a formula (display as b).
  11. Align blocks manually (feasible? - should be capable of 'automation' if necessary).
  12. Delete GroupID columns other than one on extreme left.
  13. Apply borders.

There is no significance in b - just any marker but I chose that letter since moving one *b*lock at a time.

Hopefully it is all self-explanatory though I would be happy to clarify (or attempt a fix!) if required.

Note that I worked on a copy and recommend you do too as Delete is used above.

share|improve this answer

I would suggest you use Pivot tables. Searching Google for "excel pivot tables tutorial" will bring up many websites which may help.

As for databases and reporting, SQL and SSRS may offer you more, but it depends on what you're after.

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. Administered via a web interface, it can be used to prepare and deliver a variety of interactive and printed reports. SSRS competes with, for example, Crystal Reports.

share|improve this answer
    
Hello Dave, I am familiar with PivotTables but they do not work well with text as values, and names are quite important here. Or You suggest using it for numbers and then appending names? –  Velletti Sep 4 '12 at 11:17
    
I'm not sure why you can't do this with text. It works fine for me with text. The issue you may face is that you want the results displayed as columns. I've not done that before. –  Dave Sep 4 '12 at 12:03
1  
You can place various text as values in Pivot Table? (not for the Row Label, but for actual values?) Can You please elaborate? Or give me tutorial where Youve learned it? –  Velletti Sep 5 '12 at 7:37

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.