How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9655
Experience:  Worked on Macs and PC's for 20+ years.
53785
Type Your Mac Question Here...
John D is online now
A new question is answered every 9 seconds

1. I opened a Mac Address Book file in Excel. 2. The data is

Customer Question

1. I opened a Mac Address Book file in Excel. 2. The data is not in columns. 3. The spreadsheet contains data that i don't want. 4. There are over 3,900 records taking up to 8 rows, depending on what users have entered over the years. 5. I have spent hours going through row by row, moving first and last name into columns (on a single row) and then moving the email address to its column in the same row as the names. BIG QUESTION: IS THERE A FASTER WAY TO REMOVE ALL THE UNWANTED DATA? Hope you can help. Gai
Submitted: 2 years ago.
Category: Mac
Expert:  John D replied 2 years ago.

Hi,

 

Yes if there is a logic in the way the address lines are entered. Please send me the file so I can check the fields. To do so go to http://www.wikisend.com/ and upload the file (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

 

If the file has sensitive information let me know before you upload it

 

Customer: replied 2 years ago.
Hi, John D.
Sadly, there really isn't any logic. There is a template for each address (a vcard in Address Book), but not all fields are used and users can easily add more fields and even change field names.
I hoped there would be a way I could remove all of the ROWS that have ADDR or TEL (and others) as the first letters, but haven't been able to work out a way to do that. Was hoping for a version of search & replace that would delete the entire row.
Gai
Expert:  John D replied 2 years ago.

It's ok. Please send me the file and I will try to find a pattern

 

Customer: replied 2 years ago.
John D,
I keep getting the message" We are sorry, but an error has occured while uploading.

You can return to the start page and try again.

In the meantime, do you know of a way I can download my Address Book vCard/archive in COLUMNS? Earlier this week, I was instructed to save the archive (.abbu) to a flash drive, and open it in Excel from my Windows computer. Mac help desk advised that it is not possible to convert .abbu files to either Excel or .csv.
So I successfully opened the Mac .abbu file and saved it as both Excel and .csv. But data is spread all over the place like a dog's breakfast!
Expert:  John D replied 2 years ago.

Ok let's first get file through

 

Ok try uploading it here, then send me the Share URL link

 

http://www.filesharesite.com/

 

 

Customer: replied 2 years ago.
http://www.filesharesite.com/files/201203/1331850729Excel_all_address_book_entries.xlsx.html

Let me know that it's ok.
Expert:  John D replied 2 years ago.

Got it, thanks

 

Gai, yes I can create a macro that will transpose each address into 13 columns, but since not all the address have the same number of rows the listing may need be in the same columns.

 

e.g. if address 1 has 13 rows, the fields will be placed on anther sheet in columns A thru M, starting in A

 

and if the next address has 10 rows, it will be placed in columns A thru J starting form A

and so on

 

 

Would that be ok

 

Customer: replied 2 years ago.
You know what, I only need a couple of ROWS for each entity (between BEGIN:CARD and END:CARD) ....
N; (multiple columns for first, last, sometimes middle initial, sometimes a suffix, sometimes a Mr. or Ms. etc.)
EMAIL

Does that make it easier?
Expert:  John D replied 2 years ago.

Ok I wrote a macro (and some formulas) and was able to process all 36,000 rows so that each address is transposed into a single a row. Here is a screenshot showing the result of the 50 addresses

 

 

graphic

 

 

Have a look and let me know if it is ok so I can send you the complete list in the Excel file


Expert:  John D replied 2 years ago.

please refresh this page to see the updated screenshot

 

Customer: replied 2 years ago.
Hi John D,
Thanks for revised screenshot. It looks as if the last name (in those fields that begin with N: ) hasn't been included.
Expert:  John D replied 2 years ago.

Can you give me an example. Which row on your original list?

 

 

Expert:  John D replied 2 years ago.

Ok I see what you mean.

 

I assumed that column A is all titles. But apparently it has some address related data.

 

Ok will fix that

 

Customer: replied 2 years ago.
It sure would have been nice if the address book data had separated from its fieldnames! Thanks for keeping on this one for me.
Expert:  John D replied 2 years ago.

I will try do that if the field names are XXXXX XXXXX have to create another macro for that

 

