Resort a table, preserve underlinings and write to a second worksheet of the same file

Hi,

with a lots of help I managed to get this macro (cf. file "Example Resorting.xlsm" below), but atm it is not working correctly.

What I would like to have is:
1) Resorting a table (given in the "Input-1" worksheet). Atm "range" has specific a values ("B1:AO90"), but should be defined through a selection (i.e. left-clicking [EDIT: not "right"] and dragging the cursor)

2) The resorted table should be written to a second worksheet (which is called "Output-1") of the same file. Atm, the output is written from column 11 [EDIT: not "row"] on of the "Input-1"-worksheet.

3) The Input-table will have underlinings (note the underlinings in cells: B1, B2, C3!). The output-table in "Output-1" should have the respective underlinings.

EDIT: Please have a look at my post #9, also!

Thank you very much in advance!

Hi roderh in Berlin

Originally Posted by roderh

Is There Anybody Out There?

Here is Alan In Hof, Bayern
.....
.. You gave this for sheet 1 ( I had to color yellow to highlight cells with underlining in as my screen shot only shows cells with full underlining. ( C3 here )- In your sheet cell B1 and B2 are partly underlined )

Using Excel 2007

Row\Col	A	B	C	D	E
1	Aaa	1-3	5	7f
2	Bbb	2ff	6-9
3	Ccc	1	2	6	8

Input-1

You give this for sheet 2

Using Excel 2007

Row\Col	A	B	C	D
1	1	Aaa	Ccc
2	2	Aaa	Bbb	Ccc
3	3	Aaa	Bbb
4	4	Bbb
5	5	Aaa
6	6	Bbb	Ccc
7	7	Aaa	Bbb
8	8	Aaa	Bbb	Ccc
9	9		Bbb

Output-1

....................................................
.1 ) I guess you have shortened your data for demonstration purposes. That is good.

. 2 ) I do not quite see the connection with the two based on your explanation. Remember you know this thing inside out. For someone seeing it for the first time you need to make it crystal clear and show what you have
Before
on all sheets, and then show us in a copy of that / those that you hand fill in exactly how everything should look
After
the macro runs.
. Importantly your after sheet or sheets should be based on the test data in your before. ( Again reduced data is good. But pick it carefully to test all possible scenarios that the code must cope with.

. 3) atm. I think your code does something starting from column 11, not row 11 ? I am not quite sure what ****

. 4 ) By me right-clicking and dragging the cursor does not work. By me You left-click and drag the cursor to select a range. I thought we would be in same Left / Right conventions ?

. I am quite good at sorting stuff. I might have to think a bit about how to check for part ( or full for that matter ) underlining in text. But I guess that should be possible.
. In any case I think you will need to make it clearer what you want. The Key is a clear Before and After. In addition try again with your explanation

. If you can explain your requirement better, and no one picks the Thread up, then I could take a look tomorrow, if you wish.
Alan

P.s. .. ****

Originally Posted by roderh

..with a lots of help I managed to get this macro ...., but atm it is not working correctly......!

. Can you elaborate a bit on that.. What you said is a bit like telling a mechanic your car does not work...

Thank you very much, Alan, for replying!
@ 3) yes, its "column" 11, not "row" (sorry)
@ 4) yes, its "left-clicking" (sorry again!)

I' ll try to make it clearer:

Basically it should be like this:
The Input in worksheet "Input-1" is something like this (but not a fixed range as it is in the example, rather a range selected by left-clicking and dragging the cursor):
2015-08-21_054315.jpg

The output in worksheet "Output-1" should be this:
2015-08-21_054405.jpg

Btw: the original data are a lot of files, which contain indexes for books, sorted by keywords -> page-number. You really dont want to look a these files. What I would like to have, is these indexes sorted by page-number -> keywords.

What the code does well imho is: Resolving the types of ranges, that can occur, i.e. "7f" converts to "7" and "8" (each in one cell), "2ff" converts to "2", "3" and "4" and "6-9" converts to "6", "7", "8", "9" and (same type as last one) "1-3" converts to "1", "2", "3")

What the code does not do as desired is mentioned in 1)-3) in the original post:
1) The input for the code should be selected by left-clicking and dragging the cursor. (see first picture)
2) The output should be a second worksheet called "Output-1" (see second picture)
3) Probably the biggest Problem: the underlinings, which are in some cases only partial (see example-file with code or sceenshots). For example: the keyword "Bbb" occurs on pages 2, 3, 4, 6, 7, 8, 9 and it has one important occurrence on page 3 (see cell B2 in input: "2ff"), thus in the output-worksheet "Bbb" should be underlined accordingly (see cell C3 in output)

TIA again!

Hi roderh

Originally Posted by roderh

.......
Basically it should be like this:
The Input in worksheet "Input-1" is something like this (but not a fixed range as it is in the example, rather a range selected by left-clicking and dragging the cursor):
Attachment 414766

The output in worksheet "Output-1" should be this:
Attachment 414768........!

.... These attachments are not working for me
Alan

Hi Alan,

reuploaded the pics. (Although the pics are nothing special, just sceenshots from input- and output-worksheet from "Example Resorting.xlsm".)
(BTW: I cant see your Avatar-Pic)
Greetings

Hi Again...

Originally Posted by Doc.AElstein

....
.... These attachments are not working for me
Alan

Originally Posted by roderh

...
reuploaded the pics.....

..... OK I already saw you had edited them. I see now Images. I thought , as you confirmed, that they simply look like the original Info which you gave and the screenshot therefrom that I pasted back in Post #3

.........................
. So, Sorry i am still no further to understand you
.
. 1) Please do not paste Images, or rather if you do, upload Files with the same info in. This is because I cannot copy them and would have to type in everything manually.

. 2) I think I have from you now, and get the point how the input looks. ( Sheet Input-1 )

. 3) Pasting the output to a different sheet and having the input as a selection rather than a specific range are minor points.

