Separating Same Data from One Column into Separate Columns

**willac** · 05-10-2010, 11:41 AM

Hi, I am a beginner Excel user and have been browsing the forums for the answer to my question, but haven't had any luck so far. I hope you guys can help me, I've seen some brilliant answers to other questions.

I wanted to manipulate data from an auto-generated report of alarm reasons for a piece of equipment. This report (an Excel spreadsheet) gave me the time and reason (on separate columns) why the equipment would stop.

I copy/pasted all the "reasons" into a separate spreadsheet. Each reason is in its own cell, but it's one long column. I want to be able to count each alarm and find the top 5 (or so) reasons why the equipment stopped.

Is there a way to automatically sort the same "reasons" from one column into its own separate column so I can count it (using the numbers on the side)? Or is there a way to automatically count every alarm reason?

I have attached an example page of alarm reasons and the format I have now. The column of alarms can be as long as 5K cells, so I cut most of it out for the example page.

Thank you so much!

Regards,
Willa

**NBVC** · 05-10-2010, 11:47 AM

A very easy way is to use Pivot Tables.

First you need to enter a title at top of the column, so insert a row and type a title in A1.

Then go to Data|Pivot Tables and Pivot Charts.

Click Next.

Verify that it select the correct range and Click Next

then select where you want report to go and click Layout

drag the button with same title as your column from the right side to the Row area, and then again to the Data area. Click Ok

Click Finish.

You should have a table of unique reasons and their respective counts.

**willac** · 05-10-2010, 12:11 PM

Wow, I've never used Pivot Tables before! It's perfect, I can do my job much much much more easily now. Thank you so much for your help! And thank you for your quick reply!