Oh, where to start??? Reformatting HELP

[Chris Bloom]

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

 

[David McRitchie]

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]

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

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

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

 

[David McRitchie]

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

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

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

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

 

[Chris Bloom]

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

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

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

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



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