Count data per row and more ?

  • Thread starter Thread starter Jumbo
  • Start date Start date
J

Jumbo

Hi all,
I have in A1 to A30,000 each cell with multiple data in it (actually 3).
I have in C1 to C5,000 each cell with multiple data in it (more than 3 and
less than 30).
All data's in every single cell is separated with a space.
A1 : ac b db C1: ac b d ef
ab db hj kl m n p q
A30,000 :ab cd ef C5,000: af ab b ef cd l g
s ac db

I would like to compare each cell from A1 to A 30,000 with each cell from C1
to C5,000 and count the number of occurrence then write it in CC1.

In my short example the data "ac b db" in A1 occurs 2 times so in CC1 I will
have "2".
................................the data "ab cd ef" in A30,000 occurs only
one time so in CC30,000 I will have "1".

I need help to either write a Macro or anything else that will work to check
and count A1 against C1 to C5,000 and write the result in CC1. Then to check
and count A2 against C1 to C5,000 and write the result in CC2...etc...
I hope that my explanation are clear and simple, if not let me know.
Thank you.
Jumbo
 
Try this
It going to take excel a while to do this.


Private Sub CommandButton1_Click()
For x = 1 To 30000
For Y = 1 To 5000
If Range("A" & x) <> "" Then
If Range("A" & x) = Range("C" & Y) Then
Range("D" & x).Value = Range("D" & x).Value + 1
End If
End If
Next Y
Next x
End Sub
 
hi
use the excel function countif( range whera you want to search it,what you search

So here it will done
in CC1 write
=countif(C$1:C$5000,A1

then just do an auttofill to CC3000
 
Thank you for your help.

--
Jumbo
Mike Tomasura said:
Try this
It going to take excel a while to do this.


Private Sub CommandButton1_Click()
For x = 1 To 30000
For Y = 1 To 5000
If Range("A" & x) <> "" Then
If Range("A" & x) = Range("C" & Y) Then
Range("D" & x).Value = Range("D" & x).Value + 1
End If
End If
Next Y
Next x
End Sub




l from
Click to expand...
 
Back
Top