How To Combine Several Rows of Data into 1 Row of Data

[Greg]

Excel 2007 Student Edition:

How do I combine values from multiple rows about 1 item into 1 row about
that item?

For example:

Header: Year Title A B C
Row 1: 1997 Titanic 4
Row 2: 1997 Titanic 3
Row 3: 1997 Titanic 1
Row 4: 1999 Matrix 2
Row 5: 1999 Matrix 1

Into:

Header: Year Title A B C
Row 1: 1997 Titanic 4 3 1
Row 2: 1999 Matrix 2 1

I have about 3000 rows of items, and 40 columns of values, and I'd prefer
not to spend a whole day combining these by hand.

Thanks,

Greg

 

[Jacob Skaria]

Greg, considering your scenario of 3000 rows of data and 40 columns; we will
try out the same in a different sheet..(few changes to be noted)

1. Suppose you have data in Sheet1. From Sheet2 access the menu
Data>Filter>Autofilter and 'Copy to another location' .
In list range type Sheet1!A1:B3000
In copy to type/select cell A1

2. For copying headers use the below formula in Sheet2 C1
=Sheet1!C1 and copy the formula to 40 columns to the right

3. In cell C2 of Sheet2 apply below formula and copy that to right cols and
rows...

=SUMPRODUCT(--(Sheet1!$A$2:$A$3000=$A2),--(Sheet1!$B$2:$B$3000=$B2),--(Sheet1!C$2:C$3000))

If this post helps click Yes

 

[Greg]

Thanks,

I'll give this a try as soon as I've had some coffee and I'm awake enough to
follow the instructions correctly. Will definitely post of success or
failure. Hoping for success!

Greg

 

[Greg]

Jacob,

Many thanks! This worked wonderfully! Saved me many tedious hours of work!

Greg