. 4 ) I am still struggling to understand
. 4a) How the output looks initially before a macro runs
. 4b) How the output should look ( that is to say how it changes ) after the final macro I, or anyone does for you, runs.
... I really need a
Before
And
After
.. of how your output sheet should look.. If you mean the output is empty before , then you need at least to tell me ( and obviously you do not need in that case to post me an empty sheet as the Before!!)
. 4c) Please supply a Before and After sheet, that I can copy ( so an uploaded file with 3 sheets in total: first sheet is the input I already have, ( “Input-1“ ) ; sheet 2 ( call that “Output-1_Before” is how everything looks initially ; sheet 3, call that “Output-1_After“ , filled in manually by you , should look exactly as sheet Before should look like after the successful running of a final macro ( based on the actual data in the first ( Input) sheet
..
. Sorry but i am really struggling to see what you want. Or rather tying up the criteria to give the output is puzzling me. I do not ( yet ) see the connection between the Input and Output. Maybe the extra info I ask for would help. In the meantime i will re - read what you have written and see if I can get further in understanding what you want.

. 5) Sorting data based on criteria I am quite good at. ( If you happen to know the code line that determines if the text in a cell line is pertly or fully underlined, that would be a help to me. Or try to find it in the meantime ( or even start a Thread to ask that etc.. ) )

. 6 ) As an example of what I do not quite get or rather the difficulty in understanding you..

Originally Posted by roderh

..... the keyword "Bbb" occurs on pages 2, 3, 4, 6, 7, 8, 9 ......

.. I gues you mean : the keyword "Bbb" occurs at rows 2, 3, 4, 6, 7, 8, 9 of the output
Then further.....

Originally Posted by roderh

...and it has one important occurrence on page 3 (see cell B2 in input: "2ff"), thus in the output-worksheet "Bbb" should be underlined accordingly (see cell C3 in output)..

... ?? In the input sheet, “Input-1“, the partially underlined occurs in B2. So why should i underline Bbb in C3 in the Output sheet?
. I am just not getting the logic yet

Alan
....................................................
P.s.

Originally Posted by roderh

....
(BTW: I cant see your Avatar-Pic)
Greetings

Yeh, that is another problem with Images in Excel Forum, they do not always work. There are endless Threads discussing that..

Thank you again for taking your time with my VBA-problems!
I understand, you are from Hof, Bayern!? Do you speak german? I may be better in clarifying my problems, when doing so in german...

@ 1) The images posted are just screenshots from the file "Example Resorting.xlsm‎" (a bit prettier, though).

@ 2) Correct, the table in "Input-1" is what the code will be working on.

@ 3) For me it is not so easy. I never learned VBA from the start. Its ridiculous, I know...

@ 4a) There is no output before the macro runs, i.e. "Output-1" is empty. The table in "Output-1" is what should happen to the table in "Input-1" after the macro has run. I just made the table in "Output-1" manually for, you know, "clarification" (guess that hasnt worked out well)

@ 4b) See 4a): After the (final) macro, it should look just like the table in "output-1" (see screenshot 2). Yes, you are absolutely right: "output-1" is empty before macro runs.

@ 4c) Before-sheet is "Input-1", After-sheet is "Output-1", which you call “Output-1_After“. There should be no sheet “Output-1_Before”, and maybe this is part of the problem, because when you open the file "Example Resorting.xlsm‎" (and activate the macros) and the run the macro "ReSort" it will give you a new table - but not in a sheet 2 (= “Output-1_Before”), but in the worksheet "input-1", from column 11 on (i.e. form cell K2 on). This new table is basically the same table as in "output-1" - but without the underlinings! I would think, crafting a macro that can make the correct formating, is the main problem.

Before: see screenshot 1
After: see screenshot 2
(or have a look again at the file in my first post)

@ 5) There is no code-line, that determines, if the text in a cell line is partly or fully underlined. This was done manually at the time of making the index of the book.

@ 6) No, the keyword appears on pages on a book. I tried to explain, what kind of "Reallife-task" has to be done: there is a book, the book has an index, the index is sorted by keywords (as usually), a keyword might be "Bbb" (which is just my stupid example) or rather in reality a keyword might be "Einstein, Albert", this keyword appears on some pages of the book, and the keyword might have some important occurrences, in this case the person who made the initial index (which was, btw, me) marked this important occurrence with a underlining. For example, the keyword "Einstein, Albert" is especially important on page 3 and occurred on page 2 also, so in the index would look like "2f". The "f" in "2f" means page 3 of the book. Now the macro resorts the index from the old sorting-order "keyword" -> "pages" to a (rather unusual) new sorting-order "page" -> "keywords". This means, I would like to have a list of all keywords which have relevant occurrences for each page of the book, and especially the one that are marked as important by underlinings. So, when "2ff" ist partially underlined, that means the keyword "Bbb" is important on page 3 of the book and so this important occurrence should be visible (=underlining) in the new index, "2" -> "Bbb"

I really appreciate your patience!
Greetings!

I would think, everything boils down to having some ultra-cool macro, that will do this:

1. Step: read the Input-table (which consists of page-numbers and keywords) provided in worksheet "Input" to a macro (or a part of a macro), which resolves the page-number-ranges and write its output to "Output1"

2. Step: the next macro underlines the appropriate numbers and write the new table to "Output2"

3. Step: the next macro resorts the table and writes the new table to "Output3"

4.Step: the next macro underlines the keywords according to the underlined page-numbers and writes is output to "Output4"

Please have a look at this file, too:
Example Step by Step.xlsx

Hi Again...
. My wife is German and I’ve live here for over 10 years but I am English. English is easier for me, I may be able to understand if you wish to try explaining anything in German. But I understand English better.
.
.
. 4) . OK I get the point now that your output is nothing before you start. I still could not see any relationship between your input and output. Your code was / is doing that.

