combine multiple rows into one row

[javalover]

I have a directory of company names with multiple contacts. Currently it has one row per contact. Each company has a different amount of contacts. I need there to be only ONE row per company name, moving the contacts up to multiple columns.

Currently:
ABC Company, John Smith, President
ABC Company, Ted Wilson, VP
ABC Company, William Nelson, IT
NBC Company, Scott Jones, CEO
NBC Company, Jeff Anderson, CFO

Needs to be:
ABC Company, John Smith, President, Ted Wilson, VP, William Nelson, IT
NBC Company, Scott Jones, CEO, Jeff Anderson, CFO

Any ideas? (no code, please)
Thanks!

 

[Robert Mika]

If this part:
John Smith, President
is one cell then
Use this formula:

http://www.mrexcel.com/forum/showthread.php?p=2263339#post2263339


and then CONCATENATE everything into one cell.

 

[oldrelia23]

This is one way, but most likely there are many more elegant ways. I'm assuming that the name and title need to be concatenated into one cell.

- Create a unique list of the company names (easiest would be to use the advance data filter with unique filters) in a new sheet, say these are in A2:A10 for discussions sake.

- Have a column that counts how many entries there are for each company; I'd use this for the first row, but replace "list" with the column from the underlying data that houses the company name: {=sum(--("list"=$A2))}
and fill down, make sure list is row anchored.

- Determine what the max number of entries are and create "0 to max-1" column headers to the right of the company counter column

=if(F$1<"counter",OFFSET(INDEX("list of names",MATCH($A2,"list",0)),F$1,0)&", "&OFFSET(INDEX("list of titles",MATCH($A2,"list",0)),F$1,0),"")

F$1 is the first counter columns, make sure all "lists" are row and column anchored, and make sure the counter is column anchored

Fill down

Sorry if I didn't articulate this clearly

 

[javalover]

thanks for the link. somehow, I'm missing something. I changed the "8" in the formula to the amount of rows I have. It only grabbed one contact per row. I'm assuming I needed to populate column F ahead of time by removing duplicates from column A and then entering the array into column G. Can the array be copied all the way down?

scratching my head...please help. Thanks!

 

[oldrelia23]

yes you should populate F ahead of time. what 8 did you remove?

thanks

 

[javalover]

old relia23: I replaced four different "8's" in the formula, from the link provided by Robt Mika's reply, with the number of rows I have.

I will attempt your solution a bit later today, when I have more time. thanks for your help!