Give the other numbers

FarCome

New member
Joined
May 8, 2016
Messages
30
Reaction score
0
Points
0
Hoping for a formula or UDF to do this (UDF preferred).


Please see file 'Others_Numbers.xlsx'


**Note** I am asking for a somewhat similar result here but it's much more complex and am asking for actual missing pairs.
This request is different.

http://www.mrexcel.com/forum/excel-questions/942440-sort-groups-4-numbers-5.html


From Post #44 but it has stalled. If anybody would like to try for a solution please do.
 

Attachments

  • Other_Numbers.xlsx
    9.5 KB · Views: 8
See the attachment
 

Attachments

  • __Other_Numbers snb.xlsb
    11.4 KB · Views: 9
In the attached are 2 choices of UDF, blah and blah2. Both should be array-entered (Ctrl+Shift=Enter). They should be entered into a single column range.
blah returns strings (A1 is a string). If the range you've entered the formula into is too small, a '+' sign is added to the value in the bottom-most cell. If the range you've entered the formula into is too big the '-' gets put into the surplus cells. Demonstrated in columns C & D.
blah2 returns whole numbers. No indication of too small a range used is given, too big a range results in #N/A in the surplus cells. Demonstrated in column E.

It does expect a Named vertical range called fixedset. Can be tweaked to include this as a range within the formula if required.
 

Attachments

  • ExcelGuru6114Other_Numbers.xlsm
    18.9 KB · Views: 5
I was able to reduce the number of 'helper' columns to 1.
 

Attachments

  • __Other_Numbers snb.xlsb
    11.1 KB · Views: 6
If the range you've entered the formula into is too small, a '+' sign is added to the value in the bottom-most cell. If the range you've entered the formula into is too big the '-' gets put into the surplus cells

Perfect....and with extras, top stuff :whistle:

Thank you.

I'm going to ask for a new request regarding giving other 'pairs' instead of numbers. It will be the same as the question/s I asked in the link given in #1 post here but I think it's more appropriate to re-ask it here. I'll set it up in a file.

Again thanks.
 
Thank you for the formula snb and well done.


Your time and effort is appreciated.
 
Here is the new request.

I'm asking for 9 'other' pairs be given. I believe it is quite complex and as stated this was asked in link given in post #1 but no result.

Please see attached file

Good luck
 

Attachments

  • Other_9_Pairs.xlsm
    19.8 KB · Views: 8
Why don't you use the simplest solution ?
Why don't you tell us the meaning/goal of this all ?
 
Why don't you use the simplest solution ?

Sorry. I don't understand your question. if I knew the solution then..................


Why don't you tell us the meaning/goal of this all ?

Probably won't help but it is related to pairs of people for selection purposes in a unique administration organisation.

If my request can be given a successful result then that would be appreciated.

Like I said I think it requires a complex solution. Far beyond me at least. :painkiller:
 
Last edited:
Ok this is going nowhere. Please explain where the ‘Other_9_ Pairs’ UDF is which I asked for in post #7? Or, if elsewhere, the other 9 pairs have been shown to be given as asked for in#7?

If your referring to the answer p45cal gave then they are the ‘other numbers’ not ‘pairs’.
Sure I can manually obtain to other 9 pairs from them but I’m now seeking a script result if possible.

If your referring to the UDF Pairs() which I supplied and use it as for example:
Pairs(14) or Pairs(15) or Pairs(16) or Pairs(17) or Pairs(18) then sure they will give their result but that is not what I’m seeking.

Please re-read the contents of the file “Other_9_ Pairs.xlsm” which I supplied in post #7 to see what I’m after.

Anybody else able to help with this?
 
Hi p45cal,


Would it be possible to modify your UDF ‘blah’ to give the ‘other numbers’ in A2 or wherever (across) in a comma delimited string result?
Example:
10, 20, 60, 90, 100, 130, 150, 160, 220, 230, 260, 290, 300, 310, 320, 330, 340, 350, 360


Also do you think my request in post #7 is even possible?

Thanks
 

Attachments

  • ExcelGuru6114Other_9_Pairs.xlsm
    20.3 KB · Views: 5
Would it be possible to modify your UDF ‘blah’ to give the ‘other numbers’ in A2 or wherever (across) in a comma delimited string result?
Example:
10, 20, 60, 90, 100, 130, 150, 160, 220, 230, 260, 290, 300, 310, 320, 330, 340, 350, 360
try:
Code:
Function blah3(rng)
x4 = Split(Application.Trim(Replace(Replace(rng.Value, ",", " "), "/", " ")))
Z = Range("fixedset").Value
For i = 1 To UBound(Z)
  If IsError(Application.Match(CStr(Z(i, 1)), x4, 0)) Then
    q = q & CStr(Z(i, 1)) & " "
  End If
Next i
blah3 = Join(Split(Application.Trim(q)), ", ")
End Function
 
I'm not sure, but see attached

Close p45cal and thank you but………..

I’m sure you can fix the 1[SUP]st[/SUP] problem

The second problem is the one. Very hard!!

See sheet 2.

I tried my best to set out what’s required if possible.
 

Attachments

  • ExcelGuru6114Other_9_Pairs(N).xlsm
    26.8 KB · Views: 11
I’m sure you can fix the 1[SUP]st[/SUP] problem

P45cal, there is no 1[SUP]st[/SUP] problem with your code.

The problem is mine. There are 2 more pairs 340/350 & 350/360 that should have been used. So there are 60 pairs not 58. My bad!!!

I have added them in your script and all is perfect. The ‘Others’ code does select all others possible selections of unique pairs.

If 2[SUP]nd[/SUP] problem can’t be resolved, then maybe try to modify ‘Others’ to give all other possible unique pairs (as it does now) PLUS any unused numbers as that would be almost as good.

Thanks
 
in sheet2, based on data in cell A45:
5/10, 40/50, 140/150, 160/170, 220/230, 260/270, 280/290, 320/330, 340/350
you state in cell A46:
20/30, 80/90, 100/110, 200/210 << These are the ONLY available unique pairs based on the above 9 pairs

However, after adding the "2 more pairs 340/350 & 350/360" to the code for the udf Others(), I get:
20/30, 60/90, 70/80, 100/110, 180/210, 190/200

Is this correct, or are you correct still with your 'ONLY' statement?
 
Back
Top