Forum Discussion

4Hobby's avatar
4Hobby
Copper Contributor
Dec 19, 2022

Use the values returned by COUNTIF in a table and sort them

Hello, if possible, I would like help identifying the formula and/or macro useful for automating the procedure described below:
In the lower six areas 'AD33 ÷ AS33, H73 ÷ AB73, H113 ÷ AN113, AD153 ÷ AS153, AD193 ÷ AS193, and U233 ÷ BB233' are highlighted - using the COUNTIF function - the quantities of six numbers ( A2 ÷ F2 ) found in the aforementioned groups of three or four columns
I would like to report the numbers found, for single quantities, doubles, triples, etc. in a table for a subsequent count of equal values.
In the attached example -in BQ45 ÷ DH75- the expected result is shown for the first 3 types of values (single, double and triple), in line 75 I would like to calculate the total of equal values, equal pairs, equal triples….this table -if that simplifies things- it can be divided into sections as many datasets as there are. 

Thanks in advance for any helpful suggestions

Sample ref file :  https://we.tl/t-rFeTz86VuW

excel
  • 4Hobby's avatar
    4Hobby
    Copper Contributor
    Jan 07, 2023

    I add the file containing the macro that a friend developed for me, in case it may be useful to someone.
    Practically by inserting a group of 6 numbers (in the range 1÷90) in cells A5÷F5 and launching the macro 'Compare'... in row 76 of the sheet 'Table 1' the 'equal' quantities found for single values, pairs of values, triples ... etc up to 9 quantities are returned.

    I ask if the same analysis and results can be performed using exclusively formulas - without the use of macros/code-

    thank you

Resources