Need to manage address book in excel

[smotwani]

I have an address book that has about 265 people. This address book needs to be created in a specific format in excel which I will then convert to a .ldif file. In one table I have the names and email addresses of individuals and I need those names and email addresses to appear after certain characters and at specific locations in the excel file for it to be correctly exported to .ldif format.

For example the table has Falcao, Tony as the name and [email protected] as the email and has Li, Amy and [email protected] as the second entry in the table. (The table is on a separate excel sheet)

The other excel sheet looks like this:

A1 dn:cn=,id=268
A2 cn:
A3 givenname:-----
A4 mail:
A5 objectclass:top
A6 objectclass:person
A7
A8 dn:cn=,id=269
A9 cn:
A10 givenname:-----
A11 mail:
A12 objectclass:top
A13 objectclass:person

and continues....

A1,A2, A3, etc are just the column names in the spread sheet.

So I need to grab information from the table that has the name and email and fill it in the A1, A2 etc fields so that I don't have to manually enter it for 265 people...

The final product should look like this.

A1 dn:cn=Falco, Tony,id=268
A2 cn:Falco, Tony
A3 givenname:-----
A4 mail:[email protected]
A5 objectclass:top
A6 objectclass:person
A7
A8 dn:cn=Li, Amy,id=269
A9 cn:Li, Amy
A10 givenname:-----
A11 mail:[email protected]
A12 objectclass:top
A13 objectclass:person

Any help is appreciated, not sure if there's a formula to do this or VBA script.

I have attached a sample file just to give you guys a better idea of what it looks like.

Thanks for the help!

[Olly]

Try this:

Please Login or Register  to view this content.

I haven't referenced worksheets (so it will run on the activesheet), and I've put the output in column 4 (D) so you can check against required output. I'm sure you can tweak as needed.

[smotwani]

Thanks a lot, I will try this at work tomorrow and let you know. Thanks once again.

[smotwani]

Thanks a lot worked like a charm!

[Olly]

Thanks a lot worked like a charm!

Excellent, pleased to hear it!