. However through your further explanations, in particular your Post #9 and the “Step by Step.xlsx” File I see now how you get that output. That is the sort of clear info you need to give. That was necessary in order to tie up the underlined portions in the input sheet with which cells in the output should be underlined.
. I would have needed to spend hours going step by step through your code to determine that without your latest explanations.
.
.
. 6 ) I take, or rather see, the point about what you mean by Pages. –That would have been clear if you had a heading in column 1 of your Output with “Pages”. Without that I only saw numbered rows. So “Pages” had no meaning to me. This emphasise the point again that you know this thing inside and out, but I can only know what you tell me. Very important point in getting the requirement across

.......................................
. Having understood now the problem, I think I may still need to examine your code in some detail to find out where to make the necessary modifications!
.
. Or it may be easier to start from scratch.
.
. I will try to take another look later if I find the time. But I cannot promise anything. This looks like a lot of hard work rather than much of a technical challenge. But maybe I will learn something from some of your code.

Alan
P.s. In your latest “Step by Step.xlsx” File , the first row in Output1 and Output2 has some numbers in different cells – I assume this is a typo and has no significance, Please confirm that in case i have missed something there.
p.s. 2 Another typo ?

Originally Posted by roderh

......
1. Step: read the Input-table (which consists of page-numbers and keywords) provided in worksheet "Step1" ....

.. you mean something like Input Worksheet. I know it may be an obvious Typo, but it does throw one off when trying to understand for the first time!!. So please confirm this also.

Hi Again...
.

Originally Posted by roderh

.....maybe it would has been better, just to the give the problem in "Example Step by Step.xlsx" and ask for a solution. Going through someone others code is tedious. On the other hand, I think, resolving the page-number-ranges is quite a difficult task, so I think, it would not be a bad idea providing a code that already does this. ....

.. I think the "Example Step by Step.xlsx" you prepared was the bit that really made it crystal clear. As always a "Picture Paints a thousand words", and further as a very experienced Forum Member said, "A Spreadsheet paints a thousand Pictures"-
- I have never seen in broken down into a series of step by step sheets as you did, and that takes it even further into making it even more obvious..
. Good explanations alongside help as well that is to say are nie verkehrt
. You should always include any code attempts, but it is also good to know exactly what the problems are that you are having with it. I am working through it now and it is not as bad as I thought. I am not sure yet whether to modify it or do a fresh one. Maybe both if I feel in the mood!
. I’ll post back when I have something
.
. Gruß aus Hof
Alan

Hi,
. I have solutions for you..... rather detailed and involved!!!

. In the end I think going with your code may be the better option, at least initially. You are correct, in this case, having your sorting method was interesting and useful to keep.

. I will drop off a messy version based on your original now, just in case you “pop-in” to the Thread. It does work, but has extra bits and 'comments which I always need to help see what I happening. I leave the Tidying up to you, if you wish to do so.

. I think it may be interesting for me to take a look and see if I can come up with a new from scratch, so I may give that a go later
.................................................

. A few point that spring to mind..

. 1 ) Typo I think in your last Row , sheet Output4, hardly worth mentioning

. 2 ) The codes do not allow for combinations of the forms
2-5ff
- only valid is of the forms
3
2-4
56-58
4ff
4f
( But I guess you already know that )

. 3 ) The new stuff for me in writing this code was getting at parts of text in a cell that were underlined. As always with VBA almost everything is possible. The subject area is just overwhelming. The Range Object must have all that info in, but again if you examine the Range object for some arbitrary cell ( by , for example, setting a Watch on that Range Object in the Watch Window ) again it is just overwhelming. I Googled through some Excel Forum Threads, ( often the best source of info ), and then just experimented a bit. So the method I use to do that may not be the best. It was based on experiments with code such as this...

Please Login or Register  to view this content.

. Simply run that code sometime, then examine the lines I have added to yopur code at various places which are variations of this form:

Please Login or Register  to view this content.

and then I think you will get the Point.

. 4) I do not usually use Functions or Call other Subs. (Just a personal “Messy” preference. ) But I have kept your Subs, but needed to modify them and your code so that Range Object ( Zelle ) rather than the Row, ( Zelle.Row ), was taken as I needed to do the extra check for underlining in the Cell , which required the Range Object of that cell.
. It was probably better for tidiness in this case, as I needed to replace some looping with several lines in order to check for underlining at each character

. 5 ) I have one question concerning one line in your code that i do not understand. Can you explain what this ,line is about please

Please Login or Register  to view this content.

. 6) The other mods, working with a selection and pasting to another sheet ( I call “Output” for now )were fairly straight forward. I think the mods are obvious, Any Questions, ask, no problem

. 7) You will get strange results if your code used with real life data tries to go beyond 256 Columns in the Output Sheet. I expect that may be because the code originally came from something written in a Pre XL 2007 version. ( There was then a limit was 256 columns I think ) . But that can easily be changed to allow you using the entire columns available from XL 2007
................................
. So anyways, to be going in with

.. As before input ( my spreadsheet screenshot does not show the underlining for partly underlined text, but they are as per your original test data and my screen shot in Post # 3 and your Post #4 Images ) . ( Only the references I have changed to names for fun and to unprotect the innocent ! ). I have highlighted in green the selection I made just before running the program

Using Excel 2007

Row\Col	A	B	C	D	E
1	Einstein	1-3	5	7f
2	Doc.AElstein	2ff	6-9
3	roderh	1	2	6	8

Input

.....
. After running the program you get this in the sheet I have named “Output”

Using Excel 2007

Row\Col	A	B	C	D
1
2	1	Einstein	roderh
3	2	Einstein	Doc.AElstein	roderh
4	3	Einstein	Doc.AElstein
5	4	Doc.AElstein
6	5	Einstein
7	6	Doc.AElstein	roderh
8	7	Einstein	Doc.AElstein
9	8	Einstein	Doc.AElstein	roderh
10	9	Doc.AElstein

Output

..............................
. The code I will need to paste in the next Post, as it probably will not fit in One Post...Post it a bit later

Code for last post, Post #13 ( 3 codes in Total )

Please Login or Register  to view this content.

Hi,

Originally Posted by roderh

..... (I only selected the part with the pages for input not the keywords). Can you confirm this? ....

. Correct. Don’t select the keywords
...

Originally Posted by roderh

..... Doing so, I varied the input by underlining other parts, for example "2ff" or "6-9" or didn’t underline at all, and - sadly - I got wrong outputs or an error....

. 1) I just took another look, trying some of those examples. I have not had an error yet, but I agree the results do not always seem to be as they should. I will try to take another look at that tomorrow.
.
. 2) I suppose "6-9" for a keyword means that for that Keyword 6 7 8 9 in the output should be underlined. ? Please confirm.
Thinking about it, It sounds obvious that the answer to that is yes. I think I did not allow for that possibility, so I will have to think again.
.
. I will have a re think, try some other example data and let you know what I come up with tomorrow

