Looking for cell contents based on another cell in excel

September 27, 2012 at 07:05:03
Specs: Macintosh
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


See More: Looking for cell contents based on another cell in excel

Report •


#1
September 27, 2012 at 07:50:17
First, a posting tip...

Please click on the blue line at 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.


Report •

#2
September 27, 2012 at 08:32:22
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.


Report •

#3
September 27, 2012 at 10:31:23
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.


Report •

Related Solutions

#4
September 27, 2012 at 11:08:34
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.


Report •

#5
September 27, 2012 at 12:01:24
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?


Report •

#6
September 27, 2012 at 13:01:53
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 Sub

You'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.


Report •

#7
September 27, 2012 at 13:02:30
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   37    

In cell K1 enter your first search number: 6
In cell L1 enter your second search number: 27

Enter 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    Match

The formula considers it a Match.

MIKE

http://www.skeptic.com/


Report •

#8
September 27, 2012 at 13:20:48
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?


Report •

#9
September 27, 2012 at 14:07:45
what does "&$K$1 do?

In cell K1 is your first search number: 6
In cell L1 is your second search number: 27

The $ 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

http://www.skeptic.com/


Report •


Ask Question