Count matching data Vertical verses Horizontal values.

Hello Everyone!

I need a Formula and (VBA also to work with larger data) which can count matching data Vertical verses Horizontal values.

For example I got 4 horizontal values in A6:D6 and 22 vertical values in the columns G1:AB4. I want A6:D6 value count match greater than 2, with each value is in G1:G4, H1:H4?.till AB1:AB4 and show result in range G6:AB6.

Note: If possible formula and VBA can work with excel 2000 also.

Example sheet is attached.

Thanks and regards,
Moti

Originally Posted by AliGW

Unclear - formula or VBA or both or either???

Which version of Excel do you have?

Hello AliGW, I need formula & VBA both if possible? I am using version 2010 & still older version 2000 also where I have got some programs so I request please formula & VBA could work with both version will be great always if possible. Thank you.

Good Luck

Thanks and regards,
Moti

Originally Posted by bebo021999

I don't think it's that simple. Is your actual data always 1, 1, 1, 1 in the range A6:D6, or can it be any numbers? For example, 15, 80, 9, 200. I assume it can be any numbers, and you want a VBA solution.
Please confirm.

Hello bebo021999, I guess my question was not clearly explained, here is a new sheet where I need a Formula or VBA it is ok no problem in this example range G6:AB22 but range could be adjustable if it is VBA solution.

Thank you.

Thanks and regards,
Moti

Originally Posted by AliGW

If you had something newer, this would probably work:

=IF(COUNT(IF(MATCH(G1:G4,TRANSPOSE($A$6:$D$6),0)=1,))>2,"Match","")

But I'll leave you in Bebo's capable hands.

Hello AliGW, I needed match could be counted, any ways thank you for your help. lets wait for Bebo's reply

Good Luck

Thanks and regards,
Moti

Originally Posted by AliGW

The count is this bit:

=COUNT(IF(MATCH(G1:G4,TRANSPOSE($A$6:$D$6),0)=1,))

Hello AliGW, unfortunately formula does not give the result as per #post7 it must count match greater than 2 only and with different values as shown.

Please can you check?

Good Luck

Thanks and regards,
Moti

Try this,

G6

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter. Copied down and across.

Originally Posted by windknife

Try this,

G6

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter. Copied down and across.

Hello windknife, yes your formula worked spot on! For both 2010 and 2000 Version perfect.

I appreciate your help and time you took for solving by formula.

Is it possible to get VBA?

Have a nice day and Good Luck

Thanks and regards,
Moti

Originally Posted by AliGW

That's not what you asked for!

=IF(COUNT(IF(MATCH(G1:G4,TRANSPOSE($A$6:$D$6),0)=1,))>2,COUNT(IF(MATCH(G1:G4,TRANSPOSE($A$6:$D$6),0)=1,)),"")

Hello AliGW, unfortunately this also does not give the require result.

I appreciate your help.

Have a nice day and Good Luck

Thanks and regards,
Moti

Originally Posted by bebo021999

If you want to compare one-by-one: A6 vs G1, B6 vs G2, C6 vs G3, D6 vs G4 then count the match

In G6:

Please Login or Register  to view this content.

Confirmed with Ctrl-Shift-Enter
Drag down and accross
In case "match" returns, try:

Please Login or Register  to view this content.

This is a formula-based solution. I'm not sure if it will impact the processing speed of your computer with large data. If it runs too slow, please let me know, and I can provide you with VBA code as an alternative.

Hello bebo021999, yes both formulas worked with both 2010 and 2000 versions Spot On!
I like the Match formula also.

I appreciate your help and time you took for solving by formula.

Have a nice day and Good Luck

Thanks and regards,
Moti

Originally Posted by AliGW

It does in the file you posted first!

Good luck!

Hello AliGW, yes if I compare with first post your formula does give the result as request that is perfect! But later I realize my error and corrected that in #post7, I think you did not see that that has create the confusion I am sorry I agree your formula is working fine with opening post.

I appreciate your help.

Have a nice day and Good Luck

Thanks and regards,
Moti

Originally Posted by kvsrinivasamurthy

Formula. ARRAY formula In G6 copied to full range

Formula:

Please Login or Register  to view this content.

VBA code:

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

Hello kvsrinivasamurthy, both VBA & Formula options worked great! Also it results fine with my both versions 2000 & 2010.

I appreciate your help and time you took for solving my query.

Have a nice day and Good Luck

Thanks and regards,
Moti

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Originally Posted by AliGW

Then why didn't you post more realistic sample data at the outset? Hopefully you will learn from this.

Hello AliGW, yes sure I will thank you.

Have a nice day and Good Luck

Thanks and regards,
Moti

Originally Posted by bebo021999

Nice to hear it works.
And here is VBA solution. Click the button "RUN" to run the code.

PHP Code: 

Option Explicit
Sub matching()
Dim i&, j&, k&, t&, VTable, HTable, rng, res()
VTable = Range("A6").CurrentRegion.Value ' This is left table (Vertical)
HTable = Range("G1").CurrentRegion.Value ' This is top table (Horrizontal)
ReDim res(1 To UBound(VTable), 1 To UBound(HTable, 2)) ' result array

'Loop thru each single cell in result array, then
For i = 1 To UBound(res)
    For j = 1 To UBound(res, 2)
        k = 0
        
        'compare each cell in VTable vs HTable, then count the "matching" (k)
        For t = 1 To UBound(VTable, 2)
            If VTable(i, t) = HTable(t, j) Then k = k + 1
        Next
        If k > 2 Then res(i, j) = k ' save the "count" into result range
    Next
Next
Range("G6").Resize(UBound(res), UBound(res, 2)).Value = res ' paste the array into sheet
End Sub 


Hello bebo021999, VBA also operated spot on! And thank you for adding additional notes in the VBA which field and where to change ranges as required.

I appreciate your help and time you took for solving this request.

Have a nice day and Good Luck

Thanks and regards,
Moti

Originally Posted by windknife

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Hello windknife, it is done!

Have a nice day and Good Luck

Thanks and regards,
Moti