Counting rows and updating group ID with the count

[NewVBACoder]

Thanks for reading this.

I have a spreadsheet column called GroupNumber (1,2,3..).
Each group can have from 2 to 35 rows of data. (Group 1 - 4 rows; group 2 = 16 rows, etc.

The amount of rows I need to process can be any number from 100 to 1000 or more. Currently I have to do the update manually so any help is appreciated.

I need to count each row in a column for group 1, 2 etc. and update a range of cells in a column called VariationCount with the total number of variations for the group.

Data Row | Group Number | Variation Count
Data Row | 1 | 3
Data Row | 1 | 3
Data Row | 1 | 3
Data Row | 2 | 4
Data Row | 2 | 4
Data Row | 2 | 4
Data Row | 2 | 4
Data Row | 3 | 2
Data Row | 3 | 2
Etc.

At first I thought about using a loop to count the rows where group number = x but I am having trouble figuring out how to apply it. Then I have been trying to use the range.cells property but again I am having troublw with it.

I have only been studying VBA coding for 3 weeks so any help is appreciated.

Also if anyone knows a good tutorial website that info is also appreciated.

Thanks.

NewVBACoder

 

[jproffer]

Maybe this...

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B7</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B9</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A9</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B10</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A10</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B11</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A11</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B12</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B13</th><td style="text-align:left">=COUNTIF(<font color="Blue">$A$2:$A$13,A13</font>)</td></tr></tbody></table></td></tr></table><br />

 

[NewVBACoder]

Thanks for the reply.

The formula works when I type it into the cell and copy and paste to the other cells.

What I would like to do is code it into VBA so that only the value and not the formula is in the cell.

I have been trying to use the worksheet function to apply the CountIf function to a variable and then display the value in the cells.

Thanks again for your help. I'll keep plugging away.

 

[jproffer]

Sub CountIfAndPasteValues()
Dim lstrw As Long
Dim i As Long
 
lstrw = Range("A" & Rows.count).End(xlUp).Row
    For i = 1 To lstrw
        Range("B" & i).Value = "=CountIF($A$1:$A$" & lstrw & ",A" & i & ")"
    Next i
 
    Range("B1:B" & lstrw).Value = Range("B1:B" & lstrw).Value
 
End Sub

This seemed to work for me. Try it on a COPY and make sure

BTW, this assumes data in column A and results in column B...change according to your reality.

 

[jproffer]

I guess for a complete answer, I used the above code on this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; WIDTH: 170px; BORDER-COLLAPSE: collapse; HEIGHT: 438px; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet2​

...and came up with this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; WIDTH: 161px; BORDER-COLLAPSE: collapse; HEIGHT: 438px; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet2​

This is all values....no formulas remaining.

 

[NewVBACoder]

AWESOME!

Thanks a lot. It gave me the result I wanted.

Now I just have to make sure I don't screw it up when change the variables and stuff.

Thanks again

 

[jproffer]

LOL...variables should be fine. Change "A" to whatever column your data is in and change "B" to whatever column you want your results in.

 

[NewVBACoder]

Thanks again! I have been fighting this for the last 3 days.

I was able to adapt it to my spreadsheet after updating the variables and columns.

I could find information in MS Excel help for everything except (.End(xlUp).Row) at the end of the lstrw variable. What does that mean?

Thanks again!

 

[jproffer]

Basically means find the last row that has data in it in column A.

Put this:

Sub test()
Dim lstrw As Long
lstrw = Range("A" & Rows.Count).End(xlUp).Row
MsgBox lstrw
End Sub

in a standard module, and play around with it. Put something in several rows of column A...run it....skip a few rows in column A and put more data below the skipped rows...run it again........

lstrw ends up being a whole number = row number

 

[NewVBACoder]

Thanks again,

Now I am off to tackle putting data into an array.

The purpose of the variation count value is to help me establish the array size. Also, the last line of the code you gave me will help me copy the values from one row to another after I change the variables.

Thanks again.