[Solved] Determine which group has the specified number

[phofrock]

Its been dozens years since I did anything like this and at that time it was in Excel. I've read most of the posts regarding IF and MATCH formula's (and maybe its not one of those that I need to use), but I can't seem to find an answer in my particular case. I want to create a formula based on the following (and please see the attached spreadsheet.)

I'm placing 1 of 36 possible numbers in cell A2. (1 through 36)

I'm looking for help with the formula to execute the following in cell A1: The 36 possible numbers from cell A2 are divided into the following 3 groups of 12. If one of the 36 possible numbers entered in A2 match ANY number in the group 1 set of numbers (4, 5, 6, 7, 8,14, 22, 25, 32, 33, 35, 36), a number 1 will be assigned in cell A1. If one of the 36 possible numbers entered in A2 match ANY number in the group 2 set of numbers (1,3,11,15,17,18, 21, 23, 24, 27, 28, 31) a number 2 will be assigned in cell A1. If one of the 36 possible numbers entered in A2 match ANY number in the group 3 set of numbers (2, 9, 10, 12, 13, 16, 19, 20, 26, 29, 30, 34) a number 3 will be assigned in cell A1.

I'm also looking for help with the formula to execute the following in cell A3 (although it should be quite similar): The 36 possible numbers from cell A2 are also divided into the following 3 groups of 12 (but different from those in A1). If one of the 36 possible numbers entered in A2 match ANY number in the group 1 set of numbers (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), a number 1 will be assigned in cell A3. If one of the 36 possible numbers entered in A2 match ANY number in the group 2 set of numbers (13,14,15,16,17,18, 19, 20, 21, 22, 23, 24) a number 2 will be assigned in cell A3. If one of the 36 numbers entered in A2 match any number in the group 3 set of numbers (25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36) a number 3 will be assigned in cell A3.

I created a simple formula in column D2, and it works but only with 1 number. Any help will be deeply appreciated.
Thank You!

[FJCC]

I think the attached sheet does what you want. I used the VLOOKUP function for the result in A1 and the QUOTIENT function for the result in A3.

[MrProgrammer]

[for] any number in the group 1 set of numbers (4, 5, 6, 7, 8,14, 22, 25, 32, 33, 35, 36), a number 1 will be assigned in cell A1.
[for] any number in the group 2 set of numbers (1,3,11,15,17,18, 21, 23, 24, 27, 28, 31) a number 2 will be assigned in cell A1.
[for] any number in the group 3 set of numbers (2, 9, 10, 12, 13, 16, 19, 20, 26, 29, 30, 34) a number 3 will be assigned in cell A1.

[for] any number in the group 1 set of numbers (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), a number 1 will be assigned in cell A3.
[for] any number in the group 2 set of numbers (13,14,15,16,17,18, 19, 20, 21, 22, 23, 24) a number 2 will be assigned in cell A3.
[for] any number in the group 3 set of numbers (25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36) a number 3 will be assigned in cell A3.

MATCH and INDEX for both cases. The highlighting shows which group of 12 was chosen. I used column D from your attachment instead of column A as described by your post. Type a number in D2 and the results appear immediately in D1 and D3.

202411091726.ods

(27.04 KiB) Downloaded 26 times

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[karolus]

Hallo
According to the Example-file by @MrProgammer:
EDIT: INT ⇒ ROUNDUP

Code: Select all

=ROUNDUP(MATCH( D2;$F$1:$AO$1;0) / 12)    #1

=ROUNDUP(D2 / 12)	#2 

[MrProgrammer]

=INT(D2/12) for #2

Perhaps I have misinterpreted your post, karolus. There are, of course, a dozen ways to get the desired result but I don't think this is one of them.

[for] ANY number in the group 1 set of numbers (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12), a number 1 will be assigned

Your proposed INT formula returns 0 for 1 through 11. My 202411091726.ods attachment correctly returns 1.