Customer: replied 2 years ago.
No .... I didn't mean for you to do it. I meant it would have been easier if the archive from Address Book was formatted differently.
Expert:  John D replied 2 years ago.

lol.. already wrote that macro and it is now possessing the data...

 

The two macros will take some 20-30 mins to complete transposing and changing all the data

 

Will get back to you as soon as I am done

 

 

Customer: replied 2 years ago.
You are amazing. Thank you.
Expert:  John D replied 2 years ago.

Whew!!

 

It is taking a lot of time processing all the data. In the meantime here is the initial list after running two macros, 10 sets of formulas, and plenty of manual modifications. It may not be perfect but I think it is as close it can get with the given time and scope

 

Attachment: 2012-03-16_023725_output_test.xlsx

 

Hope this is ok. Let me know what know what you think

 

 

 

 

 

Customer: replied 2 years ago.
Hello John D, Thank you for all the work you are putting into my plea for help.

Sorry I didn't reply last night. I'd changed my email to my home address, and kept checking there. obviously that change didn't happen and this morning we reconnected via work email.

I appreciate your efforts, but the report sample can't be uploaded to iContact.
I only need the names and the email data for each entity. This information is in the file I sent yesterday:

the name fields are identified by N:aaaaaaaaa in the cell in column A.

the email fields are in another row that always contains EMAIL in column A.

Could you extract just that data and use it to create a new, smaller table? For example:

For each contact, a single record begins where BEGIN:VCARD is in column A and a record ends where END:VCARD is in column A.

1. where the text in the cell in column A = N:aaaaaaaaaa (the last name appears after N:)
can you extract the first five cells (A:E) and paste them in a row in columns A:E a new report?

2. where the text in the cell in column A = EMAIL
can you extract the first three cells (A:C) and past them in the same row, in columns F:H in the new report?
JOHN, NOT EVERY CONTACT HAS A ROW FOR EMAIL. So just go to the next contact, please.

I work two jobs and need to leave for my second job in a few minutes. I will not have access to a computer. If you have questions before 2 pm, can you call or text me? XXX-XXX-XXXX.

Thank you again for looking for a solution.
Sincerely,
Gai
Expert:  John D replied 2 years ago.

Hi Gai,

 

Not sure how else to send you the file (we cannot use email on this site). Will try other methods. In the meantime here is what the final list looks like

 

graphic

Customer: replied 2 years ago.
Hello again.
There is a text message option and I just signed up.

John, early in our conversation I stated that I wanted to remove most of the rows. You have made a report out of the huge amount of data, but I still can't use it.
Please let me know whether you can remove all the data I do not need, to create a much smaller report.

Thanks, Gai
Expert:  John D replied 2 years ago.

Yes I think I can do that, but first let's get this one accepted which is per your original instructions so I can get some credit for my time on this rather extended question

 

Thank you

 

 

 

John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9655
Experience: Worked on Macs and PC's for 20+ years.
John D and 3 other Mac Specialists are ready to help you
Customer: replied 2 years ago.
Hi John
I'm at my office job now. I don't want to get into a debate and certainly an aware of the fact that you've spent a lot of time figuring out ways to sort the data. So I will hit the accept button for you.

Will you keep online or send me some instructions?

I want to upload the data to iContact this weekend. The format has to be: names and email addresses on one row per contact. Columns A:E for name and F:G for email (if there is one).

Thanks again,
Gai
Expert:  John D replied 2 years ago.

Yes Gai I will be online most of the time today

 

Thanks

 

 

 

 

 

 

Expert:  John D replied 2 years ago.

Thanks Gai. Much appreciated.

 

I will start working on the name/email address shortly. Will get back to you as soon as I am done

 

 

 

Customer: replied 2 years ago.
THANK YOU!
Expert:  John D replied 2 years ago.

no prob

 

Expert:  John D replied 2 years ago.

For example in row 24, the formula in FT24 counts the number of cells in the range E24 thru FM24 that are larger that C24

 

 

Customer: replied 2 years ago.
John ... is this question for another client?
Expert:  John D replied 2 years ago.

Oops!. You are correct.

 

Please ignore it, and thanks for letting me know

 

I should have the list ready in about 15 mins

 

 

Customer: replied 2 years ago.
W O W !
Expert:  John D replied 2 years ago.