Alan

Hi,
. I guess it was my turn to make a few typos.. you have to be very careful picking out exactly the right length in strings etc. Etc. – But that’s a poor excuse.!!
. Anyways I found a few errors, mainly by trying out a lot of different data. But I think I have it close to if not completely correct now. It certainly is behaving better...
.
. I have edited / updated the code in Post #14.
.
. Running with this data for example, ....

Using Excel 2007

Row\Col	A	B	C	D	E	F
1	Einstein	1-3	5	7f
2	Doc.AElstein	2ff	6-9
3	roderh	1	2	6	8
4

Input

http://i1065.photobucket.com/albums/...pspklifzf6.jpg
\1
.........
.... I get this

Using Excel 2007

Row\Col	A	B	C	D
1
2	1	Einstein	roderh
3	2	Einstein	Doc.AElstein	roderh
4	3	Einstein	Doc.AElstein
5	4	Doc.AElstein
6	5	Einstein
7	6	Doc.AElstein	roderh
8	7	Einstein	Doc.AElstein
9	8	Einstein	Doc.AElstein	roderh
10	9	Doc.AElstein
11

Output

.. I think that is getting closer?

...............................

. I have another code now almost written from scratch, but I will wait a bit with posting that until we get the current one up and running properly

Alan

Hi,

Originally Posted by roderh

....for the test-data it seems to work!
For some of the real-life-data-files I get the error-code "Laufzeitfehler 13. Typen unverträglich" and debugger points to this line:
spalte = wsOut.Cells(sZahl + 1, 256).End(xlToLeft).Column + 1

..
.. I lack the experience ( I am learning VBA myself through my participation in the Forum ) to Guess too well the cause of errors, working at a distance as it were. But that incompatible type error puzzles me. That code line is basically one of yours.... I will take a quick look if you upload a file you are having problems with, on the off chance I see what is going on. – But I think, as you inferred, it is up to you to make some more detailed test, going through in debug mod, F8 etc, to both understand the code and thoroughly test with different data, checking how typos effect the code etc. Etc. .. But feedback your findings. One can always add checks to test for typos, or make the code “typo proof” but of course at the cost of making the code even more complicated.
.
BTW. I have German Excel as well, so I often, like you I guess, I fight often with different Date, time, decimal Point cell formatting. And trying to work out how / why excel changes some formatting can be a pain. Often keeping all spreadsheet ranges of interest in Text Format helps, but not always. Up until now your data was well behaved with me on my German Excel. That could be luck!!
......................

Originally Posted by roderh

..... Do you think, it would be difficult, to modify your code to a new macro, that just do the reverse sorting,.....

..... I can’t see how to simply modify such a complicated code to work in reverse. It would be much simpler with a whole new code. Having said that my new code written from scratch might somehow simplify the writing of a new program.

. But before you do that do not jump too far ahead. You will need to maintain any final code, so you need to have a good understanding of it.
. Check out again my post #13 in the meantime, if you can understand that test code ( Sub CellTextPartiallyUnderlined() ) then it should help in following my logic in the mods I did to your code.
. I asked a question or two in that Post as well about a code line I did not understand, and some clarification of what formats could be expected... ( and asked in Post # 16 for some clarification as well about the “..."6-9" for a keyword means that for that Keyword 6 7 8 9 in the output should be underlined. ? …“
........
. As regards your new request.. Although your explanations to your new idea were good, and I know enough about your project I think to see roughly what you want, it is always helpful for you to do file like your last step by step. Choose carefully all test data. As you saw from the last problems ( my typos in the code ) the test data did not catch them.
. For example, at first glance this sounds a bit strange

Originally Posted by roderh

.... 2) mark the new-output red, 3) copy this below the original data, 4) and let excel consolidate the data (there is a button in excel: "Duplikate entfernen" = "remove duplicates").....

... sounds like a bad way to deal with errors. But again maybe I am missing the point initially, and a step by step would be helpful in determining the steps in the code - the new code I wrote from scratch in fact follows some of your logic in your last step by step.
. As i said before, I will wait a bit before posting that so as not too confuse the issue until the original code is fully working, as any mods to that necessary may also apply to this new code

Maybe catch you later, or tomorrow

Gruß
Alan

P.s.1
. Are you actually a German? If so your English is amazingly good. I would have guessed it is your native language?

P.s.2 Maybe you can help me, as I am having some problems with the Forum Software
. 1 ) If you scroll down now and look bottom right you see something like this which refers to your Posting permissions:
• BB code is On
• Smilies are On
• IMG code is Off
• HTML code is Off
• Trackbacks are Off
..... is your IMG code set to on or off
. 2) Did you see one or more IMAGEs from me in Post # 17 , ( NOT the copyable tables , rather these two repeated again here

http://i1065.photobucket.com/albums/...pspklifzf6.jpg

\1

