Macro to Extract Data from 500 blocks of Information. Help!

[Sue Ditsch]

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!

[#2]

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 

[#3]

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 

[#4]

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!

[#5]

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

[#6]

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!

[#7]

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

[#8]

Thanks again, Wyn.  I'm a retired instructor who helps friends out when they get stuck using Excel. In this case, our church needed the help and called me. I will go over there today to show them what you did. You are saving HOURS of time for someone and she really will appreciate it. Thanks for the book recommendation as well! I can pass this along to several people I know. 

[#9]

Wyn, I was over at our church today and just as I was ready to watch their secretary do her "happy dance", I saw that she had Office 2016 loaded on their computer and "Get & Transform" was already on the Excel ribbon.  Well, of course, all of your instructions (as perfect as they were on MY computer (2013), her ribbon doesn't look anything like mine.  So now she has a Data tab and only a few options underneath that.  Is it possible for you to update your notes for a 2016 environment?  I tried many things and didn't get close to successful.  ugh! When you have time.  Thanks so much once again! 

[#10]

Hi Sue

Show Pane is now Show Queries

Data Source Settings is now at the bottom of the list after clicking New Query.

Other things should be the same

[#11]

Thanks - I will give it a try tomorrow!