9 replies to this topic

[volcano]

A text file with the following values:

6/8/2010
aaaaaaaa
-----------
5/8/2010
bbbbbbbb
----------
4/8/2010
cccccccc
----------
3/8/2010
ddddddd
----------
2/8/2010
eeeeee
----------
1/8/2010
ffffff
----------

Now I want the following format, how could I do it?
Of course the real file is very large that you cannot answer me by manually cutting and pasting.
My preferred tools are Excel, javascript, access....

1/8/2010
ffffff
----------
2/8/2010
eeeeee
----------
3/8/2010
ddddddd
----------
4/8/2010
cccccccc
----------
5/8/2010
bbbbbbbb
----------
6/8/2010
aaaaaaaa
-----------

Thanks so much!

[WingedPanther]

On what basis are you rearranging the file? descending alphanumeric? Ascending date? Other?

[Roger]

I think this can be done in Excel alone. Couple of questions:
- Does each date have only on line of data before the delimiter?

Quote

6/8/2010
aaaaaaaa
-----------

- How comfortable are you with Excel equations?

Quote

=MOD(B2,3)
=IF(C2=0,B2,IF(C2=1,B2+1,IF(C2=2,B2-1)))

Right now, with 3 additional columns in Excel, I can flip the data to:

Quote

----------
1/8/2010
ffffff
----------
2/8/2010
eeeeee
----------
3/8/2010
ddddddd
----------
4/8/2010
cccccccc
----------
5/8/2010
bbbbbbbb
-----------
6/8/2010
aaaaaaaa

I'll upload the workbook. Let me know if you have questions. [ATTACH]3242[/ATTACH]

Attached Files

•  Book1..zip   4.96K   3 downloads

[WingedPanther]

I was thinking about using regular expressions to convert each set of lines into a single CSV line with elements ordered according to the sort criteria, then sorting the lines, then using a regex to decompose the CSV back into lines.

[Roger]

volcano said:

My preferred tools are Excel, javascript, access..

I just used Excel since volcano prefers it. If you have an alternative, let's post it and we can take a look.

[WingedPanther]

I guess the real question is: what are the sort criteria?

[Roger]

@WP: I agree.. we'll see what volcano says...

[volcano]

Hello guys, it is very nice to see a number of replies when I get back to office! Thank WingedPanther and Roger! Roger's solution is exactly what I want! Excellent!

One more "easier" problem is: how to flip the data in Excel 2007? I can find information but only for Excel 2003 or earlier...

[volcano]

I finally solved the easier problem myself. Thank again for your help!

[Roger]

no problem.