.. what often puzzles me is that I see images that people like you gave in your Post #4, But I cannot see mine immediately, ( unless I click on the Link, which is all I see )
( Note – there may be a difference on what you see, depending on if you are logged in or not. If you have time please try both logged in and nor logged in and tell me what you see)
( Note also, refreshing the page sometimes makes an image re-appear )
. 3 ) What Methhood did you use to produce your Images in your Post #4
Thanks

Hi nochamal

. Ich bin sehr überrascht das Du eine Deutscher bist. - es ist schwere mir vorstellen zu können das meine Geschriebener Deutsch so gut wie deine geschriebener Englisch ist!. Evtl. probier ich dann zwischen durch eine paar Satz. Ich habe ja seit eine paar Woche in die “Non English Excel” Sub Forum eine Thread in Deutsch geantwortet...............
http://www.excelforum.com/non-englis...be-helfen.html
..............Die OP hat nie geantwortet--.. Ob das wegen meine schlechter deutsch war??

.. Jedenfalls

.. Freut mich das die Letzter Code scheint einigermassig zu funktionieren..

.. dann vor ich es hier in meine chaos vorleeren post ich in nächste Post die andere Version das ich von neue an geschrieben habe.

Notes to new Code in Post # 22 and Post # 23 ( But note it is just one code with No Functions – I simply had to chop it in two to fit it in as it was too big for a single Post! )

.1) Code will error when no selected cells are underlined. This can be easily corrected, but I wanted to keep it as simple as possible initially

.2) the code is written from scratch, but as much as possible I have followed the general ideas and used the same names for variables doing similar thing as in your and my first code. This should help a little in comparing and following through the code.

.3) The general thinking in this code version is to use VBA Arrays as much as possible: As much as possible is done “internally” by VBA and the interaction with the spreadsheet therefore is minimised. Because of the nature of your requirement the code is a sort of “Half – Way – house” between a “spreadsheet” Type code ( As in your and my first codes ) and a typical “full VBA Array” Type code. This is because I see no way at getting easily Info. Regarding underlined portions of text in cells without using the spreadsheet Range Object and hence necessarily interacting with the Spreadsheet.

.4) If something like 6-9 or 6-9 or 6-9 or 6-9 0r 6-9 all meant that all 6 7 8 and 9 are underlined then the code could be simplified.
As it is now
6-9 means to underline 7 8
6-9 means to underline 6 7 8
6-9 means to underline 7 8 9
6-9 means to underline 6 7 8 9
6-9 means to underline 9
6-9 means to underline 6

. 5) As with all my codes they can be shortened considerably without effecting performance. I just always include lots of steps in between as I am learning and it helps me remember what was going on. Also the code is “crying out” for a Function or two to take the place of some repeated steps.., but again the current format I tend to prefer as it is just helps me work through it easier

. 6) The code adds more page numbers than you need currently in the first column. Again that could be easily rectified with some more code lines
.................

Gruß
Alan
P.s. 1....
. I had another think about your new idea...the “..reverse thing..”
. I think technically it would be very easy to do. My Array approach suits it as you would store all the information required to reconstruct an “ Input Array “ as you made the nnew “ Output Array “ . But the code would be very long and would have to be done very carefully. So it would take an horrendous amount of time.
. Something along those lines, but much simplified, that would be fairly simple ( so simple that in fact I just did it !! ) is / was to write a code that would take something like this
Using Excel 2007

Row\Col	A	B	C	D
1
2	1	Einstein	roderh
3	2	Einstein	Doc.AElstein	roderh
4	3	Einstein	Doc.AElstein
5	4	Doc.AElstein
6	5	Einstein
7	6	Doc.AElstein	roderh
8	7	Einstein	Doc.AElstein
9	8	Einstein	Doc.AElstein	roderh
10	9	Doc.AElstein

Output

.. and give you some variation of this

Using Excel 2007

Row\Col	A	B
1	Einstein	1U 2 3 5 7 8
2	Doc.AElstein	4 6 9 2 3 7U 8U
3	roderh	1 6 2 8

Index

.
. He U indicates underlined, but that could easily be changed, even to underling with a few extra code lines.
,. So let me know if you want that code posted..


P.s. 2
I have edited occasionally Typos etc. in the Code in Post # 14, so it may be worth occasionally re copying it.

P.s. 3
Can you see this? Kuch mal ob Du dies sehen kann..( es ist übrigens die „Input“ passende zu die zwei oberer Arbeitsblatt Screenshots )
roderh Post 21.JPG

P.s. 4
. I had to split the code in two to get it to paste in ... But it is all one code – There are no Functions

First Part Code for Post # 21

Please Login or Register  to view this content.

Second part of code for post # 21

Please Login or Register  to view this content.

@ 4) There are typos in your underlinings, right?

or 6-9 0r 6-9

means: only the respective keyword for page "6" or "9" should be underlined.

And btw: with the exception of "6-9" no unterlinings of the "-" (= dash = Bindestrich) should occur. Otherwise there is a typo in the data-field.

The general thinking in this code version is to use VBA Arrays as much as possible

I heard (or rather: read) that often: arrays are probably the golden key for problems like this.
It is too bad, that I am not able to appreciate the beauty of your code to its full extend!

In case you want to know. Thats how your infos and part of your last posting appears:


2015-08-24_085536.jpg

2015-08-24_104250.jpg

Hi, Guten Morgen!..oder evtl. Guten Tag jetzt!
.

Originally Posted by roderh

.....Dein Deutsch ist jedenfalls ganz gut verständlich.......

. ich mach dann mal Deutsch, mal English.. ( evtl. wäre eine Excel Forum Moderator was dagegen sagen, bzw. das Du in die “Non English Excel” Sub Forum Posten soll…. Kannst Du ja fürs nächstemal überliegen.. aber da würde fast nie geschaut und da bekommst Du nicht oft Antwort. Soweit die ich weise, gibt’s nur ich und einer andere ( Holger HaHoHe aus Hamburg ) die deutsch sprechen hier.
.
. ich antworte deine letzter posten nur teilweise zwischendursch später oder morgen..–ich hab nicht zu viele Zeit heute für mein „VBA Hobby“
……………………………………………………….

..erstes mal schnell..
. 1 )

