Contact Data from tabular to row

dehmerl
dehmerl used Ask the Experts™
on
Hello,

I have an excel file that has contacts in it. I am looking for a way to extract and list each company name and the associated email address in a row, I am not sure how to do a formula for this. The example image show how the data is in tabular format and the results I am looking for.

Thanks,
example data
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
You could copy the relevant data for each company and use PasteSpecial ->  Transpose unless you have hundreds of Companies
Finance Analyst
Commented:
See attached file.

Result sheet currently allows up to 8 email contacts per company but can be copied further right if required.
Transpose-contact-info.xlsx
Neil FlemingConsultant and developer

Commented:
If there's a very large number of company names, then you might want to use the simple macro below to reduce the list to unique names.

In Rob's example, this would mean first copying column A from the Source sheet to the result sheet, Then switch to the result sheet and run macro

Sub ZapDuplicates()
Dim r As Range

Set r = ActiveSheet.Cells(1, 1).CurrentRegion
Set r = r.Resize(r.Rows.Count, 1)

r.RemoveDuplicates Array(1), xlYes

End Sub

Open in new window


You can then copy Rob's formula from Cell B2 throughout the worksheet from column B onwards.

Hope this helps

Author

Commented:
Thanks Rob and Co,

We have several hundred lines so this worked out very well.

Rgs,
Rob HensonFinance Analyst

Commented:
You can use Pivot Table to generate the list of unique company names

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial