Find most common (x5) numbers within numerical string range

[JEWB]

Hi,

I have a range of cells containing a string of numbers separated by ';' e.g.

COLUMN A COLUMN B
IDENTIFIER_01 1122;1942;2011;1869;2642;2729;2731;2655;2678;2341;2347;2354;2332;2506;1809
IDENTIFIER_02 1122;1942;2011;1869;2642;2729;2731;2655;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_03 1122;1942;2011;1869;2642;2729;2731;2732;2648;2255;2207;2217;2220;2387;1880;2506;1809
IDENTIFIER_04 1122;1942;2011;1869;2642;2729;2731;2732;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_05 1122;1942;1971;2011;1869;2642;2729;2731;2648;2678;2342;2354;2355;2207;2209;2217;2220;2398;1880;2506;1809
IDENTIFIER_06 1122;1942;2011;2169;1869;2642;2729;2731;2732;2678;2341;2343;2347;2354;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_07 1122;1942;2095;1869;2573;2587;2648;2655;2673;2207;2209;2220;1880;1809
IDENTIFIER_08 1122;1942;2095;1870;2642;2648;2673;2678;2341;1880;2506;1809

I am looking to find the top 5 most common/repeated numbers within this range. I have found a formula to work out the largest number within the range:

=MAX(IFERROR(0+TRIM(MID(SUBSTITUTE(";"&B3,";",REPT(" ",LEN(B3))),(ROW(A$1:A$200)*LEN(B3))-1,LEN(B3))),0)) (REF http://www.excelforum.com/excel-gene...e-numbers.html)

but I haven't yet found a solution to modify it to work with e.g. the mode function.

I know I could separate all the numbers out using 'text to columns' but I will need to work this out regularly on large quantities of data so I am looking for nice tidy solution :-)

Any ideas would be really appreciated!

Thanks

[XOR LX]

Hi,

If you could post a workbook with some examples and your desired result in each case then I'm sure we'll be able to help.

Regards

[Jakobshavn]

How do you want "ties" handled? ................say six values occurred six time and all other values were unique??

[JEWB]

Hi,

Thank you so much for replying! I have attached here an example workbook:

Find most common (x5) numbers within numerical string range.xlsx

So in this file is:
COLUMN A: CATEGORY ID (this is the parent/category identifier)
COLUMN B: INDIVIDUAL ID (this is the unique identifier for each line)
COLUMN C: NUMBERS (these are the number strings separated by ';' that I need to search)
COLUMN D: This is where I am looking to place the top 5 results that belong to each CATEGORY ID.

In column D I have put in some pretend results to show what I am trying to achieve; the results will search the range of the CATEGORY ID and return the top 5 most common results.

Thanks so much!

[XOR LX]

Ah, so you want the results concatenated into a single cell? That will require VBA.

Or, if you are happy to have each returned into a separate column to the right, then that is reasonably straightforward using worksheet formulas.

Please confirm.

Regards

[JEWB]

Thinking about it 5 separate columns to the right would actually be more useful...

Thanks!

[AlKey]

You can use this formula in D2

=IFERROR(--TRIM(MID(SUBSTITUTE($C2,";",REPT(" ",LEN($C2))),(COLUMNS($B:B)-1)*LEN($C2)+1,LEN($C2))),"")

Pull it across and then down. It will extract values in separate cells.

[XOR LX]

Hi again.

Try this array formula** in E2:

=MODE(IF(COUNTIF($D2:D2,IFERROR(0+TRIM(MID(SUBSTITUTE($C2:$C9,";",REPT(" ",255)),255*(TRANSPOSE(ROW(INDIRECT("1:"&1+MAX(LEN($C2:$C9)-LEN(SUBSTITUTE($C2:$C9,";",""))))))-1)+1,255)),""))=0,IFERROR(0+TRIM(MID(SUBSTITUTE($C2:$C9,";",REPT(" ",255)),255*(TRANSPOSE(ROW(INDIRECT("1:"&1+MAX(LEN($C2:$C9)-LEN(SUBSTITUTE($C2:$C9,";",""))))))-1)+1,255)),"")))

Copy to the right a further four cells.

Also copy to E10:I10 and E18:I18 (note that currently your CATEGORY IDs are grouped into eights; this is assumed to always be the case, so no amendment to the formula has been made to account for situations where it is not.)

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[XOR LX]

Try this array formula** in E2...

Copy to the right a further four cells...

Also copy to E10:I10 and E18:I18...

I'm not sure why you keep insisting on mis-reading/ignoring my instructions! Why have you copied the formula to rows 3-9, 11-17, etc.? It was not designed to be thus.

I can amend it so that you see precisely the same results in those rows as well if you want, but wouldn't it be simpler (not to mention saving on considerable calculations) to have the results in the first row only, as my formula gives, and then have simple =E$2, =F$2, etc. formulas in the rows beneath, if you really want to see the results duplicated there?

Regards

[JEWB]

Thanks guys, this is so close!

I have amended my sheet to show your my workings:

Find most common (x5) numbers within numerical string range 2.xlsx

I have included both of your formulas (@ XOR LX columns I-M and @ AlKey columns N-R) to the right of the sheet (in case I have done something wrong!) but what I am looking for is for the formula to return the top 5 most common numbers within the range of each CATEGORY ID, so each section should return the same results (see my examples in columns D-H). I'm not sure if the current formulas are looking at the rows and now the overall range?

@ XOR LX, you mentioned assuming that my categories are in groups of eights, but they actually vary (sometimes 10, sometimes over 100).

Thanks again!

[XOR LX]

Not sure I understand. You put the formula I suggested in a different cell to that which I stated but didn't make the necessary adjustments?

Also, why are you having the results duplicated for each row in a given CATEGORY ID and not just the first one? Seems like unnecessary calculation to me.

Regards

[JEWB]

Apologies XOR LX, I was trying to help by showing the new result layout across 5 cells as we discussed earlier.

Please find attached here the original layout with your formula:

Find most common (x5) numbers within numerical string range 3.xlsx

It doesn't really matter to me the results are repeated within this layout/sheet, as I will cut this down for the final result to show e.g.:
CAT_001: 1880 1809 1869 1122 1942
CAT_002: 1121 2684 2655 2411 1809
CAT_003: 1121 1935 1970 1869 2684
etc... (imagine each number in a separate cell)

All I really need to identify are the top 5 repeated numbers across each INDIVIDUAL ID (column C) within the CATEGORY ID range (column A) so that I can provide 5 final numbers for each parent/CATEGORY ID.

I hope this makes sense... sorry for any confusion!

[XOR LX]

Ok, so if I come to the matter of it not always being eight rows' worth of data for each CATEGORY ID (a relatively simple fix), can you tell me which of the results from my formulas in this latest attachment are not what you were hoping for?

I have to say I'm not quite sure how you arrive at your manually-inputted results in column D. For example, for row 2, you give: 1122, 1870, 2011, 2642 and 2731. Yet, in the range C2:C9, each of these occurs 5, 1, 3, 4 and 3 times respectively.

Whereas my outputs (1880, 1809, 1869, 1122 and 1942) all occur 5 times, apart from 1869, which occurs four times.

Regards

[JEWB]

Morning XOR LX,

I have just looked at the numbers and can see that the formula is practically there... (this is literally amazing, thank you so much!)

My manual entries were originally format examples, so I've updated them to show true results. The top results for CAT_001 are:
1122 (8)
1809 (8)
1942 (8)
1869 (7)
1880 (7)
2506 (7)
2642 (7)

and this means the top 5 would be any of those results, but I can actually see 2655 (3) and 2673 (2) in the results using your formula? Should that happen?

In CAT-002, the top occurring numbers are
1121 (8)
1809 (8)
1956 (8)
2411 (8)
2655 (8)
2658 (8)
2684 (8)

So, any of these numbers would also be acceptable, but I can see 1880 (7) in the results?

Ultimately, each of these numbers will reference a descriptive phrase from a list (I can work that bit!), so the numbers within each CATEGORY ID need to be uniform (like the format in column D - ignoring the grey bracket notes!), although as we mentioned, separate columns for the results is more then fine!

I can also see that the formula returns "#N/A" on the last line? Should that happen?

Many thanks :-)

Find most common (x5) numbers within numerical string range 4.xlsx