a very difficult HLOOKUP?

hI, Try,

Formula:

Please Login or Register  to view this content.

Same approach for remaning numbers also

so if I understand you, you want to find out if you have 10, 14, 9, 24, 40 in any other row but in any order so if it appears again but in this order 24, 9, 40, 14, 10 then you want to count that, correct?

well based on the data you posted, i don't see a single instance of a repeating set of values though i had to do it by hand, it wasn't very difficult but it took me a couple steps to do it. But the way I did it, keeping your data set together in columns A through E, in col F i did a sum of A4:E4 copied down. Then took those values and copied them elsewhere as values, then removed duplicates, then sorted cols A:F on F ascending, then did a count of the summed values and any values where there were more than 1 I checked to see if the two rows matched in any order. But if you have a large dataset this could be time consuming.

Here is your table back with my brief explanations of how i would approach it.

well the assumption I made is that if the numbers are the same in any row but in a different order, then the sum of those numbers will have to be the same. That is why I summed them.

Hey, I understand, you have a lot of rows to look through doing that by hand. Good luck and I'll watch to see what someone else offers.

Hi.

What's the "Top Five Numbers" bit for?

Which numbers are you searching for? Where are they in this workbook?

Regards

Originally Posted by stavroseco

I want to know this sequence of numbers (10,14,9,21,40) if they appear and if yes how many appearances in the array of data A4:91

In cells J2:N2 I have listed the 5 numbers you want to match.

In cell F2 I have put this formula which will only show a 1 if all 5 numbers match:
=--(SUMPRODUCT(--(ISNUMBER(MATCH(A4:E4, $J$2:$N$2, 0)))) = 5)

I've copied that formula down the data set.

In N2 is this formula to give the count of matches:
=SUM(F:F)

Jerry's has to be the most practical (and efficient) solution, though if for whatever reason you want the count using a single formula:

=SUM(N(MMULT(N(COUNTIF(OFFSET(A5:E5,ROW(A5:E100)-MIN(ROW(A5:E100)),,,),A4:E4)=COUNTIF(A4:E4,A4:E4)),{1;1;1;1;1})=5))

Change the upper range reference (100 here) as required.

Regards

Hi Stavros,

Here's a VBA for only your stated goal:

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi Stavros,

Here are instructions for running a macro (VBA):

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name


*But this routine only works on the one that you described in your first post - I'm thinking of a program that will deliver what you seem to actually want - shall I pursue it??

Hi Stavros,

I think that this is what you meant:

Please Login or Register  to view this content.

I think what avendi is telling you is the same thing I told you in post #6 (i think). If the values are, say 10, 42, 9, 16, 12, they would add up to 89. If in another row you have 9, 42, 10, 12 and 16, they also add up to the same 89. You can't have the same values (same or different order) in two different rows that will NOT add up to the same total value. (just my guess since I dropped off the thread a while ago.)

The reasons I forwarded that as part of the solution are:
1) I thought your count of rows was managable
2) it would be a way by hand to find rows that had the same OVERALL value and see if they had the same #s but different order.
It was extraneous to your needs but part of my solution.

So if your problem is solved don't forget to mark the post as solved and certainly add to the reputations of those who helped you. It is cheap, dosen't cost anything. and makes people happier that they spent time and effort trying to assist you.