Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 331
Default How To Combine Several Rows of Data into 1 Row of Data

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 331
Default How To Combine Several Rows of Data into 1 Row of Data

And, pretend that the example formatted correctly, and the 4 and 2 are under
the A column, the 3 is under the B column, and the 1 and 1 are under the C
column.

The example as a header row, and then 5 rows of data and 5 columns of data.
Thanks,

Greg

"Greg" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default How To Combine Several Rows of Data into 1 Row of Data

Greg

You can try the below..

1. Select the range in Col A and B including the header.
2. DataFilterAdvanced FilterCopy to another location
3. In copy to I have selected G1 and check 'Unique records only'
4. Click OK will give you the unique list of year and title in Col G and H.
5. Now place the headers A,B,C in I1, J1, K1.
6. In I2 apply the below formula

=SUMPRODUCT(--($A$2:$A$100=$G2),--($B$2:$B$100=$H2),--(C$2:C$100))

Copy that to J2 and K2
Drag down as required.

If this post helps click Yes
---------------
Jacob Skaria


"Greg" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default How To Combine Several Rows of Data into 1 Row of Data

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
DataFilterAutofilter 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
---------------
Jacob Skaria


"Greg" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 331
Default How To Combine Several Rows of Data into 1 Row of Data

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

"Jacob Skaria" wrote:

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
DataFilterAutofilter 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
---------------
Jacob Skaria


"Greg" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 331
Default How To Combine Several Rows of Data into 1 Row of Data

Jacob,

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

Greg

"Jacob Skaria" wrote:

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
DataFilterAutofilter 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
---------------
Jacob Skaria


"Greg" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I combine multiple rows with like data in Excel? CAW@NG&G Excel Discussion (Misc queries) 2 May 22nd 07 06:47 PM
Combine Data from Multiple Rows MR Excel Discussion (Misc queries) 1 January 24th 07 07:44 PM
How do I combine data from separate Excel rows Pete New Users to Excel 2 October 19th 06 07:03 PM
Combine the data in 2 columns of 20 rows into one column of 40 row Tom Excel Discussion (Misc queries) 6 May 3rd 06 09:27 AM
Combine data rows in Pie Chart Jayntree Charts and Charting in Excel 1 March 16th 06 10:15 AM


All times are GMT +1. The time now is 12:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"