| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Oh, where to start??? Reformatting HELP
I'm pretty clever with Regular Expressions and can generally reformat
just about any Excel data by dumping it into a program like EditPlus and running a ton of RegExp powered Find & Replace operations. However, this latest spreadsheet has me pulling my hair out. Here's what I have a b c d e f 1 job 1 doc # job 2 doc # ... ... 2 sop 1 sop001 sop 3 sop003 ... ... 3 sop2 sop002 sop 4 sop004 ... ... 4 sop 5 sop005 5 sop 6 sop006 Cells a1, c1, e1, etc is a job title Cells b1, d1, f1, etc is the static text 'Document Number' Cells a2, a3, a4, etc are names of policies for the job in a1 Cells b2, b3, b4, etc are the policy numbers for the jobs in column a There can be many jobs (a1, c1, e1, etc) and many policies per job. (but always 1 policy # for each policy) I need to get the data formatted as a b c 1 job 1 sop 1 sop001 2 job 1 sop 2 sop002 3 job 2 sop 3 sop003 4 ... ... ... 5 ... ... ... Any ideas? I came across David McRitchie's RotateCW macro, which allowed me to pivot the table by 90 deg CCW (a bit easier to use RegExp's on) but now Im left with alternating rows of policy #'s and policies: a b c d 1 doc # sop001 sop002 2 job 1 sop 1 sop 2 3 doc # sop003 sop004 ... 4 job 2 sop 3 sop 4 ... 5 ... ... ... ... 6 ... ... ... ... and I'm not sure how to go about merging each group of rows into something like: a2 b2 b1 c2 c1 ... a4 b4 b3 c4 c3 ... .... ... ... ... ... ... .... ... ... ... ... ... which would at least make a good base for doing the rest of the formatting in RegExp land. Any help would be UBER appreciated. Thanks, Chris Bloom |
|
#2
|
|||
|
|||
|
Hi Chris,
Look up TRANSFORM in HELP. Make your selection A1: F10 copy (Ctrl+C) select a cell past your data or on another sheet, i.e. A12 then Edit, paste special, make sure All (at top), and Transform (near bottom) are checked. BTW, I don't see any connection in your question to Regular Expressions, unless it was just an indication that you know what you are doing in other areas. HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Bloom" wrote in message oups.com... I'm pretty clever with Regular Expressions and can generally reformat just about any Excel data by dumping it into a program like EditPlus and running a ton of RegExp powered Find & Replace operations. However, this latest spreadsheet has me pulling my hair out. Here's what I have a b c d e f 1 job 1 doc # job 2 doc # ... ... 2 sop 1 sop001 sop 3 sop003 ... ... 3 sop2 sop002 sop 4 sop004 ... ... 4 sop 5 sop005 5 sop 6 sop006 Cells a1, c1, e1, etc is a job title Cells b1, d1, f1, etc is the static text 'Document Number' Cells a2, a3, a4, etc are names of policies for the job in a1 Cells b2, b3, b4, etc are the policy numbers for the jobs in column a There can be many jobs (a1, c1, e1, etc) and many policies per job. (but always 1 policy # for each policy) I need to get the data formatted as a b c 1 job 1 sop 1 sop001 2 job 1 sop 2 sop002 3 job 2 sop 3 sop003 4 ... ... ... 5 ... ... ... Any ideas? I came across David McRitchie's RotateCW macro, which allowed me to pivot the table by 90 deg CCW (a bit easier to use RegExp's on) but now Im left with alternating rows of policy #'s and policies: a b c d 1 doc # sop001 sop002 2 job 1 sop 1 sop 2 3 doc # sop003 sop004 ... 4 job 2 sop 3 sop 4 ... 5 ... ... ... ... 6 ... ... ... ... and I'm not sure how to go about merging each group of rows into something like: a2 b2 b1 c2 c1 ... a4 b4 b3 c4 c3 ... ... ... ... ... ... ... ... ... ... ... ... ... which would at least make a good base for doing the rest of the formatting in RegExp land. Any help would be UBER appreciated. Thanks, Chris Bloom |
|
#3
|
|||
|
|||
|
Thanks for the response David, but I'm a bit confused. I don't see any
thing called TRANSFORM in the help or the Past Special dialog. I do see Transpose in Paste Special, and I tried that, but that just got me to where I was at after using the custom RotateCW function, well except that the document #'s are now underneath the document names. But otherwise, I'm still stuck as to how to merge each set of rows together into one row. I suppose a custom macro would work, but I was hoping someone know of an existing function or macro that would do it for me. I'll keep plugging away. Thanks again for taking the time to respond, though. Oh, and my references to RegExp were meant to say that once I could merge the sets of rows together I could easily use a RegExp in an external program to do the rest of the formatting. Unfortunately, the program that I intend to use only supports up to 10 back-references and I need more than that to do transform the data entirely in that one program. I was hoping to use Excel to do the first half of the formatting and ... well, you get the idea. My problem still stands as it did in my initial posting. I can repost it if necessary. Regards, Chris Bloom David McRitchie wrote: Hi Chris, Look up TRANSFORM in HELP. Make your selection A1: F10 copy (Ctrl+C) select a cell past your data or on another sheet, i.e. A12 then Edit, paste special, make sure All (at top), and Transform (near bottom) are checked. BTW, I don't see any connection in your question to Regular Expressions, unless it was just an indication that you know what you are doing in other areas. HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Bloom" wrote in message oups.com... I'm pretty clever with Regular Expressions and can generally reformat just about any Excel data by dumping it into a program like EditPlus and running a ton of RegExp powered Find & Replace operations. However, this latest spreadsheet has me pulling my hair out. Here's what I have a b c d e f 1 job 1 doc # job 2 doc # ... ... 2 sop 1 sop001 sop 3 sop003 ... ... 3 sop2 sop002 sop 4 sop004 ... ... 4 sop 5 sop005 5 sop 6 sop006 Cells a1, c1, e1, etc is a job title Cells b1, d1, f1, etc is the static text 'Document Number' Cells a2, a3, a4, etc are names of policies for the job in a1 Cells b2, b3, b4, etc are the policy numbers for the jobs in column a There can be many jobs (a1, c1, e1, etc) and many policies per job. (but always 1 policy # for each policy) I need to get the data formatted as a b c 1 job 1 sop 1 sop001 2 job 1 sop 2 sop002 3 job 2 sop 3 sop003 4 ... ... ... 5 ... ... ... Any ideas? I came across David McRitchie's RotateCW macro, which allowed me to pivot the table by 90 deg CCW (a bit easier to use RegExp's on) but now Im left with alternating rows of policy #'s and policies: a b c d 1 doc # sop001 sop002 2 job 1 sop 1 sop 2 3 doc # sop003 sop004 ... 4 job 2 sop 3 sop 4 ... 5 ... ... ... ... 6 ... ... ... ... and I'm not sure how to go about merging each group of rows into something like: a2 b2 b1 c2 c1 ... a4 b4 b3 c4 c3 ... ... ... ... ... ... ... ... ... ... ... ... ... which would at least make a good base for doing the rest of the formatting in RegExp land. Any help would be UBER appreciated. Thanks, Chris Bloom |
|
#4
|
|||
|
|||
|
Hi Chis,
You got it every where I typed "past" it was supposed to be "paste", everywhere I typed "paste" it really was "paste". So you did that but it was not what you wanted. How about starting over from the original and what you want to have as a result. You have a Doc# that was not in the original. Can you start with something like A1 B1 C1 D1 A2 B2 C2 D2 A3 B3 C3 D3 A4 B4 C4 D4 and show how you want to see the data, TRANSPOSE wiil look like this when finished. A1 A2 A3 A4 B1 B2 B3 B4 C1 C2 C3 C4 D1 D2 D3 D4 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Bloom" wrote in message ... Thanks for the response David, but I'm a bit confused. I don't see any thing called TRANSFORM in the help or the Past Special dialog. I do see Transpose in Paste Special, and I tried that, but that just got me to where I was at after using the custom RotateCW function, well except that the document #'s are now underneath the document names. But otherwise, I'm still stuck as to how to merge each set of rows together into one row. I suppose a custom macro would work, but I was hoping someone know of an existing function or macro that would do it for me. I'll keep plugging away. Thanks again for taking the time to respond, though. Oh, and my references to RegExp were meant to say that once I could merge the sets of rows together I could easily use a RegExp in an external program to do the rest of the formatting. Unfortunately, the program that I intend to use only supports up to 10 back-references and I need more than that to do transform the data entirely in that one program. I was hoping to use Excel to do the first half of the formatting and ... well, you get the idea. My problem still stands as it did in my initial posting. I can repost it if necessary. Regards, Chris Bloom David McRitchie wrote: Hi Chris, Look up TRANSFORM in HELP. Make your selection A1: F10 copy (Ctrl+C) select a cell past your data or on another sheet, i.e. A12 then Edit, paste special, make sure All (at top), and Transform (near bottom) are checked. BTW, I don't see any connection in your question to Regular Expressions, unless it was just an indication that you know what you are doing in other areas. HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Bloom" wrote in message oups.com... I'm pretty clever with Regular Expressions and can generally reformat just about any Excel data by dumping it into a program like EditPlus and running a ton of RegExp powered Find & Replace operations. However, this latest spreadsheet has me pulling my hair out. Here's what I have a b c d e f 1 job 1 doc # job 2 doc # ... ... 2 sop 1 sop001 sop 3 sop003 ... ... 3 sop2 sop002 sop 4 sop004 ... ... 4 sop 5 sop005 5 sop 6 sop006 Cells a1, c1, e1, etc is a job title Cells b1, d1, f1, etc is the static text 'Document Number' Cells a2, a3, a4, etc are names of policies for the job in a1 Cells b2, b3, b4, etc are the policy numbers for the jobs in column a There can be many jobs (a1, c1, e1, etc) and many policies per job. (but always 1 policy # for each policy) I need to get the data formatted as a b c 1 job 1 sop 1 sop001 2 job 1 sop 2 sop002 3 job 2 sop 3 sop003 4 ... ... ... 5 ... ... ... Any ideas? I came across David McRitchie's RotateCW macro, which allowed me to pivot the table by 90 deg CCW (a bit easier to use RegExp's on) but now Im left with alternating rows of policy #'s and policies: a b c d 1 doc # sop001 sop002 2 job 1 sop 1 sop 2 3 doc # sop003 sop004 ... 4 job 2 sop 3 sop 4 ... 5 ... ... ... ... 6 ... ... ... ... and I'm not sure how to go about merging each group of rows into something like: a2 b2 b1 c2 c1 ... a4 b4 b3 c4 c3 ... ... ... ... ... ... ... ... ... ... ... ... ... which would at least make a good base for doing the rest of the formatting in RegExp land. Any help would be UBER appreciated. Thanks, Chris Bloom |
|
#5
|
|||
|
|||
|
Hi David,
In the end, I did use the TRANSPOSE option. However, I couldn't everything that I needed to directly in Excel, so the process ended up going something like this: 1.) Transpose data in Excel. Copy all data. 2.) Paste into text file. Use RegExp search/replace function to trim excess white space. Copy all data. 3.) Paste data into PHP script (I'm a web programmer so it was the easiest route to go) that would a.) split the text into an array of rows (split text on newline char); b.) split each row into an array of cells (split row on tab char); c.) iterate over the lot and create a new array of document/document # pairs along with some other formatting. Copy all data 4.) Paste into text file. Use RegExp search/replace function to further clean data. Then I finally had something I could import into a database. Sorry for the robust explanation, but I figured I'd share since I spent all weekend working on it :) Thanks for the hint about TRANSPOSE. That set me off in the direction that led to the solution. Regards, Chris Bloom David McRitchie wrote: Hi Chis, You got it every where I typed "past" it was supposed to be "paste", everywhere I typed "paste" it really was "paste". So you did that but it was not what you wanted. How about starting over from the original and what you want to have as a result. You have a Doc# that was not in the original. Can you start with something like A1 B1 C1 D1 A2 B2 C2 D2 A3 B3 C3 D3 A4 B4 C4 D4 and show how you want to see the data, TRANSPOSE wiil look like this when finished. A1 A2 A3 A4 B1 B2 B3 B4 C1 C2 C3 C4 D1 D2 D3 D4 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Bloom" wrote in message ... Thanks for the response David, but I'm a bit confused. I don't see any thing called TRANSFORM in the help or the Past Special dialog. I do see Transpose in Paste Special, and I tried that, but that just got me to where I was at after using the custom RotateCW function, well except that the document #'s are now underneath the document names. But otherwise, I'm still stuck as to how to merge each set of rows together into one row. I suppose a custom macro would work, but I was hoping someone know of an existing function or macro that would do it for me. I'll keep plugging away. Thanks again for taking the time to respond, though. Oh, and my references to RegExp were meant to say that once I could merge the sets of rows together I could easily use a RegExp in an external program to do the rest of the formatting. Unfortunately, the program that I intend to use only supports up to 10 back-references and I need more than that to do transform the data entirely in that one program. I was hoping to use Excel to do the first half of the formatting and ... well, you get the idea. My problem still stands as it did in my initial posting. I can repost it if necessary. Regards, Chris Bloom David McRitchie wrote: Hi Chris, Look up TRANSFORM in HELP. Make your selection A1: F10 copy (Ctrl+C) select a cell past your data or on another sheet, i.e. A12 then Edit, paste special, make sure All (at top), and Transform (near bottom) are checked. BTW, I don't see any connection in your question to Regular Expressions, unless it was just an indication that you know what you are doing in other areas. HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Bloom" wrote in message oups.com... I'm pretty clever with Regular Expressions and can generally reformat just about any Excel data by dumping it into a program like EditPlus and running a ton of RegExp powered Find & Replace operations. However, this latest spreadsheet has me pulling my hair out. Here's what I have a b c d e f 1 job 1 doc # job 2 doc # ... ... 2 sop 1 sop001 sop 3 sop003 ... ... 3 sop2 sop002 sop 4 sop004 ... ... 4 sop 5 sop005 5 sop 6 sop006 Cells a1, c1, e1, etc is a job title Cells b1, d1, f1, etc is the static text 'Document Number' Cells a2, a3, a4, etc are names of policies for the job in a1 Cells b2, b3, b4, etc are the policy numbers for the jobs in column a There can be many jobs (a1, c1, e1, etc) and many policies per job. (but always 1 policy # for each policy) I need to get the data formatted as a b c 1 job 1 sop 1 sop001 2 job 1 sop 2 sop002 3 job 2 sop 3 sop003 4 ... ... ... 5 ... ... ... Any ideas? I came across David McRitchie's RotateCW macro, which allowed me to pivot the table by 90 deg CCW (a bit easier to use RegExp's on) but now Im left with alternating rows of policy #'s and policies: a b c d 1 doc # sop001 sop002 2 job 1 sop 1 sop 2 3 doc # sop003 sop004 ... 4 job 2 sop 3 sop 4 ... 5 ... ... ... ... 6 ... ... ... ... and I'm not sure how to go about merging each group of rows into something like: a2 b2 b1 c2 c1 ... a4 b4 b3 c4 c3 ... ... ... ... ... ... ... ... ... ... ... ... ... which would at least make a good base for doing the rest of the formatting in RegExp land. Any help would be UBER appreciated. Thanks, Chris Bloom |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Start spreadsheet with WinXP start | Excel Worksheet Functions | |||
| Help! Excel 2000 fully SP'ed will not start | Excel Discussion (Misc queries) | |||
| sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) | |||
| Help? start macro automatic in excel | Excel Worksheet Functions | |||
| Inserting Blank Rows Macro? | Excel Worksheet Functions | |||
Linear Mode
