Need Help Creating Macro

March 16, 2009 at 13:50:18
Specs: Windows XP
Hello, I have been given a project at work to take approximately 1000+ emails that have been pooling for a costumer of mine and entered the data into a spreadsheet format. The data that is coming to me looks like this...

Name: John Dow
Address: 123 Apple Creek
Date: Sat Nov 15 18:50:30 CST 2008
City: Sample
State/Province: TN
Zip: 12345
Country: USA
Publication: abcbooklet

The spreadsheet just needs to have the Name in one column, Address in another column and so on. What I am thinking is coping all the emails into a blank spreadsheet (Spreadsheet 1-Column A). Then create a macro that would look in Column A for anything starting with "Name:" if it is found move that cell to Spreadsheet 2 - Column A. Next the macro would look for anything in Column A (Spreadsheet 1) that starts with "Address:" if it is found move that cell to Spreadsheet 2 - Column B. Then it would continue the same process for the rest.

Please let me know if you have any ideas, or any other thoughts on how I could create this.



See More: Need Help Creating Macro

Report •

March 16, 2009 at 14:35:02
Your idea will work, but if the data comes to you exactly as described, and is consistant, you can do it without a macro.

I just "columnized" your example data from Sheet1 to Sheet2 with 2 formulas and a little dragging to autofill.

I won't go into the gory details unless you can assure me that the data for the 1000+ email will be laid out exactly as you described.

Report •

March 16, 2009 at 15:30:48
Thank you for replying...Yes, the layout is exactly the same as above. The only thing is that I need to make sure that John Dow's address doesn't somehow get switched with Jane Dow's address.


Report •

March 16, 2009 at 20:28:09
OK, I took three formulae.

Here's what I did:

I copied your data into Column A, starting in A1.
In B1 I put:


I then dragged this formula down along side the data to get this in Column B:

John Dow
123 Apple Creek
Sat Nov 15 18:50:30 CST 2008

In A1 of Sheet2 I put:


In A2 I put =A1+9 and dragged this down, so that Column A showed the row number from Sheet1 that contained the first piece of each data set (Name:)


In Sheet2!B2 I put:

=INDIRECT("Sheet1!B" & COLUMN()+$A1-2)

Drag this across to Column J to bring in the first set of data.
Drag the formulae from B1:J1 down to bring in each subsequent data set.

Once it's all in Sheet2, do a Copy...Paste Special...Values to eliminate the formulae and leave the text.

That was kind of fun..

Report •

Related Solutions

Ask Question