want a macro to extract tickers / pivot table

B

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
844
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I need to manipulate some data in order to make it reasonable. I'm reading up on Macro's now in order to be able to do this stuff in the future, but I think macro's are a long term thing. So coming for help.

Actually before I go into that, is there any book one can recommend for macro's? I do have a friend's books, How to Do Everything with MS Office Excel 2003, MS Excel Bible 2003, and MS Office - Excel 2003 Formulas. Are these good or any other recommendations? In particular I would like some data to play around w/ so that when in the book they tel me to do something I actually have some data to manipulate. I imagine learning the basics of macro's will be easy enough, but as I understand it the real power of macro's comes from programming in VB, and I dont see how that can be covered in these books.

Now for the more specific questions -

My raw data looks like the attached pic of the excel file called Raw Data, I want it to end up as the other attached pic of excel file called Result. How would I create a macro to read where the ticker begins and ends? Is there any way to do it w/o VB knowledge?

Raw Data:
http://i103.photobucket.com/albums/m...n1/RawData.jpg

Resulting Data:
http://i103.photobucket.com/albums/m...an1/Result.jpg


More importantly, I want to be able to order the tickers by how many times they've been mentioned. For example in the second excel file called Result, Laura has been mentioned twice as often as anyone else. When I sort this list I want to sort by who's been mentioned the most. So I'd like to see 6 lines of her data, followed by the person mentioned the second most (if they had 5 lines, the next 5 should be them etc). For this one would pivot tables do the job, or do I need macro's for this as well?

Thanks
Dan
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
i posted here because my thread had been there for 3 days on the previous one and it looked like no answer was coming. interestingly enough right after posting here that one received a response. sorry about the mistake, im fairly inexperienced w/ excel forums.
 
Upvote 0
Try this:
Code: 
Option Explicit
Option Compare Text    'non-case sensitive matching

Sub ReOrganizeStrings()
'JBeaucaire  (10/6/2009)
Dim LR As Long, NR As Long, i As Long, c As Long
Dim MyName As String, MyDate As Date, MyArr
Application.ScreenUpdating = False

Sheets("Sheet1").Activate
LR = Range("C" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    Select Case Cells(i, "C").Text
        Case ""
            MyDate = 0
            MyName = ""
        Case "Top Picks:"
            MyArr = Split(Cells(i, "A"), " ")
            For c = 0 To UBound(MyArr)
                Select Case MyArr(c)
                    Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
                        MyDate = DateValue(MyArr(c) & " " & MyArr(c + 1))
                        Exit For
                    Case Else
                        MyName = Trim(MyName) & " " & MyArr(c)
                End Select
            Next c
        Case Else
            MyArr = Split(Cells(i, "C"), "(")
            With Sheets("Sheet2")
                NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                .Cells(NR, "A") = Format(MyDate, "DD-MMM-YY")
                .Cells(NR, "B") = MyName
                .Cells(NR, "C") = Left(MyArr(1), Len(MyArr(1)) - 1)
            End With
    End Select
Next i

Sheets("Sheet2").Activate       'See the results
Application.ScreenUpdating = True
End Sub

im fairly inexperienced w/ excel forums.
Click to expand...

That's why most forums have Forum Rules posted pretty prominently, if you look for them. They not only tell you what is expected, they typically give you great tips on the most efficient ways to use the forum for greatest benefit. Worth the time to read.

http://www.excelforum.com/excel-mis...t-a-macro-to-extract-tickers-pivot-table.html
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Running Data Refresh Macro, then Table Update Macro, Back-to-Back?
2
Replies
11
Views
827
Alex Blakenburg
A
C
  • Question
Confused over pivot table calculations :-(
Replies
1
Views
398
number kruncher
N
zookeepertx
  • Question
Macro will run correctly in 1 workbook but not another
Replies
5
Views
516
zookeepertx
zookeepertx
A
  • Question
Creating a School Time Table
Replies
6
Views
460
Legacy 143009
L
S
  • Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
168
Solon Aquila
S

Forum statistics

Threads
1,216,462
Messages
6,130,781
Members
449,591
Latest member
sharmavishnu413

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top