Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Arranging single-row records across multiple rows
My toddler's playgroup needs to arrange worksheet data in a bizarre fashion
(one record per every 4 lines) from a conventional worksheet table with one record per row. Specifically... we need to take firstname, middle names, surname and preferred name from consecutive columns of our main worksheet (e.g. B2,C2,D2,E2)... and produce a sheet formatted so that the first column reads as follows: firstname (from B2) middlename (from C2) surname (from D2) preferred name (from E2) firstname (from B3) middlename (from C3) surname (from D3) preferred name (from E3) Repeat for up to 50 records... Sadly, that's just the start of it: I've then got to get an address in one of the adjacent columns (again over 4 rows per record) and calculated data in other columns. Pivot table doesn't really seem to help... and whilst I'm happy to work my way through help files... I don't even know where to start looking! Greg. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Arranging single-row records across multiple rows
What you need is a formula which returns when entered in a cell and copied
down... assuming you have your names in Sheet1 Sheet1!B2 Sheet1!C2 Sheet1!D2 Sheet1!E2 Sheet1!B3 Sheet1!C3 Sheet1!D3 Sheet1!E3 You can put an INDIRECT around it and get what you want... One crude way is to enter on Sheet2 B in E1 C in E2 D in E3 E in E4 and repeat the pattern Then enter this in F1 =I1&(ROUNDDOWN((ROW()-1)/4,0)+2) and copy down Then in B2 you can have =INDIRECT("Sheet1!" & J1) and copy down I am sure someone else will come up with an elegant formula...sot hat you can put everything in INDIRECT ... -- If you find this post helpful pl. choose "Yes"... "Greg Spencer" wrote: My toddler's playgroup needs to arrange worksheet data in a bizarre fashion (one record per every 4 lines) from a conventional worksheet table with one record per row. Specifically... we need to take firstname, middle names, surname and preferred name from consecutive columns of our main worksheet (e.g. B2,C2,D2,E2)... and produce a sheet formatted so that the first column reads as follows: firstname (from B2) middlename (from C2) surname (from D2) preferred name (from E2) firstname (from B3) middlename (from C3) surname (from D3) preferred name (from E3) Repeat for up to 50 records... Sadly, that's just the start of it: I've then got to get an address in one of the adjacent columns (again over 4 rows per record) and calculated data in other columns. Pivot table doesn't really seem to help... and whilst I'm happy to work my way through help files... I don't even know where to start looking! Greg. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Arranging single-row records across multiple rows
Thanks for your feedback...
DO post back if you comeup with an elegant solution. You may try this macro This will work upto 65K/4 rows of data '------------------------------- Sub copyname() Dim i As Integer Dim j As Integer Dim k As Integer Dim LastRow LastRow = Sheets("Sheet2").Cells(65536, "B").End(xlUp).Row MsgBox LastRow k = 2 For i = 2 To 100 For j = 2 To 5 Sheets("sheet1").Cells(k, 2).Value = Sheets("Sheet2").Cells(i, j).Value k = k + 1 Next j Next i End Sub '------------------------------- -- If you find this post helpful pl. choose "Yes"... "Greg Spencer" wrote: My toddler's playgroup needs to arrange worksheet data in a bizarre fashion (one record per every 4 lines) from a conventional worksheet table with one record per row. Specifically... we need to take firstname, middle names, surname and preferred name from consecutive columns of our main worksheet (e.g. B2,C2,D2,E2)... and produce a sheet formatted so that the first column reads as follows: firstname (from B2) middlename (from C2) surname (from D2) preferred name (from E2) firstname (from B3) middlename (from C3) surname (from D3) preferred name (from E3) Repeat for up to 50 records... Sadly, that's just the start of it: I've then got to get an address in one of the adjacent columns (again over 4 rows per record) and calculated data in other columns. Pivot table doesn't really seem to help... and whilst I'm happy to work my way through help files... I don't even know where to start looking! Greg. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Arranging single-row records across multiple rows
There is a bug in the formula... it misses few entries like D6, E6...
-- If you find this post helpful pl. choose "Yes"... "Sheeloo" wrote: What you need is a formula which returns when entered in a cell and copied down... assuming you have your names in Sheet1 Sheet1!B2 Sheet1!C2 Sheet1!D2 Sheet1!E2 Sheet1!B3 Sheet1!C3 Sheet1!D3 Sheet1!E3 You can put an INDIRECT around it and get what you want... One crude way is to enter on Sheet2 B in E1 C in E2 D in E3 E in E4 and repeat the pattern Then enter this in F1 =I1&(ROUNDDOWN((ROW()-1)/4,0)+2) and copy down Then in B2 you can have =INDIRECT("Sheet1!" & J1) and copy down I am sure someone else will come up with an elegant formula...sot hat you can put everything in INDIRECT ... -- If you find this post helpful pl. choose "Yes"... "Greg Spencer" wrote: My toddler's playgroup needs to arrange worksheet data in a bizarre fashion (one record per every 4 lines) from a conventional worksheet table with one record per row. Specifically... we need to take firstname, middle names, surname and preferred name from consecutive columns of our main worksheet (e.g. B2,C2,D2,E2)... and produce a sheet formatted so that the first column reads as follows: firstname (from B2) middlename (from C2) surname (from D2) preferred name (from E2) firstname (from B3) middlename (from C3) surname (from D3) preferred name (from E3) Repeat for up to 50 records... Sadly, that's just the start of it: I've then got to get an address in one of the adjacent columns (again over 4 rows per record) and calculated data in other columns. Pivot table doesn't really seem to help... and whilst I'm happy to work my way through help files... I don't even know where to start looking! Greg. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Arranging single-row records across multiple rows
It is not my day today...
It will work for 100 rows unless your replace 100 by LastRow. You can delete the MsgBox row -- If you find this post helpful pl. choose "Yes"... "Sheeloo" wrote: Thanks for your feedback... DO post back if you comeup with an elegant solution. You may try this macro This will work upto 65K/4 rows of data '------------------------------- Sub copyname() Dim i As Integer Dim j As Integer Dim k As Integer Dim LastRow LastRow = Sheets("Sheet2").Cells(65536, "B").End(xlUp).Row MsgBox LastRow k = 2 For i = 2 To 100 For j = 2 To 5 Sheets("sheet1").Cells(k, 2).Value = Sheets("Sheet2").Cells(i, j).Value k = k + 1 Next j Next i End Sub '------------------------------- -- If you find this post helpful pl. choose "Yes"... "Greg Spencer" wrote: My toddler's playgroup needs to arrange worksheet data in a bizarre fashion (one record per every 4 lines) from a conventional worksheet table with one record per row. Specifically... we need to take firstname, middle names, surname and preferred name from consecutive columns of our main worksheet (e.g. B2,C2,D2,E2)... and produce a sheet formatted so that the first column reads as follows: firstname (from B2) middlename (from C2) surname (from D2) preferred name (from E2) firstname (from B3) middlename (from C3) surname (from D3) preferred name (from E3) Repeat for up to 50 records... Sadly, that's just the start of it: I've then got to get an address in one of the adjacent columns (again over 4 rows per record) and calculated data in other columns. Pivot table doesn't really seem to help... and whilst I'm happy to work my way through help files... I don't even know where to start looking! Greg. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving data from multiple rows to single rows | Excel Worksheet Functions | |||
How do I combine multiple records for a single item? | Excel Discussion (Misc queries) | |||
How do I combine multiple records for a single item? | Excel Discussion (Misc queries) | |||
to make a single row record become multiple row records | Excel Worksheet Functions | |||
arranging excel tabs (charts vs worksheets)in multiple rows/dimens | Charts and Charting in Excel |