Converting data to tally format

A

AMK4

I couldn't think of a better header, sorry. Anyway, I have data tha
comes in like this (via a plain text file)
Code
-------------------
1-Jan-06 Exploit.W32.MS05-039
1-Jan-06 HTML.Phishing.Auction-28
1-Jan-06 HTML.Phishing.Bank-1
1-Jan-06 HTML.Phishing.Bank-1
1-Jan-06 HTML.Phishing.Bank-1
1-Jan-06 HTML.Phishing.Bank-209
1-Jan-06 HTML.Phishing.Bank-28
1-Jan-06 HTML.Phishing.Pay-6
1-Jan-06 W32/Bagle.ai@MM
1-Jan-06 Worm.Sober.U-3
1-Jan-06 Worm.Sober.U-3
1-Jan-06 Worm.Sober.U-3
1-Jan-06 Worm.Sober.U-3
2-Jan-06 HTML.Phishing.Bank-1
2-Jan-06 HTML.Phishing.Bank-1
2-Jan-06 HTML.Phishing.Bank-1
2-Jan-06 HTML.Phishing.Bank-206
2-Jan-06 HTML.Phishing.Pay-25
2-Jan-06 HTML.Phishing.Pay-37
2-Jan-06 HTML.Phishing.Pay-43
2-Jan-06 HTML.Phishing.Pay-6
2-Jan-06 HTML.Phishing.Pay-6
2-Jan-06 HTML.Phishing.Pay-6
2-Jan-06 HTML.Phishing.Pay-6
2-Jan-06 W32/Bagle.ai@MM
2-Jan-06 Worm.Sober.U-3
2-Jan-06 Worm.Sober.U-3
2-Jan-06 Worm.Sober.U-3
2-Jan-06 Worm.Sober.U-3
3-Jan-06 HTML.Phishing.Auction-28
3-Jan-06 HTML.Phishing.Auction-28
3-Jan-06 HTML.Phishing.Auction-28
3-Jan-06 HTML.Phishing.Bank-1
3-Jan-06 HTML.Phishing.Bank-1
3-Jan-06 HTML.Phishing.Bank-28
3-Jan-06 HTML.Phishing.Pay-34
3-Jan-06 HTML.Phishing.Pay-37
3-Jan-06 W32/Bagle.ai@MM
3-Jan-06 W32/Bagle.ai@MM
3-Jan-06 W32/Bagle.ai@MM
3-Jan-06 W32/Netsky.p@MM
3-Jan-06 W32/Netsky.p@MM
3-Jan-06 W32/Netsky.p@MM
3-Jan-06 Worm.Sober.U-3
3-Jan-06 Worm.Sober.U-3
3-Jan-06 Worm.Sober.U-
-------------------


And what I'd like to accomplish is a sheet that looks like the attache
example, which reflects the month of February (as opposed to the abov
data which is from January.) Basically a listing of how many o
whatever virus came in on any particular day.

Any suggestions

+-------------------------------------------------------------------
|Filename: excel-virus.gif
|Download: http://www.excelforum.com/attachment.php?postid=4480
+-------------------------------------------------------------------
 
T

Tom Ogilvy

if the data is all in one column, then select the column and do Data=>Text
to columns, select delimited and space s the delimiter.

this should put the data in two columns.

Insert a row at the top and put in header

DATE VIRUS

then select the Data=>Pivot Table Report

when you to the dialog with Layout, put VIRUS as a row field by dragging the
Virus button to the row area. Also drag it to the data area an it should
default to count. Drag Date to the Column Area

Not OK the pivot table.

the date will probably be vertically oriented. Select the DATA button at
the top with the mouse and drag slightly to the right and release. They
should reorient to a horizontal orientation.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top