compare two arrays a1:a7 compare to b1:b7, count how many are the same?
- Thread starter henksss
- Start date
jskushawah
New Member
Welcome to the forum
Read this post:
http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/
and
http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/
you will find your answer.
Read this post:
http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/
and
http://chandoo.org/wp/2010/07/01/compare-lists-excel-tip/
you will find your answer.
Kyle McGhee
Member
Hi henksss,
Array enter this formula (ctrl+shift+enter while in the formula bar):
=SUMPRODUCT(--(MMULT((A1:A7),TRANSPOSE(1/(B1:B7)))=1))
The link just provided will colour the duplicates but will not count them into a cell, if that is what you need.
Kyle
Array enter this formula (ctrl+shift+enter while in the formula bar):
=SUMPRODUCT(--(MMULT((A1:A7),TRANSPOSE(1/(B1:B7)))=1))
The link just provided will colour the duplicates but will not count them into a cell, if that is what you need.
Kyle
Kyle McGhee
Member
henksss,
The formula provided returns the same value as you are looking for, 3. My comment about the link related to the post prior to mine, not my formula.
sreekhosh, thanks
The formula provided returns the same value as you are looking for, 3. My comment about the link related to the post prior to mine, not my formula.
sreekhosh, thanks
G
Guest
Guest
For example if you are interested in a unit that has a glass top you should make sure that the glass has been strengthened.,isabel marant shoes
Mendel frock that had a sweetheart neckline and draping fabric." KStew wore a gorgeous strapless off white J. could lead to such worrying events as identity theft, the implementation of medical information technology solutions may be something which both medical professionals and patients can welcome. Kim Kardashian celebrated Easter with her family in Los Angeles. white pleated mini skirt and sky-high Christian Louboutin heels,Isabel Marant Sneakers Bekket High-top Suede Beige White, London is also renowned for Europe's biggest fetish club where the visitor's dress code corresponds to the theme of the club. Reminiscent of the cabarets of another era such as the famous Moulin Rouge in Paris,isabel marant sneakers, Most of the Driver Authorization System also includes Keypad Unit, the basic process is somehow remains same.
"We've got a lot of people out there today that are a little sadder today because Thomas Kinkade passed away," ___ Associated Press writers John Marshall,isabelle marant, Mainly the driver authorization system is made for commercial vehicles as well as different machinery vehicle.
Related articles：
Mendel frock that had a sweetheart neckline and draping fabric." KStew wore a gorgeous strapless off white J. could lead to such worrying events as identity theft, the implementation of medical information technology solutions may be something which both medical professionals and patients can welcome. Kim Kardashian celebrated Easter with her family in Los Angeles. white pleated mini skirt and sky-high Christian Louboutin heels,Isabel Marant Sneakers Bekket High-top Suede Beige White, London is also renowned for Europe's biggest fetish club where the visitor's dress code corresponds to the theme of the club. Reminiscent of the cabarets of another era such as the famous Moulin Rouge in Paris,isabel marant sneakers, Most of the Driver Authorization System also includes Keypad Unit, the basic process is somehow remains same.
"We've got a lot of people out there today that are a little sadder today because Thomas Kinkade passed away," ___ Associated Press writers John Marshall,isabelle marant, Mainly the driver authorization system is made for commercial vehicles as well as different machinery vehicle.
Related articles：
shrivallabha
Excel Ninja
Hi mercatog,
Please check your second formula with following data:
[pre]
[/pre]
It seems Frequency handling is bit unstable as it sometimes gives correct results while sometimes it doesn't.
I tried with
=SUM((FREQUENCY(A2:A8,B2:B8)>=1)+0)
which gives results correctly sometimes but sometimes it doesn't.
Please check your second formula with following data:
[pre]
Code:
1 8
2 7
3 6
4 5
5 4
6 3
7 2It seems Frequency handling is bit unstable as it sometimes gives correct results while sometimes it doesn't.
I tried with
=SUM((FREQUENCY(A2:A8,B2:B8)>=1)+0)
which gives results correctly sometimes but sometimes it doesn't.
Hi shrivallabha
You all right, the function frequency had a n+1 size
Here 2 ways I found to answer to the problem
If A8 non numeric
You all right, the function frequency had a n+1 size
Here 2 ways I found to answer to the problem
If A8 non numeric
Code:
=SUMPRODUCT((FREQUENCY($A$1:$A$7,$B$1:$B$7)>0)*ISNUMBER($A$1:$A$8))
Or
=SUMPRODUCT((FREQUENCY($A$1:$A$7,$B$1:$B$7)>0)*(ROW($A$1:$A$8)<=ROWS($A$1:$A$7)))I have a new question re conditional format.
On the toolbar in excel 2003 there are only 3 conditional formats possible, I need 7 conditional formats.
a1:a100 can be amounts 0,1,2,3,4,5,6 as the formulas in the aray will give these amounts.
I need to enter into vb the code to give me 7 different colours, one for each number 0-6.
On the toolbar in excel 2003 there are only 3 conditional formats possible, I need 7 conditional formats.
a1:a100 can be amounts 0,1,2,3,4,5,6 as the formulas in the aray will give these amounts.
I need to enter into vb the code to give me 7 different colours, one for each number 0-6.
Hi
Here is code vba which color each cell of A1:A100 in Sheet1 depending of values of these cells
[pre]
[/pre]
Here is code vba which color each cell of A1:A100 in Sheet1 depending of values of these cells
[pre]
Code:
Sub CondForm()
Dim ClrInd As Integer
Dim c As Range
For Each c In Sheet1.Range("A1:A100")
Select Case c.Value
Case "": ClrInd = xlNone
Case 0 To 6: ClrInd = c.Value + 35 '(the max in colorindex is 56, so choose correctly)
Case Else: ClrInd = xlNone
End Select
c.Interior.ColorIndex = ClrInd
Next c
End SubYou should Run it (for your case)
[pre]
[/pre]
[pre]
Code:
Sub CondForm()
Dim ClrInd As Integer
Dim c As Range
For Each c In Worksheets("enter results").Range("L3:L1000")
Select Case c.Value
Case "": ClrInd = xlNone
Case 0 To 6: ClrInd = c.Value + 35 '(the max in colorindex is 56, so choose correctly)
Case Else: ClrInd = xlNone
End Select
c.Interior.ColorIndex = ClrInd
Next c
End Sub
Now if you wish that it runs automatically when the user changes values in L3:L1000, we can use the event Change of the worksheet
Code to copy in Module of your Sheet and notice if values in column L change by formulas, the code won't work
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ClrInd As Integer
Dim c As Range
If Not Intersect(Target, Range("L3:L1000")) Is Nothing Then
For Each c In Intersect(Target, Range("L3:L1000"))
ClrInd = xlNone
Select Case c.Value
Case ""
Case 0 To 6: ClrInd = c.Value + 35
End Select
c.Interior.ColorIndex = ClrInd
Next c
End If
End SubHi Debraj Roy
Спасибо тебе за комплимент! The translator aa
Translated: Thank you for the compliment!
Can you help me to explain to henksss? Thank you
@henksss
Nothing works or you can't make it work?
Edit: You answered! The 2nd code won't automatically work
The code
[pre]
[/pre]
needs to be run that the user execute this sub by a shortcut or a commandbutton
Спасибо тебе за комплимент! The translator aa
Translated: Thank you for the compliment!
Can you help me to explain to henksss? Thank you
@henksss
Nothing works or you can't make it work?
Edit: You answered! The 2nd code won't automatically work
The code
[pre]
Code:
Sub CondForm()
Dim ClrInd As Integer
Dim c As Range
For Each c In Worksheets("enter results").Range("L3:L1000")
ClrInd = xlNone
Select Case c.Value
Case ""
Case 0 To 6: ClrInd = c.Value + 35
End Select
c.Interior.ColorIndex = ClrInd
Next c
End Subneeds to be run that the user execute this sub by a shortcut or a commandbutton
everything in my sheet works but not the vb code.
I have formulas in that range which give results (0,1,2,3,4,5,6.)end.
i need vb to give conditional format colour, to cells, depending on which of the seven numbers comes up as a result of the formula.
example: if 6 the colour red, if 5 colour blue, if 4 colour green, etc.
seven different colours only , one for each number 0-6
regards henk
I have formulas in that range which give results (0,1,2,3,4,5,6.)end.
i need vb to give conditional format colour, to cells, depending on which of the seven numbers comes up as a result of the formula.
example: if 6 the colour red, if 5 colour blue, if 4 colour green, etc.
seven different colours only , one for each number 0-6
regards henk
Debraj
Excel Ninja
Hi Henksss,
Open sheet >> Press Alt + F11 >> Insert a New Module >> Paste the below Code..
[pre]
[/pre]
Now Press F5..
If still is it doesn't work, Please upload file, we will adjust and set the file for you..
Crossing Finger..
Deb
Open sheet >> Press Alt + F11 >> Insert a New Module >> Paste the below Code..
[pre]
Code:
Sub CondForm()
Dim ClrInd As Integer
Dim c As Range
For Each c In Sheets(""enter results").Range("L:L")
Select Case c.Value
Case "": ClrInd = xlNone
Case 0 To 6: ClrInd = c.Value + 35 '(the max in colorindex is 56, so choose correctly)
Case Else: ClrInd = xlNone
End Select
c.Interior.ColorIndex = ClrInd
Next c
End SubNow Press F5..
If still is it doesn't work, Please upload file, we will adjust and set the file for you..
Crossing Finger..
Deb