Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help!! find two number recurring numbers in row??
Hi everyone, new to posting on this forum so thank you for your patience.
I have a spreadsheet which has data in 7 columns and around 100 rows. I want to be able to find what two numbers appear most frequent together on each of the rows. They don’t have to appear side by side they can appear anywhere on the row. It is kind of like the mode but instead of finding which number is most frequent it is to find what two numbers appear most frequent on each row? Sorry if the explanation is not clear it’s hard to put in words. I have searched all around but can find a solution, any help from you excel gods would be much appreciated. Below is a sample of the data.... Thank you..... 4 11 17 28 39 45 10 16 26 29 30 36 41 21 8 12 19 24 28 42 18 4 5 9 20 27 31 34 6 10 18 26 27 34 15 14 16 20 22 31 43 24 4 9 24 25 29 44 39 1 11 14 16 18 23 13 2 12 15 29 34 35 16 2 3 6 14 24 26 38 7 9 21 29 30 36 13 1 10 17 18 43 44 24 1 18 22 26 35 40 17 5 14 16 26 34 40 15 4 5 15 25 28 33 35 2 7 12 15 26 28 45 9 11 17 24 33 41 36 2 21 24 32 36 40 20 |
#2
|
|||
|
|||
Quote:
Okay so basically what you need to do is for each row you need to create a list of unique sets of two The below will do this and spit it out into one column (there will be (#rows)*(#cols) *(#cols -1) entries - each set of two will be sorted in ascending order) Then you can do a countif - so countif(J:J, J2)/2 - i have the output starting in row 2 so that an autofilter will slide straight in. the /2 in the countif is because there will be at least one duplicate for each entry, due to the nature of the VBA NOTE - data is assumed to be in activesheet and starting in cell A1 with no jumps in data (blank cells) I could have gone further and did the rest but didnt know what kind of an output you wanted. So this will output the unique identifies for which you can insert a countif to find the most frequent ones Sub Cycle() Range("A1").Select Selection.End(xlDown).Select Selection.End(xlToRight).Select last_row = ActiveCell.Row last_col = ActiveCell.Column row_out = 2 col_out = last_col + 3 For r = 1 To last_row For j = 1 To last_col y = 1 For x = 1 To last_col If x = j Then Else If Cells(r, j) < Cells(r, x) Then Cells(row_out, col_out) = Cells(r, j) & "_" & Cells(r, x) Else Cells(row_out, col_out) = Cells(r, x) & "_" & Cells(r, j) End If y = y + 1 row_out = row_out + 1 End If Next x Next j Next r End Sub Last edited by shanermuls : April 4th 13 at 01:13 AM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting of numbers, but not text, in recurring rows | Excel Worksheet Functions | |||
Recurring Numbers | Excel Discussion (Misc queries) | |||
Can you sort by most to least often recurring number in Excel? | Excel Worksheet Functions | |||
Denoting recurring numbers in formulas | Excel Worksheet Functions | |||
How do I generate non-recurring random numbers to assign groups? | Excel Discussion (Misc queries) |