Ok here is the file with the names and email for all the addresses. Please note that some names have more than one email address associated with them

 

http://www.filesharesite.com/files/201203/1331933352output1.xlsx.html

 

Please check it and let me know if it needs and adjustments

 

 

Customer: replied 2 years ago.
Hi John,
You are nearly there!

I still need the names separated for uploading to iContact and addressing some messages to first name.

For every row where column A = N:aaaaa (aaaaa is supposed to be the contact's last name)

can you extract the first five cells (A:E) and insert them into columns A:E the new report?
Sometimes there will only be one populated cell (in column A)--other cases use all five cells.
I will have to do some manual cleaning up, because there is no consistency.

Thanks,
Gai
Expert:  John D replied 2 years ago.

Gai, I am not sure I fully understand. Are you referring to the original data?. If so the final list is so far removed from that data, some 8 steps have been performed to transpose the data and extract the names and emails (the rows in the original list no more relate to the rows the final transposed and condensed list)

 

If it helps I can extract the first names and the last names from the last list and put them in separate columns

 

Let me know

 

 

Expert:  John D replied 2 years ago.

UPDATE:

 

Ok I managed to get all the names that are preceded by the "N:" character. They are placed in column A in this file

 

Attachment: 2012-03-16_230315_output2.xlsx

 

Hope this is ok

 

Customer: replied 2 years ago.
Thank you John.

I still need to have first names in their own column.
Can it be done?
If not, I will have to manually remove the last name and comma before the firstname and remove the comma and salutation from after the firstname.

Thank you very much for continuing to work on this today.

Gai
Expert:  John D replied 2 years ago.

Ok one moment please...

 

Expert:  John D replied 2 years ago.

Had to do most of extraction manually because of the inconsistency of the data

 

Here if the final list

 

 

Attachment: 2012-03-17_003813_output3.xlsx

 

Hope this helps

 

Thank you

 

 

Customer: replied 2 years ago.
Hello John,
My huge thanks for creating this report. This final report is exactly what I wanted.

What would you suggest I do differently next time, so I don't take up so much of your time/the expert's time? I thought my request was clear, but I accept the fact that you did not understand what I wanted.

Again, thank you for sticking with this on my behalf.
Sincerely,
Gai
Expert:  John D replied 2 years ago.

You're welcome Gai! Glad you are happy with the result

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
< Last | Next >
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
 
 
 

Meet The Experts:

 
 
 
  • Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
< Last | Next >
  • http://ww2.justanswer.com/uploads/macthelife/2009-10-20_1899_mikesebaharsquare64.jpg Mike's Avatar

    Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
  • http://ww2.justanswer.com/uploads/AS/ashiknasameen/2012-5-15_141836_final2.64x64.jpg Ashik's Avatar

    Ashik

    Mac Helper

    Satisfied Customers:

    5282
    7+ Years of Experience in troubleshooting Macs, iPhone, iPad, iPod etc
  • http://ww2.justanswer.com/uploads/DP/dpean/2012-6-6_172828_avatorme1.64x64.JPG Daniel's Avatar

    Daniel

    Mac Genius

    Satisfied Customers:

    4670
    Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.
  • http://ww2.justanswer.com/uploads/VI/vinodvmenon2005/1.64x64.jpg Vinod Menon's Avatar

    Vinod Menon

    Support Specialist

    Satisfied Customers:

    2068
    worked as a Tech support Associate for Apple products
  • http://ww2.justanswer.com/uploads/BE/beboo/2011-1-14_201648_n5063313142021801763.64x64.jpg Brandon M.'s Avatar

    Brandon M.

    Mac Support Specialist

    Satisfied Customers:

    1501
    10+ Years Mac Support as contractor and currently an IT Manager for law firm
  • http://ww2.justanswer.com/uploads/MA/MacDruid/IMG_0232.64x64.JPG John T. F.'s Avatar

    John T. F.

    Mac Druid

    Satisfied Customers:

    1408
    20+ years in the computer/Mac industry
  • http://ww2.justanswer.com/uploads/MA/MacHelpdesk/1d2d506.64x64.jpg David's Avatar

    David

    Mac Support Specialist

    Satisfied Customers:

    1236
    BSc, H.Dip, Apple Certified