# [dba-Tech] finding the most consistent combination of 3 columns

Stuart McLachlan stuart at lexacorp.com.pg
Sun Mar 7 01:54:25 CST 2021

```> > Convert each series of three numbers into an integer - 5,5,5, becomes
> 555 ((First value * 100) + (Second value * 10) + Third value) ,

So 0,0,15 becomes 15?  Was that originally 0,1,5 or 0,0,15?
0,10,0 becomes 100? What that originall 1,0,0?

Assuming the numbers have no more than 2 digits then a string sort:
Format\$(n1,"00") & fromat\$(n2,"00" and format\$(n3,"00")

If there may be larger numbers, change the "00" to "000" or whatever is needed.

You can convert back to the original values with three x VAL(MID\$(....))

--
Stuart

On 6 Mar 2021 at 22:10, Rocky Smolin wrote:

> Convert each series of three numbers into an integer - 5,5,5, becomes
> 555 ((First value * 100) + (Second value * 10) + Third value) , and
> place the integer value in a column of a spreadsheet or in a field in
> a table.
>
> Sort descending by the integer column or retrieve the data form the
> Access table with a simple select query query that sorts descending on
> the integer field.
>
> You can then take each integer value and recreate the original 3
> values by reversing the process. Yes?
>
> hth
>
> r
>
> On Sat, Mar 6, 2021 at 9:52 PM John Bartow <jbartow at winhaven.net>
> wrote:
>
> > Hi All,
> > I am looking to find the best combination of 3 columns of numbers
> > and then sort by the highest ranking. For instance given the values
> > below I want to find 5,5,5 and then 4,4,4 and then 3,2,3 and then
> > 3,2,2. 0,4,8 5,1,1 1,3,5 3,2,3 12,0,0 0,10,0 0,0,15 4,4,4 5,5,5
> > 3,2,2
> >
> > Suggestions on how to proceed in Excel or Access.
> >
> > John B
> > _______________________________________________
> > dba-Tech mailing list