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!

Attached Images

2014-05-04_182300.gif (10.4 KB, 29 views)

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'.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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

Attached Files

tmp1321.xlsx (9.5 KB, 8 views)

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!

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

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!

Attached Files

Array 2.xlsx (13.4 KB, 10 views)

Nice
Instead of posting an image of the error, you can describe it in english, just the word "error" is not enough, details matter, like: "Formula is too long", (i guess you can translate such a message in english )

2. If you type ROW($A$2:$A$12) it will return wrong results, offseted with 1 row, and you have to apply a correction: ROW($A$2:$A$12)-1

3. To return a list of unique entries, you can use another array formula, in J2:
=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($B$2:$B$ 12,"<"&$B$2:$B$12)-SUM(COUNTIF($B$2:$B$12,$J$1:J1)),0)),"")
As you can see, only 1 column from those 3 is used to generate this list, if you want a list from all these 3 columns, it gets even more complicated.
4. To create a new list on the side, you have to consider the second argument of SMALL function: "k". If k=1, SMALL will return the smallest value from this array: {1;"";3;"";5;"";7;"";9;10;11} (these are the array results of rows found for "Red"). If k=2, it will return the second smallest value, (3 in our example)
To indicate the k value, i used COLUMN()-1 in the formula. The formula starts from column B, COLUMN() will return the column B number (which is 2). Substracting 1 from that column number , will result 1 (so, in column B SMALL function will return the smallest value from an array of row numbers), this is the way to create a dinamic argument.
If you want to start the formula from column M, this is column 13, so you have to substract 12 from column number to show the first match:
Change COLUMN()-1 to COLUMN()-12 to start from column M.
You will also need to change this:
From: FIND($A17 to FIND($L2 (according to your example)

L.E.: An error may occur because i used the ";" separator in formula, instead of "," if you tried to paste the formula from my first message and you have a comma separator in your regional settings...make the replacements..
Catalin

Sure I can try to translate the essence my ubiquitous error-messages (there are quite long and intimidating, you know).

Thanks, your explanations clarifies things for me.

May I ask: Could you put the formula in an xlsx, because this way the English formulas translate to German. I can translate IFERROR etc. by myself, but Excel does not recognizes "MATCH". And could you do this for at least to columns. This way I probably can expand it to more columns myself.

This problem with unique entries, that will get "even more complicated". In fact there will be dozens of words. Do you thing, this is manageable in that way?

And (of course) I have another question: 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"

No problems with ";". Everything is fine.

Here it is.
Compacting in formulas - much too complicated... In VBA is easier to detect all consecutive numbers and replace them with: first item - last item. That algorithm for 34f or 56ff looks nice, in the same manner: "21, 22, 23, 24, 25" will be 21ffff ?? I think you are desperately trying to complicate things instead of simplifying Keep it simple, complicated solutions lead to unexpected errors.
I don't know what to say about how manageable is that way... If you have a lot of array formulas in this workbook, will slow down performance, significantly. Try creating a unique list using a pivot table, this will get updated only at opening, or when refreshing manually.
Catalin

Attached Files

Array-2.xlsx (14.1 KB, 9 views)

Ah, I see.

For now, I would say, I need time to learn more about arrays and wont ask further (although I have already an other, of course desperately complicated, question in mind ...)

Thank you for your patience!

You're wellcome.
You can mark your thread as solved, using Tread Tools from top of this subject.
Cheers,
Catalin