Compare numbers in two columns in Excel

[Mollethewizard]

I’ve got a spreadsheet with 8 numbers in column A from A1 to A8. In column B there will be 20 numbers from B1 to B20. I want to compare each number in column A with the numbers in column B and present the number of matches in cell A10.

Anyone – suggestions for code.

Mollethewizard

 

[MeGustaXL]

Hello Mollethewizard

Type this formula in C1:

=SUMPRODUCT(IF(A1=$B$1:$B$20,1,0)

Enter it as an Array Formula by pressing CTRL+SHIFT+ENTER, then copy it down to C8 - Bingo!

HTH


Chris

Varium et mutabile semper Excel

 

[xlbo]

No code needed - you can use either an array formula or sumproduct
non array:
=SUMPRODUCT(COUNTIF(B1:B20,A1:A8))

array:
=SUM(COUNTIF(B1:B20,A1:A8))
(entered with CTRL+SHIFT+ENTER)


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MeGustaXL]

Geoff: Same cat, just another strip of hide!

Chris

Varium et mutabile semper Excel

 

[Bowers74]

=SUMPRODUCT((COUNTIF(B1:B20,A1:A8)))

Will do what you want!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

DOH! Should've read the question!

George: Use Geoff's

Geoff: I bow to the Master's superior reading and typing ability!



Chris

Varium et mutabile semper Excel

 

[Bowers74]

And I guess I should Refresh my browser more often before I post an answer! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

heh heh heh BTW you can remove a set of brackets from yours Mike...

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Aren't we picky today, Geoff!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Mollethewizard]

Formulas are fine, but what about some VBA code?

Tank you for your help so far.

Mollethewizard

 

[xlbo]

why use code when you can enter a formula ???? If you really want to use code, just use:

Range("A10&quot.formula = "=SUMPRODUCT(COUNTIF(B1:B20,A1:A8))"

or is that just too basic for ya ;-)

What's the point in having some looping code with IF statements ??? IMHO, if it can be done in native excel (or any other app for that matter) it should be done that way - code should be a way of either automating long winded manual tasks or performing an action cannot be done in the native app


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[MeGustaXL]

Attach this to a Button and Click it:

   With Range("A10")
    .FormulaR1C1 = _
        "=SUMPRODUCT((COUNTIF(R[-9]C[1]:R[10]C[1],R[-9]C:R[-2]C)))"
    .Interior.ColorIndex = 19
    End With

Or see Geoff's last.

Chris

Varium et mutabile semper Excel

 

[Bowers74]

If you insist:

Sub CountMatches()
Dim ValueRange As Range, MatchRange As Range, c As Range, i As Integer
Set ValueRange = [A1:A8]
Set MatchRange = [B1:B20]
For Each c In MatchRange
    If Not ValueRange.Find(c) Is Nothing Then
        i = i + 1
    End If
Next c
[A10] = i
End Sub

;-)

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MeGustaXL]

Heh, heh, heh! Just how many layers of skin does this Kitty have, anyway!

Chris

Varium et mutabile semper Excel

 

[Mollethewizard]

Geoff and all others, I really appreciate all your efforts to solve my problems. But I don’t appreciate when someone tells me how things should be done and in what way!

That is to put oneself on a pedestal looking down on other people that don’t have the wisdom!

Mollethewizard

 

[MeGustaXL]

Mollethewizard, sincere apologies for any patronising tone that might have come thru' in my posts; no offence intended.

That said, we have provided you with 2 formulas and 3 bits of code, ANY and ALL of which will do what you ask, with no telling you "how things should be done and in what way - just pick the method which suits you best.

"If you can't be the best at what you do, worst is probably good enough" Homer Simpson



Chris

Varium et mutabile semper Excel

 

[MeGustaXL]

Damn! forgot to turn off bold and itals

Chris

Varium et mutabile semper Excel

 

[xlbo]

I'm not telling you how to do things - IMHO = In My Humble Opinion

and IMHO, to code by looping is wasting time and system resource when it could be done with either a formula or a simple one liner to insert the formula
If you don't want the formula to appear, just use:

With range("A10&quot
.formula = "=SUMPRODUCT(COUNTIF(B1:B20,A1:A8))"
.copy
.pastespecial paste:=xlpastevalues
end with

You ask how to do something - I'm not just here to give you 1 answer to make your immediate problem go away - I would far rather give a bit more info so that you can make an informed decision and maybe arm yourself better for such decisions in the future. Whether you use the loop or a formula or code to insert a formula makes no difference to me but I pride myself on having learnt quite a lot about excel and VBA and I have and will continue to express my opinion as to what is good and bad practice in those areas.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

If you don't want to have someone tell you how things should be done then you shouldn't really ask "How can this be done?"

Geoff made an excellent point, in helping you try to understand the difference between the use of code and the use of formulae.

There really is no reason to do what you want in code, unless you just wanted to see how to do something like this by using code (or you were asked to show someone else how).

There are few people in this forum who belong on a pedestal, and we all know who one of them definately is! ;-)

You should just appreciate the knowledge you have just received from those who are more eperienced than you are, and realize that you too have leaned something new.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!