I have an excel spreadsheet that contains a series of 5 number and a date. I am looking for every occurrence of a second number in a row if I find the first number anywhere along that row. for example - I want to find every occurrence of 27 if I find 6 first.

9/26/12 2 18 19 35 36

9/25/12 12 20 35 36 37

9/24/12 11 18 22 32 39

9/23/12 6 10 27 35 39

9/22/12 6 23 27 32 37

9/21/12 2 33 34 35 39

9/20/12 13 17 18 19 37

9/19/12 10 12 15 27 34

9/18/12 4 16 17 35 38

9/17/12 2 3 5 9 21

9/16/12 6 15 22 33 36

First, a posting tip... Please click on the

blue lineat the end of this post and read the instructions on how to post example data in this forum.As for your question, what do you mean by "find" every occurrence of e.g. 27? Do you want the address returned? Do you want to highlight it?

Please clarify.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I don't know if this is what you are looking for, but it was fun to try so I'll toss it out there: Assuming your data starts in A1, and your 6 is always found in Column B, this formula will return one of 3 answers:

1 - "No 6" if there is no 6 in Column B of that row.

2 - "6 but no 27" if there is a 6 in Column B but no 27 in any other column.

3 - The address of the cell that contains 27 if there is a 6 in Column B and a 27 anywhere in Column C:F e.g. $D$4 in your example above.Put this in G1 and drag it down.

=IF(B1=6,IF(ISNA(MATCH(27,A1:F1,0)),"6 but no 27",ADDRESS(ROW(),MATCH(27,A1:F1,0))),"No 6")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks for your reply, but no, 6 is not always found in column B. Ultimately, I would like the function to do something like the COUNTIF does; gives a result of how many times my criteria happens in my spreadsheet. My criteria might be looking for the number of occurrences of 2 specific numbers, up to 5 specific numbers.

My spreadsheets has thousands of entries.

Thanks for the posting instructions.

I think you will need some VBA to do what you want, especially if you are looking for a specific value before another. For example, counting how many rows have a 6 and a 27 in them might be doable with a combination of formulas SUMed together, but I don't think it can be done with formulas if the 6 must come before the 27. Formulas can't really loop through a range and make decisions based on what they find.

However, VBA can do that with For-Next loops and If-Then statements. We would need to know some more specifics before any code could be written.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

What kind of specifics do you need to know? The data in the spreadsheet is always in numerical order - the smallest is always first in each row. So . . . . Find "a". If "a" = yes, is there a "b"? If yes, increment counter by one.

VBA - visual basic?

You mentioned sometimes needing to the "number of occurrences of 2 specific numbers, up to 5 specific numbers." Where will the criteria for each run be found? Will you put those numbers in a range of cells or enter them from the keyboard?

The code could be written to read a range of cells for the input criteria or it could present a series of "Input Boxes" for the user to enter the criteria into.

You said the spreadsheet has thousands of entries. Thousands of rows that look like your example or thousands of columns or thousands of both?

That's the kind of specifics I mean.

Make believe that I am sitting way over here where I can't see your spreadsheet or know what process you are following. The more details you supply, the better the odds that the solution offered will be work right the first time.

VBA - Visual Basics For Applications

It is essentially Visual Basic, but each MS Office application has a specific version of VBA with instructions that only work within that application. Since the VBA language is very specific, just like an Excel formula, it needs to know where to look for the input data, what to do with it, what range to search in, where to put the output, etc.

For example, your original data has no Row or Column labels. I would need to know where the data is on the spreadsheet - or at least the starting cell. As long as there is nothing below the data or to the right of it, there are VBA commands that can determine the last Row and/or Column that has data in it. Once that's known the code can determine the aea to be searched for your criteria - assuming it also knows where to get the criteria from.

For example, this code will search B1:J15 for the value in A1 and place the address in A2:

Sub FindA1() 'Get Value To Search For myVal = Range("A1") 'Set Search Range With Range("B1:J15") 'Search For Value Set v = .Find(myVal) 'Output Address or Message To A2 If Not v Is Nothing Then Range("A2") = v.Address Else Range("A2") = "Value Not Found" End If End With End SubYou'll note that I had to tell the code where to find the input, what range to search and where to put the output.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

If your data is always in ascending order, then try this: With your data like this:

A B C D E F G 1) 9/26/2012 2 18 19 35 36 2) 9/25/2012 12 20 35 36 37 3) 9/24/2012 11 18 22 32 39 4) 9/23/2012 6 10 27 35 39 Match 5) 9/22/2012 6 23 27 32 37 Match 6) 9/21/2012 2 33 34 35 39 7) 9/20/2012 13 17 18 19 37In cell K1 enter your first search number: 6

In cell L1 enter your second search number: 27Enter this formula in Cell G1 and drag down.

=IF(COUNTIF(B1:F1,"="&$K$1),IF(COUNTIF(B1:F1,"="&$L$1),"Match",),"")

It should indicate the two lines that have your Search Numbers

with the word Match.BUT, your data must be in Ascending order, because the formula

will find your two search number, regardless of where they

appear on the line.If you have a line:

A B C D E F G 1) 9/26/2012 27 18 6 35 36 MatchThe formula considers it a Match.

MIKE

Excellent, gentlemen First, input boxes would work best - don't know any VB, much less VBA so didn't quite get there in my thought processes.

My spreadsheet is currently 5990 rows of data that is a date in column A and individual numbers in columns B - F.

Ok. Mike - what does "&$K$1 do? How do I alter if I want to search on more than 2 criteria?

what does "&$K$1 do?In cell K1 is your first search number: 6

In cell L1 is your second search number: 27The $ symbol, anchors the cell so when you copy down

the column and row stay the same and are not incremented.The & is the Concatenation symbol, it combines the string "="

and the anchored cell number, so the formula evaluates to "=6" or "=27"(COUNTIF(B1:F1,"="&$K$1) is the same as

(COUNTIF(B1:F1,"=6")To search for more than 2 criteria you will need to add another

nested IF COUNTIF section.Try this: (Untested)

In cell M1 enter your third search number: 39

Enter the formula:

=IF(COUNTIF(B1:F1,"="&$K$1),IF(COUNTIF(B1:F1,"="&$L$1),IF(COUNTIF(B1:F1,"="&$M$1),"Match","")),"")

MIKE

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History