Contact Data from tabular to row

dehmerl used Ask the Experts™

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.

example data
Watch Question

Do more with

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

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

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

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


Thanks Rob and Co,

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

Rob HensonFinance Analyst

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