Probably a Noob-Question: How to 'resort' a spreadsheet?

[roderh]

I would like to do this (have a look at the picture, please), but have no clue. I would be very glad, if someone could help me.
Thank you in advance!

[macropod]

This is not a sorting problem, since the inputs and outputs are substantially different. Rather, it is a data conversion problem that would probably require some macro programming in order to produce the required solution. Hardly a 'Noob-Question'.

[Catalin.B]

Hi roderh,
As Paul already said, it's far from a simple problem. The easiest way is to write a simple macro to solve the problem.
But i cannot help myself to choose the hard way, to find a formula that can do this.
Here it is:
=IFERROR(SMALL(IFERROR(IF(FIND($A$17;($B$2:$B$12&$ C$2:$C$12&$D$2:$D$12))>0;ROW($A$1:$A$11);"");"");C OLUMN()-1);"")
It's an array formula, so you have to confirm it with Ctrl+Shift+Enter, not with simple Enter after you type it.
Same formula is in sample workbook attached.
Wellcome to our forum
Tip: It's a very good idea to upload a sample workbook yourself to ease our efforts to help you, this time i wasted some time to transfer your data to excel, but usually, it's your obligation.
Cheers,
Catalin

[roderh]

Hi guys,

Thank you for your replys! Uploading a picture instead of a xls was obviously not so clever, sorry!

@macropod: somehow I thought this could be achived by clicking some buttons.

@Catalin.B: Thank you very much for your help! This will probably save me literally dozens, maybe hundreds of hours typing (really!)! I am very, very relieved!

At the Moment, I have problems to tailor this solution, but I hope I will manage it.

If possible, I would like to hear about this makro!

[Catalin.B]

Hi,
First, let me ammend the previous formula: you have to change $A$17 to $A17 ( swith the row reference to relative reference, this will make the formula to autoadjust when copied to another row)
Don't forget to enter the formula with CSE after you make this change.
For the second problem, again, it's a good idea to upload a sample workbook maybe we can spot other problems.
Another great idea is to share with us the exact error message, just saying that there is an error is not very helpful.
Cheers,
Catalin

[roderh]

Hi,

I have attached a worksheet:

1) I have the German Version. So I would think, posting my error message in German wont help, sadly. If necessary, I would buy a language pack for English (which will cost 19,95€ according to MS - not so cheap imho)

2) Why is it: "$A$1:$A$11"? I would think, it should be "$A$2:$A$12". Or does it not matter?

3) How can I extract a list from the matrix in my example without redundancies to "List of Index-Words"?

5) How can I compact a list of numbers. At the end I do not want "21, 22, 23, 24, 25" (for example), but "21-25", "34, 35" changed to "34f" and "56, 57, 58" changed to "56ff"

Maybe you want to know: Hopefully this way I can create indices for some books I own (in fact 94 Books) in order to work more efficient with them. So there is some more work to to with excel and some more things I dont not understand. But I know I have to learn excel better and I do not want, that you do all the job for me. You helped me a lot so far.

TIA!