[for] ANY number in the group 2 set of numbers (13,14,15,16,17,18, 19, 20, 21, 22, 23, 24) a number 2 will be assigned

Your proposed INT formula returns 1 for 13 through 23. My 202411091726.ods attachment correctly returns 2.

[for] any number in the group 3 set of numbers (25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36) a number 3 will be assigned

Your proposed INT formula returns 2 for 25 through 35. My 202411091726.ods attachment correctly returns 3.

=INT(MATCH(D2;$F$1:$AO$1;0)/12) for #1

I find similar problems with this proposed formula. It returns the wrong result in most cases. I understand how to amend both INT formulas to return the correct result, but feel no need to explain that since I have already provided my own solution.

[karolus]

Hallo
Of course: replace INT with ROUNDUP

[phofrock]

Thank you for your reply's! I'm pretty sure I didn't describe the problem accurately, so I revised my spreadsheet example to have more clarity.
Column A is more descriptive and provides rules. Columns D,E and F provide specific examples of what I'm trying to accomplish.
Thank you again for your help!

[FJCC]

Can you explain what is lacking in my solution that is the first post after your initial question?

[phofrock]

I'm not good at this so I'm sorry if I don't understand your solution. Is your column C the same as my row 2 (starting at D2), meaning that is the randomly drawn number? If so, your column D does indeed match with what I'm looking for in my row 1. However, I want to enter the drawn number in your column C as it is drawn in real time, and see the results on your column D in real time. Maybe I'm asking for something that can't be done, I don't know.
Hope this helps to explain my request.Thank you for your replies and expertise.

[keme]

... If one of the 36 possible numbers entered in A2 match ANY number in the group 1 set of numbers (4, 5, 6, 7, 8,14, 22, 25, 32, 33, 35, 36), a number 1 will be assigned in cell A1. If one of the 36 possible numbers entered in A2 match ANY number in the group 2 set of numbers (1,3,11,15,17,18, 21, 23, 24, 27, 28, 31) a number 2 will be assigned in cell A1. If one of the 36 possible numbers entered in A2 match ANY number in the group 3 set of numbers (2, 9, 10, 12, 13, 16, 19, 20, 26, 29, 30, 34) a number 3 will be assigned in cell A1.

If you don't want to use a cell range, you can use an inline table to hold the return value for each number from 1-36.

The resulting one-liner for A1:

Code: Select all

=INDEX({2;3;2;1;1;1;1;1;3;3;2;3;3;1;2;3;2;2;3;3;2;1;2;2;1;3;2;2;3;3;2;1;1;3;1;1};A2)

You could of course use the same approach for A3:

Code: Select all

=INDEX({1;1;1;1;1;1;1;1;1;1;1;1;2;2;2;2;2;2;2;2;2;2,2;2;3;3;3;3;3;3;3;3;3;3;3;3};A2)

but for this, a simple calculation is easier:

Code: Select all

=INT((A2-1)/12)+1

[FJCC]

@phorock = Sorry, I somehow missed your post yesterday. Columns C and D in my original file are a table mapping the possible drawn values, 1 - 36, to the desired result for the drawn value. In the attached file, I have moved that helper table to the cell range A5:B40. Now rows 1 and 2 have formulas that return the desired values when drawn values are entered in row 2. I filled in A2 with 13 and A1 shows 3 and A3 shows 2. If you enter values in any cell in B2:F2, you will get the desired results in the corresponding rows 1 and 3. You can extend the formulas to more columns with copy and paste.

[Lupp]

I must admit that I didn't read all the posts above thoroughly. (Some of them are rather long regarding my bad English.)
However, I think the attached file contains a reasonable suggestion for how to solve the given problem.
The questioner will need to shift positions of cells regarding the relative and absolute addressing, and to delete many of the formulas which only are contained to show all the cases.

aoo112087_assigningGroupNumbersToTheElemtsOfArbitraryLists..ods

(21.55 KiB) Downloaded 24 times

Also: I try to avoid overly fortmatted sheets if possible.