Originally Posted by roderh

....Thats how your infos and part of your last posting appears:.....

Danke, Dann wenn es zu sehen ist mit eine alte Browser dann gibst eine Chance das dieses Methode öfter mal geht ..... da bemützt ich varianten ( Für eine .jpeg File )
von zu erste ( Für eine .jpeg File )
Attachment 415231
dann gehts weiter
Attachment 415232
Attachment 415233
u.s.w.
oder
Attachment 415235
Attachment 415236
... aber Sorry, ich ablenken etwas hier, es ist aber dauernde Probleme mit Excel Forum Images.
http://www.excelforum.com/the-water-...-the-like.html
In besonderer .png Files , die viele bevorzug. Und die Probleme scheint öfter mal In Internett Explorer
Wenn Du mir evtl. sagen kannst ob / welcher von dies Image sehen kann oder nett, dann lass ich das Thema für jetzt!!
Erste .jpeg
Attachment 415243
Dan die problematisch .png
Attachment 415244

( Bin Nur überrascht das mein Photobucket nimmer mehr funktionieret bzw. Du kannst sie gar nicht sehen und ich nur wenn ich drauf klicken ?!?
.. ich probier irgendwann was du bemützt. (Faststone capture 5.3. – hast Du auch das letztemal für das große zwei Images in Post # 27 ? ( Und du hast es gekurtz oder ??? – dachte es war großer heute früh? )
. 1b) .. Oder warte… bin ich Komplet falsch? Faststone capture 5.3 hab ich gerade unterladen. Es sieht aus wie eine alternative zum Snipping Tool… es ist nicht eine externen Anbieter wie Photobucket..? )

....Jedenfalls zurück zu wichtiger....!!!!! b.z.w. Haupt Sachen dieses Thread..

…… 2 )

Noch mal zu klaren….von Post # 21 . 4) Ich denke nicht das ich eine typo haben ( außer das ein paar unter strich sind etwas zu lange )
Nochamal um klar zu sein ich schreibe es wieder etwas klarer..
. – So funktioniert beide Codes das Du schon von mir bis jetzt hast:

6-9 bedeutet unterstrich gehört Seiten 7 8
6-9 bedeutet unterstrich gehört Seiten 6 7 8
6-9 bedeutet unterstrich gehört Seiten 7 8 9
6-9 bedeutet unterstrich gehört Seiten 6 7 8 9
6-9 bedeutet unterstrich gehört Seiten 9
6-9 bedeutet unterstrich gehört Seiten 6

Ich habe dann nur gemeint.. Falls es wäre, OK das 6-9 or 6-9 or 6-9 or 6-9 0r 6-9 bedeutet unterstrich gehört Seiten 6 7 8 9 soll , dann wäre die Code etwas einfacher / kurzer möglich.

. Es ist ja etwas schwere die punkt zu klar machen. Ich vermute, aber, so wie die Codes jetzt sind ist OK, auch wenn nicht alle Scenario z.b. 6-9 austreten kann . Und weiter denke ich meine Vorschlag um die code zu simplizieren ist nicht möglich, weil so wie Du gesagt hast
…„……6-9 oder 6-9 means: only the respective keyword for page "6" or "9" should be underlined….“…..

.....
...... 3)

Originally Posted by roderh

...... Wow! Basically thats it, it seems. (Minor thing: numbers are not sorted: "4 6 9 2 3 7U 8U")....

. Ist mir nicht klar ob Du interessiert bist für so eine Code. Wenn Ja dann sag genau wie die Output sehen soll, in die üblicher Before and After weise. Wenn ich dann zeit haben schau ich das noch mal an bzw. Modifizieren es zu die genau Output das Du willst ( ich muss es sowieso nochmal schreiben weil ich es nur ganz schnell geschrieben habe ( und so seit es entsprechend aus!!! )

….4 )

Originally Posted by roderh

.......
I heard (or rather: read) that often: arrays are probably the golden key for problems like this.
It is too bad, that I am not able to appreciate the beauty of your code to its full extend!

.. Weise nicht ob du dieses verstehen Kann…
. Üblicherweise bemützt ich zwei Monitoren. ( Erweiterter Desktop )„ Optionen ( >>>Recht Maus klick auf freier Platz an Desktop >>> Anpassen >> Anzeige … usw )
. Ich habe dann meine Notebook vor meine Nase und eine Gross ( alte fernsehen ) Bildschirm etwas weiter weg ( rechts oben auf’s wand oder sowas ). Ich organisieren VB Development Fenster auf’s Notebook,. Auf Gross Bildschirm habe ich Arbeitsblätter und eine Überwachung Fenster (In VB Fester Leister >>>Ansicht >>> Überwachung fenster )
. Dann mach ich ein „Überwachung“ auf alle Arrays ( Highlight eine Array variable irgendwo in Code >>> F9 )
. dann lass Code in Debug ( F8 ) Mode Ausführen.
. Dann schau ich wie Arrays eingefühlt würde.
. dass mach alles klar was passieret in Code.
. dass größte Vorteil, wie ich schon Erwähnt habe, ist man minimieret wie oft man mit die Arbeitsblätter interagiert. Das soll Veil Vorteil haben besonderer Geschwindigkeit. Damit ist es etwas unübersichtlich wenn code Debugging, deswegen ich benutze diese zwei Monitoren / Überwachung F8 Idee.
... …………………

....... Ich schau nochmal was Du zu letzt geschrieben habe etwas später , und dann evtl. noch mal schreiben. Wenn passt kannst du ja in zwischen zeit was schicken / antworten, … - evtl. schau ich zwischen durch nach.

Bis später
Alan

Hi
. Nur teil antworten schnell zwischen durch….

Originally Posted by roderh

.....
For the last two, I cant see the pictures in your post, but when clicked, the link is valid and shows me the pictures....

. Danke für das Info. Dann weise ich Bescheid.. dass es noch sehr schwere zu schätzen wie es lauft in Excel Forum Mit Images… ich habe sie alle in eine ähnlicher weg gemacht!! Keine Ahnung warum Du nicht dann alles sehen konnten. Am besten macht man gleich viele und hofft mindestens einer ist zu sehen!!

. Thanks for all that Info. As I expected it is very unpredictable if and how Images work or not at Excel Forum. Best is o try different Methods at the same time and hope at least one works!!

……………………………

Originally Posted by roderh

......I am very interested in this code, first for checking-reasons, but second (and probably more important), this could be very useful for creating indexes way more easier in the future!.....

.. evtl ist meine Deutsch doch nicht zu gut. Schau noch mal bitte genau P.s.1 in Post # 21….
….So wie Du es in deine " Example ReverseSorting.xlsx“ vorgestellt ist es eine genau Rekonstruktion in genau die original Format – Das hab ich ja gesagt machbar ist aber Wahnsinn lange und komplizieret wurde. Wenn Du unbedingt das brauchst, dann sag Bescheid und dann nur irgendwann schau ich evtl. nach. Aber versprechen es kann ich nicht
. Was ich aber da angeboten, bzw. schon geschrieben habe war etwas simplifizierte.. Also schau bitter wieder nach ob das wäre von Interesse. Ich habs ja gesagt Du sollst die genau Formet Du willst zeigen, habe aber nuur gemeint etwas anderer als die „U“ als Bezeichnung für unterstrichen ( evtl. auch unterstrichen ). Ich habe aber nicht für das simplifiziert Code gemeint die genau Format wie es original war. Das ist ja die sehr kompliziert und lange zeit dauerende varianten.
. Sag mich irgendwann bescheid was Du willst, bzw. welcher von die Code Varianten

.. Maybe my German was not too good after all!! Please look again through my P.s.1) in Post # 21. What You have shown in your " Example ReverseSorting.xlsx“ is a complete reverse maintaining exactly the same Format. I did say that was possible, but would be a very long , complicated, ( if technically not difficult ) code that would take an horrendous amount of time. If You really want / need that then tell me, and I will maybe have a look sometime in the future, but cannot promise anything.
What I did offer is a simplified code which I asked if would be of interest in. And I then asked to show exactly how You would like it to look. – But I meant by that some other thing other than the „U“ to show that it was underlined, even possibly underlined ) But I was not meaning the Format exactly as in the original which would be the possible but extremely complicated and long time taking Variation.
. let me know sometime exactly what You want, that is to say which Code Variation You wish

Gruß
Alan

Hi,

Originally Posted by roderh

......
Long time ago, I tried the same - and (again: with lots of help) I couldnt achieve it. I will attach this old example-file (and translated my German). Maybe your are interested in.
.....

. That is one hell of a Formula. I could not do that. I find it very good to have alternative solutions in a thread.

. I will look later again at your stuff


. Wow das ist eine geil Formulare. Sowas könnte ich nie Schafen!! Ich finde es gut in eine Thread anderer varianten zu schauern. Ich denke

. ich schau etwas später nach wieder auf deine zeug
. Jetzt mach ich etwas Sport..
Bis Später oder morgen
. Alan

P.s. 1
. I expect You know it would be very easy to modify that formula so that all numbers in a row appear in one cell ( if that is what You would prefer ++++ )

. Du weise schon es Kinder leicht wäre das Formulare zu modifizieren so das alle nummern in eine Zeilen sind in eine Zelle, oder ? ( Falls du das willst ++++ )

Using Excel 2007

Row\Col	L	M	N
1	Output-List:
2	Blue	1 2 4 6

Sheet1

..................................


P .s. 2
. For now assume I will not ( yet ) try to do a code to give You the exact reverse format. ..
.. ++++ But show me how it should look in the simplified form, ... - whether the order of numbers is important, whether a „U“ or similar is satisfactory to show that the number was underlined , or if it should be underlined etc. etc.
. Don’t forget it is always better to keep a code as simple as You really need to minimise the chance of und expected problems popping up later

. Für jetzt geh von aus das ich nicht ( noch nocht ) die genau Code varianten um zu machen deine „Output4“ in „Example ReverseSorting.xlsx“ ….
… ++++ dann zeig noch welcher Format ( bzw. ohne „-„ und ff’s ).. – also ob die Seite Nummern in richtig order soll, ob etwas wie einen “U“ oder ähnliches reicht um die bezeichnen für das es war original unterstrichen, oder wen als unterstrichen soll usw.
. Erinner es ist immer in Codes vernünftig um es so einfach haben wie man braucht, sonst gibt’s immer mehr Wahrscheinlichkeit unerwartet Problem später zu haben!!

Hi

Originally Posted by roderh

….
I will be fine with everything. ….

. Hier nur schnell die Code fürs sehr einfach ruckwertes Code wie in Post # 21 angeboten.. Ich schick nur schnell vor ich es verlieren

.. This is just the code for the already offered and discussed very simple “Reverse Code”, as discussed in Post # 21.
.. Just posting before I lose or forget it. !

…..

Originally Posted by roderh

…. That said, order of numbers would be important, underlining too………

. Ich schau noch später nach wie / wann / ob ich eine besserer oder sogar ein volleres schreiben kann / will

. I will still have a think If / how / when to look at this and or a full reverse code


Alan
. P.s

Man muss ja auch für diesen Code auch die Seiten Nummern selektieren..

For this code you highlight the Page numbers as well


Using Excel 2007

Row\Col	A	B	C	D	E
1
2	1	Einstein	roderh
3	2	Einstein	Doc.AElstein	roderh
4	3	Einstein	Doc.AElstein
5	4	Doc.AElstein
6	5	Einstein
7	6	Doc.AElstein	roderh
8	7	Einstein	Doc.AElstein
9	8	Einstein	Doc.AElstein	roderh
10	9	Doc.AElstein
11	10

