Sorry if this is so specific.
I have a list of contact info; it is 9905 lines of text I need to convert into a spreadsheet. The info was in PDF form, I imported to Excel and used conditional formatting to highlight the phone numbers, fax, email and web addresses; so it would be easier to be sure I was getting the info to the correct place.
I need a macro to help me transpose the info into a new sheet in the workbook. Each contact entry is about 10 lines then it skips a line and the next entry is about 10 lines. I need a marco to transpose the 10 lines or 11 lines across instead of down; but I need it to run all 9905 lines of data at once.
Everything I have found in other forums are limited to only a few lines.
Can anyone help me? PLEASE?! I am desperate! Oh I am using Excel 2010
Thanks!
Last edited by mtc; 11-23-2011 at 12:28 PM.
hi mtc, as you have not posted sample workbook with your data, I will post mine with the data as per your description, run code "test". Output is on the same page starting from E1
Thanks that is exactly what I need! However my samples are either too spaced or have no spaces. Please see attached samples on sheets 1 & 2.
Thanks!
Meghan
Last edited by mtc; 11-22-2011 at 11:04 AM.
hi Meghan, are they mixed: some with spaces some w/o?
I really would like to be able to add a space below any cell that says "Web Site:" then run your macro. I cannot figure out how to add a space below only cells that read Web Site:
I do have a sheet that has spaces between the name and address and the phone/email/web info. However they are not constant.
Does that make sense?
can I ask you to post real data sample of around 200 rows?
WHile I approciate your help I cannot share the data. I have figured out how to insert the row I just need to figure out how to move all the cells below into the blank cell.
I have:
Option Explicit
But I cannot figure out how to loop it through all 8K lines. Can you help me with that?Sub MoveTheCell()
Dim rngFound As Range
Set rngFound = Cells.Find(What:="Web Site", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
rngFound.Cut Destination:=rngFound.Offset(-1)
Set rngFound = Nothing
End Sub
Thanks!
Meghan
question: do you need that web site line and its data?
please check attachment, run code "test"
It works beautifully in your test! Writing these totaly baffles me! I know what I need but have no idea how to implement it!
When I bring it into my sheet it takes a really long time to run. Should it take so long?
revised code, comments added, please check attachment
Edit: this should work quicker
Last edited by watersev; 11-22-2011 at 04:29 PM.
I am so sorry - it is giving me aI also noticed that Dr. is another variable aside from Mr., Ms., Mrs. in my data.Run-time error '9':
Subscript out of range
I am so appreciative for the help!
I suppose the problem is that data is not consistent so Web Site is missing in some cases.
Please try this attachment with a different approach. The code defines data blocks by name titles: Mr, Ms, Mrs, Dr. The list can be extended. All the data in between titles will be taken to appropriate fields. As I can operate with the sample data provided only, I've checked the code on your sample data extrapolated to 10K rows. For now it looks good. I hope it will be the same on your data as well. In case you have any further questions, feel free to ask. Comments are within the code. The data is supposed to be from A1 and down.
THANK YOU, THANK YOU, THANK YOU!
It works great! And thanks for putting the comments in there so that I can see how the macro is working. I have been trying to teach myself but clearly I am not at all experienced!
I so much appreciate you sharing your genius with me!
Thanks again!
How would you transpose such a list to Horizontal?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks