| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to convert some columns to rows
Hi,
I would like to build a macro to convert a table from rows to columns. The initial spreadsheet is set up so that each row represent one piece of information for the same ID. For each ID there are 5 rows A B C ID1 Name Smith ID1 Address 22 Smith rd. ID1 Date of Birth 1987/12/07 ID1 Country of Birth Canada ID1 Nationality Canadian ID2 Name Brown ID2 Address xxBrown st. ID2 Date of Birth 1965/01/12 ID2 Country of Birth USA ID2 Nationality Canadian The new worksheet should display the information by ID while the attributes listed in column B should be displayed in the Header row (as Column Headings) ID Name Address Date of Birth Country of Birth Nationality ID1 Smith XX Smith rd 1987/12/07 Canada Canadian ID2 Brown XX Brown rd 1965/01/12 USA Canadian |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to convert some columns to rows
Don't need a macro - Someone else was kind enough to post this answer
previously (pretty cool). This assumes the information starts in C2 - not C1 has might be assumed in your example.... =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:C),) in example column "F" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:D),) in example column "G" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:E),) in example column "H" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:F),) in example column "I" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:G),) in example column "I" Copy down as needed - this has been tested and does work..... -- Wag more, bark less "T.Vidak" wrote: Hi, I would like to build a macro to convert a table from rows to columns. The initial spreadsheet is set up so that each row represent one piece of information for the same ID. For each ID there are 5 rows A B C ID1 Name Smith ID1 Address 22 Smith rd. ID1 Date of Birth 1987/12/07 ID1 Country of Birth Canada ID1 Nationality Canadian ID2 Name Brown ID2 Address xxBrown st. ID2 Date of Birth 1965/01/12 ID2 Country of Birth USA ID2 Nationality Canadian The new worksheet should display the information by ID while the attributes listed in column B should be displayed in the Header row (as Column Headings) ID Name Address Date of Birth Country of Birth Nationality ID1 Smith XX Smith rd 1987/12/07 Canada Canadian ID2 Brown XX Brown rd 1965/01/12 USA Canadian |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to convert some columns to rows
Hi Brad, thanks this is great and it works but is there a way of adding an
additional function to this formula so that I can be sure I that I am picking up the right information for the right IDwhen creating the new worksheet? Thanks... "Brad" wrote: Don't need a macro - Someone else was kind enough to post this answer previously (pretty cool). This assumes the information starts in C2 - not C1 has might be assumed in your example.... =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:C),) in example column "F" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:D),) in example column "G" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:E),) in example column "H" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:F),) in example column "I" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:G),) in example column "I" Copy down as needed - this has been tested and does work..... -- Wag more, bark less "T.Vidak" wrote: Hi, I would like to build a macro to convert a table from rows to columns. The initial spreadsheet is set up so that each row represent one piece of information for the same ID. For each ID there are 5 rows A B C ID1 Name Smith ID1 Address 22 Smith rd. ID1 Date of Birth 1987/12/07 ID1 Country of Birth Canada ID1 Nationality Canadian ID2 Name Brown ID2 Address xxBrown st. ID2 Date of Birth 1965/01/12 ID2 Country of Birth USA ID2 Nationality Canadian The new worksheet should display the information by ID while the attributes listed in column B should be displayed in the Header row (as Column Headings) ID Name Address Date of Birth Country of Birth Nationality ID1 Smith XX Smith rd 1987/12/07 Canada Canadian ID2 Brown XX Brown rd 1965/01/12 USA Canadian |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro to convert some columns to rows
Yes, using basically same formula as before
=OFFSET($A$1,ROWS($2:2)*5-5+COLUMNS(A:$A),) Will get you what you need - there are other ways to do this but this would be consistent with what you have.... -- Wag more, bark less "T.Vidak" wrote: Hi Brad, thanks this is great and it works but is there a way of adding an additional function to this formula so that I can be sure I that I am picking up the right information for the right IDwhen creating the new worksheet? Thanks... "Brad" wrote: Don't need a macro - Someone else was kind enough to post this answer previously (pretty cool). This assumes the information starts in C2 - not C1 has might be assumed in your example.... =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:C),) in example column "F" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:D),) in example column "G" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:E),) in example column "H" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:F),) in example column "I" =OFFSET($C$1,ROWS($2:2)*5-5+COLUMNS($C:G),) in example column "I" Copy down as needed - this has been tested and does work..... -- Wag more, bark less "T.Vidak" wrote: Hi, I would like to build a macro to convert a table from rows to columns. The initial spreadsheet is set up so that each row represent one piece of information for the same ID. For each ID there are 5 rows A B C ID1 Name Smith ID1 Address 22 Smith rd. ID1 Date of Birth 1987/12/07 ID1 Country of Birth Canada ID1 Nationality Canadian ID2 Name Brown ID2 Address xxBrown st. ID2 Date of Birth 1965/01/12 ID2 Country of Birth USA ID2 Nationality Canadian The new worksheet should display the information by ID while the attributes listed in column B should be displayed in the Header row (as Column Headings) ID Name Address Date of Birth Country of Birth Nationality ID1 Smith XX Smith rd 1987/12/07 Canada Canadian ID2 Brown XX Brown rd 1965/01/12 USA Canadian |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert columns to rows | Excel Worksheet Functions | |||
| Convert rows to columns | Excel Worksheet Functions | |||
| Convert columns to rows | Excel Worksheet Functions | |||
| to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
| Convert rows to columns | Excel Worksheet Functions | |||
Linear Mode
