Moving data from multiple rows to single rows

[Pete]

In Excel 2007 -

I have data organized in rows under headings (Account, First name, Last
name, Title...) for multiple contacts within each account. I need to move all
contacts for each company into one single row for that company (multiple
contacts for each company in one row ( First name 1, Last name 1...First name
2, Last name 2....) can this be done easily?

This.....

Account First name Last name Title
ABC company Donna Smith Manager
ABC company Karen Jones VP, Media

To this:

Account First name Last name Title First name Last
name Title
ABC company Donna Smith Manager Karen Jones VP

 

[Max]

Think what you have is a better structure than what you intend to do. Your
current data lends itself easily to autofilter, pivot table analysis, etc.

 

[Pete]

Understood but I need it in this format in order to import it into a CRM
solution that requires it...any ideas?

 

[Max]

Suggest you try posting in .programming if nobody else drops by here.

 

[Max]

Came up with a formulas set-up which might work for you
Illustrated in this sample:
http://www.freefilehosting.net/download/3c52i
Moving multiple rows data to single rows.xls

Source data assumed in sheet: x, cols A to D, data from row2 down

In x,
In E2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
Copy E2 down to cover the extent of source data, eg to E100

In F1:
=IF(COLUMNS($A:A)>COUNT($E:$E),"",INDEX($A:$A,SMALL($E:$E,COLUMNS($A:A))))
Copy F1 across as far as required to extract the unique list of accounts, eg
to IV1

In F2: =IF($A2="","",IF($A2=F$1,ROW(),""))
Copy F2 across to IV2, fill down to IV100 to populate

In another sheet: y,
In A2: =IF(ROWS($1:1)>COUNTA(x!$F$1:$IV$1),"",INDEX(x!$F$1:$IV$1,ROWS($1:1)))

In B2:
=IF(ISERROR(SMALL(OFFSET(x!$E:$E,,MATCH($A2,x!$F$1:$J$1,0),),INT((COLUMNS($A:A)-1)/3)+1)),"",INDEX(OFFSET(x!$B:$B,,MOD(COLUMNS($A:A)-1,3),),SMALL(OFFSET(x!$E:$E,,MATCH($A2,x!$F$1:$J$1,0),),INT((COLUMNS($A:A)-1)/3)+1)))
Copy B2 across as far as required to cover the expected extent, eg to J2.
Select A2:J2, fill down as far as required for the desired results.

Adapt to suit the extent of your Excel/as required.

 

[Max]

Slight amendment ..

In sheet: y,

In B2 should be
=IF(ISERROR(SMALL(OFFSET(x!$E:$E,,MATCH($A2,x!$F$1:$IV$1,0),),INT((COLUMNS($A:A)-1)/3)+1)),"",INDEX(OFFSET(x!$B:$B,,MOD(COLUMNS($A:A)-1,3),),SMALL(OFFSET(x!$E:$E,,MATCH($A2,x!$F$1:$IV$1,0),),INT((COLUMNS($A:A)-1)/3)+1)))

The range "x!$F$1:$J$1" should be extended to col IV limit: x!$F$1:$IV$1
for consistency

---