How to take values and populate a matrix using Find Method

Board Regular
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
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

Board Regular
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
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.

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

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...