Output

….

Please Login or Register  to view this content.

Hi Alan,

wow, another code! That looks like a lot of work!

Unfortunately, at the moment, for the test-file (see attachment), this code just deletes the Input and shows this: "Laufzeitfehler 9: Index außerhalb des gültigen Bereichs". Maybe, I have done something wrong?

Greetings!

Hi,

Originally Posted by roderh

....wow, another code! That looks like a lot of work!
Unfortunately, at the moment, for the test-file this code just deletes the Input ......". Maybe, I have done something wrong?....

......
... You just have the sheets a bit mixed up. Or rather we do.... as we are talking about reversing stuff it is not maybe always too clear which sheet we are referring to...
.. For this code the Index is the actual sheet to which the results are pasted by the code. , and the Input sheet is the original Output sheet ( which I just did not bother to rename ),.
. - check my screen shot in post # 35 – I showed there what you select, and note the name the sheet shown there has is “Output”. In the File you uploaded you have Pasted the test data in the index sheet. The code takes the data from the output sheet and produces the result in the Index sheet

Alan

Hi

Originally Posted by roderh

.....
In the meantime I have tested ......extensively with real data. And: As far as I can see at the moment, it is flawless!....

.. so do you no longer have issues with sheets such as you uploaded in Post # 26 ?? ( I have not got around to looking at that yet )
Alan
P.s.
. groovy Elvis Image, thanks
P.s. Let me know how the Array version code does as well. It should work the same, but may have different size limitations ( one way or the other.. )

Hi, Morgen

Originally Posted by roderh

...
Regarding your new code, it is a bit harder to compare with want it should be, but I will let you know...!

. Nur ganz schnell: diesem zwei Code die ich dir gegeben habe, soll genau so funktionieren, bzw. Soll selber Ergebnisse geben. ( Und so weit wie möglich habe ich sie geschrieben das sie ähnlich in Wirkung sind..

. Just a quick reminder... The following codes I gave should work the same, that is to say give the same results. ( As much as possible i tried to write them such that they are working similarly )

Codes

Sub ReSortToOutputSheetArrays()

Sub ReSortToOutputSheet()

. bis später
Alan

. Hi
. Not sure if you really want this…!!

. Here is a second Index code, numbers in order and an actual underline used rather than a “U” as an indication for underlined side numbers.

. The code may have a few typos, but initial test showed it is producing expected results. The last bits to pick out where to underline got a bit boring. There are of course infinite ways to do it, nothing technically challenging . I stopped at the point where only the second bit of a number will be underlined. So you would get results like this 9 34 567 . Getting the whole number underlined is easy- just a few more tedious lines ( Leave you to practice at that if you wish ! )
. Haven’t considered Duplicate either ( yet )
. The may also be still some issues still with the decimal point to Thousands Separator to Date etc random changing habits of Excel. But I certainly struggled ( as always with this issue ) to make the code bullet proof..

. Anyways, first version of this Sub IndexMakerOrderedUnderlined() is in the next two Posts … It would not fit into one. Remember is it one code , no Functions etc. Copy both to same module, the second bit tacked directly underneath onto the first.

…
. It takes for example the selected range shown here in Green..

Grüß dich, hi

. Ob du die nächste Code wirklich sehen willst!!

. Hier kommt die nächst Index Maker Code

. Die Code hat bestimmt eine paar Typos in evtl. Bugs noch..Aber scheint zu erste zu Funktionieren. Die letzte stuck um die Zahlen unterstrichen zu könne hat mir etwas gelangweiligt. Ich hab auf gehört als ich geschafft eine teil eine Seite zahl zu unterstrichen. Bzw. man kriegt dieses9 34 567 . Um das ganz zahl zu unterstreiche ist einfach, nur eine paar Code Linien noch, abee etwas langweilig. ( Das verlass ich dir zu üben wen du Lust hast ! )
. Die Duplikat Seiten habe ich auch noch nicht aufs Betracht gezogen
. Es kann auch noch evtl. Problem das kommt von die sehr ärgerlich tendace Excel hat um TausenderTrennzeichen zum DezimalTrennzeichen zum Datum umwandle usw. Abe ich hab ( wie immer ) sehr Veil muhe gegeben müssen um das zu verhindern, bzw. ins griff zu Kriegen

. jedenfalls es kommt dies Version die Sub IndexMakerOrderedUnderlined() in nächste zwei Posts … Aber wichtig , es ist einer Code ( Keine Funktionen usw..), da musst du die zweite Teil ins selbe Modul direkt unter die erste Teil einfügen

. es arbeitet, zB. Aud diesn Bereich gezeigt in grün

Using Excel 2007

Row\Col	A	B	C	D	E
1
2	1	Einstein	roderh
3	2	Einstein	Doc.AElstein	roderh
4	3	Einstein	Doc.AElstein
5	4	Doc.AElstein
6	5	Einstein
7	6	Doc.AElstein	roderh
8	7	Einstein	Doc.AElstein
9	8	Einstein	Doc.AElstein	roderh
10	9	Doc.AElstein
11	10	Einstein
12	11

Output

… and gives you this ( If you can see it)

… dann bekommst du diese (falls du es sehen kann)


Attachment 415864


……………………………………………………….

. I had some further ideas / thoughts on your reverse program thing. I will sleep on that a bit and get back in a day or two on that one. You have enough to do in the meantime working through the Sub IndexMakerOrderedUnderlined() I think…..!!


. ich habe eine paar Gedanken / Ideen in der zwischen Zeit an deine Reverse Code. Ich denk noch nach und irgendwann in lauf die nächste paar Tagen kontaktieren und sag was dazu. Aber Du hast bestimmt genug für jetzt mit die Sub IndexMakerOrderedUnderlined() zu verstehen…!

Bis dann

Alan

Please Login or Register  to view this content.

Please Login or Register  to view this content.