Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine multiple rows with like data in Excel? | Excel Discussion (Misc queries) | |||
Combine Data from Multiple Rows | Excel Discussion (Misc queries) | |||
How do I combine data from separate Excel rows | New Users to Excel | |||
Combine the data in 2 columns of 20 rows into one column of 40 row | Excel Discussion (Misc queries) | |||
Combine data rows in Pie Chart | Charts and Charting in Excel |