Forum Discussion
Macro to Extract Data from 500 blocks of Information. Help!
I have attached a file with just two blocks of information that was downloaded from another program.
There are over 500 blocks in total. I need to first Replace All occurrences of "Totals: $" with nothing so that only the dollar amount is left. Then I would like to copy and paste the name of the first person over to the right in Col. D, on the same row, in the same file. It's ok if it is directly to the right of these cells as I will later like to do a sort and then delete unwanted rows. Ideally I would love the copy to be on a new sheet tab in the same file, but that could get tricky? The name and address reside in one cell only. I know that if I press [F2] the cell goes into Edit Mode and I can Ctrl+Home to get to the top of the cell and then Shift+End to copy the name. Then I could paste it and move down to the dollar amount. Unfortunately, the computer I am using does not have [F2] functionality. Is there another way to put a cell into Edit Mode via the Ribbon?
Once I get the name copied, then I need to go back to Col. A and down to the dollar amount and copy that over to the right of the name that was just pasted, in Col. E. This dollar amount will be at the top edge of the cell, so then I need to highlight the column and move the column contents to the bottom of the cells.
Then on to the next block of information. I can write simple macros, but I do not write VB at all.
How do I "copy" these instructions (in Visual Basic?) to repeat 500 times? The number varies each week. It could go up to 600 separate blocks one week or only 450 separate blocks another week. How do I get the macro to stop once it sees that there are no more blocks of text? I know how to get into VB and I've done minor editing, but nothing like this.
I would then copy both Columns and paste them into a new sheet tab so that I can sort as desired.
I appreciate any help you can give me. Thanks so much!
10 Replies
Hi Sue
You'd be best off using Power Query (Get & Transform) if you can for this.
What version (Excel 2016, 2013, 2010) of Excel do you have?
If 2010 or 2013 then download Power Query https://www.microsoft.com/en-au/download/details.aspx?id=39379
I've attached a solution using this, and can talk you through how to use it
Hi Sue
You'd be best off using Power Query (Get & Transform) if you can for this.
What version (Excel 2016, 2013, 2010) of Excel do you have?
If 2010 or 2013 then download Power Query https://www.microsoft.com/en-au/download/details.aspx?id=39379
I've attached a solution using this, and can talk you through how to use it
Thank you so much, Wyn! Genius! Everything worked like a charm! I really appreciate the time you took to fix the file and then communicate to me how to then do it myself. You sure saved me a ton of time and effort trying to figure this all out on my own. Really appreciate it!
No problem, Power Query is definitely worth learning about.
If you do a lot of this sort of thing then I can't urge you strongly enough to attend a course on it.
Also the best book on the topic is this M is for Data Monkey
Good luck
Thanks so much, Wyn, for getting back to me! I was beginning to worry that no one thought my problem was problem enough!
I did download the 32 bit version of Power Query. I am running Excel 2013, but it wouldn't let me download the 64 bit version. I also pulled up some help information on how to use this feature, never having heard of it. So, even though the name, street address, city/state/Zip are in the same cell, it will extract the information into separate columns? That would be the fix - although on a separate cell below this contact information is the dollar amount. I would want that in the final column. Then I could easily delete/hide the columns I don't want and work with the ones I do.
I would appreciate any insight you can give me. I played around with it a little, but didn't get it to work.
I also tried the Data/Text to Columns feature in Excel . . . didn't quite get that to work either, but it was close.
Again, I REALLY APPRECIATE your help! Love that I don't have to write a macro! Yea!
Hi Sue
32 bit is fine (most people have that version).
If you download my file (in the previous reply) you can then point it at the correct source file on your system and it should do the transformation for you.
To repoint it at your file:
Once you've downloaded my file go to the Power Query ribbon and select the Data Source Settings button....
then click on Change Source and go and find your source file that needs formatting
After that click on Show Pane to show the query panel down the right hand side
Finally RIGHT-CLICK in the green table and select refresh.
Power Query should then pull the data from the source file and clean it up.
To look at what the query is doing you can double click on the query in the Query Pane on the right and then click on each of the "Applied Steps" starting with source and working your way down.
For more info on Power Query I've done some videos
https://www.linkedin.com/today/author/wynhopkins
Check out Power Query part 1 and 2
