I have an Excel spreadsheet with two worksheets called Cities and Data. The Data page contains 108264 rows of data, and the columns move up to the AT column.
In the list “Cities” I have a list of 210 cities from lines B4 - B214. Next to it (column C) is a list of counts of codes used for each city (for example, how many codes this city used). The next 20 columns (DW columns) should show the sequence of the most frequently used codes for each city (i.e., the most common for the least common). Ive attached images with a sample pseudo-sensor to provide a graphical representation of what Im refers to.
If you look at the city “1”, for example (line 4 “Cities”), you will notice that it has a score of 5, and the most frequently used code is 5, then 4, then 3, then 2 and finally. 1. If you are referencing the “Data” image, you can see the correlation.
The array formulas that I used for this sample set are as follows:
In D4 "Cities"
{=IFERROR((MODE(IF(ISNUMBER(SEARCH(B4,Data!$B2:$B6)),IF(ISNUMBER(Data!$K2:$AT6),Data!$K2:$AT6)))),"")}
In E4 "Cities"
{=IFERROR(MODE(IFERROR(SMALL(IF(ISNUMBER(SEARCH($B$4, Data!$B2:$B6))*ISNUMBER(1/Data!$K2:$AT6)*ISNA(MATCH(Data!$K2:$AT6,$D4:D4,0)),Data!$K2:$AT6,""),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNT(Data!$K2:$AT6)))),"")),"")}
Then I drag the formula from E4 and automatically counts the frequency of frequently used codes based on the data in the previous column.
: , "" , Id 20 , B K AT "", , B, , K AT.
, (.. , , ). , Excel.
, , Ive :
- (. )
- VBA. '1004 FormulaArray Range, .
, , VBA. VBA, .
.
Sub Option1()
Dim r As Long
For r = 4 To 214
Sheet2.Cells(r, 210).FormulaArray = _
"=IFERROR((MODE(IF(ISNUMBER(SEARCH(C" & CStr(r) & ", Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"
Next r
End Sub
Sub Option2()
Sheet1.Range("C4").FormulaArray = _
"=IFERROR((MODE(IF(ISNUMBER(SEARCH(C4, Data!$B$2:$B$108264)),IF(ISNUMBER(Data!$K2:$AT108264),Data!$K2:$AT108264)))),"")"
Sheet1.Range("D4:D214").FillDown
End Sub