Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenating multiple rows into a single row
I am using Excel 2007. My data consists of rows with identical information
except for date values. I want to concatenate the rows into a single row so that I can see, along with the other information, the date values in the single row. Here is an example: MemberID DataDate 16 6/18/2009 16 3/26/2009 Here is the desired format: Member ID DataDate1 DataDate2 16 3/26/2009 6/18/2009 Is this possible with Excel '07? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
concatenating multiple rows into a single row
Here's one simple formulas model which should deliver it (irrespective
version) .. Assume source data as posted running in A2:B2 down in Sheet1 Assume your MemberIDs are real numbers from 1 to 20 (say) In Sheet1, In F2: =IF($A2="","",IF($A2=COLUMNS($A:A),ROW(),"")) Copy F2 across by 20 cols to Y2 (since you have 20 MemberIDs), then fill down as far as required to cover the max expected extent of source data in cols A and B, say down to Y100? In Sheet2, You have the MemberIDs 1-20 running in A2 down In B2: =IF(COLUMNS($A:A)COUNT(OFFSET(Sheet1!$E:$E,,ROWS( $1:1))),"",INDEX(Sheet1!$B:$B,SMALL(OFFSET(Sheet1! $E:$E,,ROWS($1:1)),COLUMNS($A:A)))) Copy B2 across & fill down as far as required to return the desired results, all neatly packed to the left. Format as dates to taste. Success? Celebrate it, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "ssheth" wrote: I am using Excel 2007. My data consists of rows with identical information except for date values. I want to concatenate the rows into a single row so that I can see, along with the other information, the date values in the single row. Here is an example: MemberID DataDate 16 6/18/2009 16 3/26/2009 Here is the desired format: Member ID DataDate1 DataDate2 16 3/26/2009 6/18/2009 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple rows to single row | Excel Discussion (Misc queries) | |||
Moving data from multiple rows to single rows | Excel Worksheet Functions | |||
Moving data from multiple rows into a single row | Excel Discussion (Misc queries) | |||
HELP!! ARRANGE MULTIPLE ROWS IN A SINGLE ROW | Excel Worksheet Functions | |||
Multiple rows from a single row | Excel Discussion (Misc queries) |