Match value in cells

elmnas

Board Regular
Joined
Feb 20, 2015
Messages
206
Hello guys

I got these Columns A and B

I represent in Column B how many times the number of instances of the number in Column A are.

Now I would like to fill the rest of cells with the number of instances

see picture for Original

XkxKpGf.png


I would like to have this result then see picture below


abZtQxR.png




Could someone help me out with that code?

Thank you in advance
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi!

Have you tried to fill column B with
Code:
=COUNTIF(A$1:A$39;A1)
or
Code:
=COUNTIF(A:A;A1)
?


Regards
Anders
 
Upvote 0
how do I use this in VBA?

Ohh, I didn't understand you were using VBA. I'm no VBA expert, and neither not really sure about what you are trying to achieve, but this code should paste the formula into the cells in the B-column (row 1-100).
Code:
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Selection.AutoFill Destination:=Range("B1:B100"), Type:=xlFillDefault

But you don't want formulas in the excel sheet, only the values?
 
Upvote 0
The code works fine but It does it just to B100

I want to until last used cell in B

Could you help me to modify it?

Thank you in advance.
 
Upvote 0
solved :)

Code:
'Run 5;;;


Sub CountIDer()
Dim num_rows As Long


lastrow2 = Range("B" & Rows.Count).End(xlUp).Row


With Worksheets("Volvo_Statistik")
num_rows = .Range("A1").End(xlDown).Row


.Range("B2").Formula = "=IF(A2<>A3,COUNTIF(A$2:A2,A2),"""")"
.Range("B2:B" & num_rows).FillDown




Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Selection.AutoFill Destination:=Range("B1:B" & lastrow2), Type:=xlFillDefault


End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,203,506
Messages
6,055,807
Members
444,826
Latest member
aggerdanny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top