How to take values and populate a matrix using Find Method

ouadad

Board Regular
Joined
Jun 19, 2005
Messages
209
I'm not too familiar with the Find method as I've always used For loops, which take way too much time. What I'm trying to do is go down a list of over 400,000 values in four columns and then take three of those values to determine where in a matrix the fourth value should be placed. For example, here is one row of values:
Year Average UIC Group
2013 6.615384615 0002 11

1. I want to first search a horizontal list for a particular Group (there are 322 groups in the list). The list will looks like this

1 2013 2014 2015 2016 2017 2018 2019

where the first number is the Group followed by seven dates. There are an additional 321 lists like this in a horizontal range where the Groups go from 1 to 322. So using the above example, I want to be able to find Group 11.

2. Once I find the Group, I want to find the UIC 0002 (using the example) to search a vertical list of 2811 values directly under the Group label. The offset would be row 1 to 2811 and column = 0.

3. Finally, I want to search the date list to the right of the Group number to find the year, in this case 2013.

Once I find the Group, UIC and Year, I want to place the value Average in the cell with row UIC and column Year for the correct Group.

I written some code (see below), but I know it isn't correct and was hoping that someone could help me out.

Code:
Sub Data_for_Friedman_Test()
Dim year As Integer, avg As Double, factor As String, group As Integer, iRow As Long
Dim FindUIC As Range, FindGroup As Range, FindYear As Range
For iRow = 2 To 440052
    year = Cells(iRow, 1)
    avg = Cells(iRow, 2)
    factor = Cells(iRow, 3)
    group = Cells(iRow, 4)
    Set FindGroup = Range("F1:CTZ1").Find(group)
    Set FindUIC.Offset(2811, 0) = factor
    Set FindYear.Offset(0, 7) = year
    Cells(r, c) = avg
   Next iRow
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,821
Office Version
2007
Platform
Windows
If your data is like this
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.97px;" /><col style="width:55.13px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Year</td><td style="background-color:#ffff00; font-weight:bold; ">Average</td><td style="background-color:#ffff00; font-weight:bold; ">UIC</td><td style="background-color:#ffff00; font-weight:bold; ">Group</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2013</td><td style="text-align:right; ">6.6150</td><td style="text-align:right; ">001</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2014</td><td style="text-align:right; ">8.4680</td><td style="text-align:right; ">002</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2015</td><td style="text-align:right; ">10.3210</td><td style="text-align:right; ">003</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2013</td><td style="text-align:right; ">12.1740</td><td style="text-align:right; ">001</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2014</td><td style="text-align:right; ">14.0270</td><td style="text-align:right; ">002</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2015</td><td style="text-align:right; ">15.8800</td><td style="text-align:right; ">003</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2013</td><td style="text-align:right; ">17.7330</td><td style="text-align:right; ">001</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2014</td><td style="text-align:right; ">19.5860</td><td style="text-align:right; ">002</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2015</td><td style="text-align:right; ">21.4390</td><td style="text-align:right; ">003</td><td style="text-align:right; ">3</td></tr></table>

And you want something like this
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1</td><td style="text-align:right; ">2013</td><td style="text-align:right; ">2014</td><td style="text-align:right; ">2015</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2</td><td style="text-align:right; ">2013</td><td style="text-align:right; ">2014</td><td style="text-align:right; ">2015</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="text-align:right; ">2013</td><td style="text-align:right; ">2014</td><td style="text-align:right; ">2015</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">001</td><td style="text-align:right; ">6.615</td><td > </td><td > </td><td style="text-align:right; ">001</td><td style="text-align:right; ">12.174</td><td > </td><td > </td><td style="text-align:right; ">001</td><td style="text-align:right; ">17.733</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">002</td><td > </td><td style="text-align:right; ">8.468</td><td > </td><td style="text-align:right; ">002</td><td > </td><td style="text-align:right; ">14.027</td><td > </td><td style="text-align:right; ">002</td><td > </td><td style="text-align:right; ">19.586</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">003</td><td > </td><td > </td><td style="text-align:right; ">10.321</td><td style="text-align:right; ">003</td><td > </td><td > </td><td style="text-align:right; ">15.88</td><td style="text-align:right; ">003</td><td > </td><td > </td><td style="text-align:right; ">21.439</td></tr></table>

You could try a pivot table

 

ouadad

Board Regular
Joined
Jun 19, 2005
Messages
209
Yes, that works. Thank you very much Dante!

Is there some way to display all the Groups, Years and UICs, even the ones that have no values?
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,821
Office Version
2007
Platform
Windows
The dynamic table shows the information of the data, if the information is not in the data, then they are not displayed.


You could add the years in the data so that the table shows empty.
 

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top