book recommendation to learn excel / want a macro to extract tickers / pivot table

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 excel file called Raw Data, I want it to end up as the other attached 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:
\1

Resulting Data:
\1


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

Hi Dan, please take a moment to edit your thread title to something descriptive of your problem, per the forum rules. Good thread titles help people find what they're looking for easier when searching, and allows volunteers to quickly see which posts they may want to or can help with.

Thread titles should briefly describe the problem and not the solution or things you have already attempted. (If you can't come up with a concise thread title, your post may be asking too much or too vague.)

Thanks!

sorry about the mistake paul, i had thought my title was reasonably descriptive. i have edited it, hope this works.


i actually have another question now. i created a macro but didnt get the toolbar for it. instead it just said at the far bottom-left corner that it was recording. i think i figured out the issue, toolbars weren't on. but now stuff has been recorded and i want to start this same one from scratch. i can create a new one but id like to work on this if possible, for experimentation. first off, how do i do this? (without knowledge of VB). secondly, when i click on the marco and hit 'edit' i get an error saying: cannot edit a macro on a hidden workbook. unhide the workbook using the unhide command.

what do i do? i certainly never tried to hide any workbook and dont know how to unhide it either.

Instead of uploading pictures of workbooks, upload an actual workbook with BEFORE/AFTER sheets with enough data in it to be useful.

Click on GO ADVANCED and use the paperclip icon to upload your sample workbook.

=======
Also, please read all the forum rules. Crossposting on multiple forums is understandable, but forum rules require you post a link to those other threads in your thread here. We like to help, and we do it for free. To find out our time is wasted because the problem is already solved elsewhere and we had no link to the other activity...that would be bad, yes?

http://www.mrexcel.com/forum/showthread.php?t=420544

Hey zazz, this is a VBA solution, formulas just got too cumbersome for me to think through.

Please Login or Register  to view this content.

=======
How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Edit the sheet name references in the macro, if necessary
6. Get out of VBA (Press Alt+Q)
7. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

thanks so much, this is great of you to go to all this trouble!

and thanks for breaking down how to put it into VB, i didnt really know.

everything worked fine, except the running part. i didnt really know which macro it was but i just assumed it was the last one on my list. that didnt do anything. neither did the second last one. i was actually surprised it didnt ask me what to name my macro, is it not supposed to do that?

-------------------------------

EDIT - ooh never mind! i see it now. didnt see that earlier, dont know what happened there. thanks again for the help.

btw is there a way to sort this by how many times a ticker was 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 again beaucaire!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

How about instead starting a new thread with a title that describes one issue?

Thanks.