find the frequency a number appears with another number within a s

[greenmachine]

i tried using histogram but it does not return the results. I have a large
set of numbers in 5 columns. Each number represents a process(not really
important). I am trying to find how often a number appears with another
number. To explain better here are three sets of numbers,
2,13,7,15,1_1,13,19,37,35 and 2,13,8,13,5.
In this example 2 and 13 appear twice within the 3 number set. I need to be
able to anaylze the data and pull this data out. I've tried the sort feature
but it takes alot of manual sorting going through each combination. Really
confused!

 

[Ragdyer]

Not too much info.
Are the number groups in single cells?
Like
A1 = 2,13,7,15,1
A2 = 1,13,19,37,35

Then try:

=COUNTIF(A1:E100,"*2,13*")

Or, are they each in individual cells?
Does 13 followed by a 2 count also?
Does the 2 and 13, or the 13 and 2 have to be next to each other, or can
other numbers be in between?

Care to clarify if the above suggestion does not help?

 

[greenmachine]

each number is in an individual cell (many rows 5 columns). 2 would have to
be next to 13 no other number between them. I'm trying to find the number of
times 2 appears next to 13 within the set of numbers. Another example would
be how many times does 1 appear next to 3. Thanks for taking the time to
answer!

 

[T. Valko]

What result do you expect based on these samples:

2,13,1,13,2
13,2,13,2,13
1,5,13,2,13

If the result should be 4 with the numbers in the range A1:E3:

=SUMPRODUCT(--(A13=2),--(B1:E3=13))

 

[greenmachine]

if i was looking for the number of times 2 appeared next to 13; the answer
would be one. If I was looking for the number of times 13 appeared next to
2; the answer would be 4. The rest of the numbers would not be important. I
need to search the numbers and ask how many times does this number appear
next to this number with in each set (A1:E3).

 

[DanGSB]

Hi,
I'm assuming you're interested *ONLY* in the 213 and 132 sequences. First,
from what I see of your data, there's 4 ocurrences of both.
One way, if my statment is correct:
assuming data is A1:E1
in G1 enter =TEXT(A1,"General") and copy across to K1
in M1 enter =G1&H1 and copy across to P1 (relatives references, so that in
N1 you have H1&I1...)
then have 2 columns, each one counting strings 213 and 132
Q1 enter =COUNTIF(M11,"=213")
R1 enter =COUNTIF(M11,"=132")

HTH

 

[DanGSB]

didn't see your post. Simple and neat and if you change order you get the
other sequence

 

[T. Valko]

if i was looking for the number of times 2 appeared next to 13;

the answer would be one.

Well, I see 4 instances of 2 followed by 13:


[2,13],1,13,2
13,[2,13],[2,13]
1,5,13,[2,13]

Have no idea how you arrive at 1.

 

[greenmachine]

Biff , Your solution works very well and I truly appreciate the time you
took to answer my problem!

 

[greenmachine]

I wasn't very clear in my response. I was only looking at the first line of
the example data. I do want to be able to count the number of times the 2
appears next to 13 through out the range of data. The solution you provided
is what I need! Thanks for the help

if i was looking for the number of times 2 appeared next to 13;
the answer would be one.

Well, I see 4 instances of 2 followed by 13:


[2,13],1,13,2
13,[2,13],[2,13]
1,5,13,[2,13]

Have no idea how you arrive at 1.

--
Biff
Microsoft Excel MVP

if i was looking for the number of times 2 appeared next to 13; the answer
would be one. If I was looking for the number of times 13 appeared next
to
2; the answer would be 4. The rest of the numbers would not be important.
I
need to search the numbers and ask how many times does this number appear
next to this number with in each set (A1:E3).

 

[T. Valko]

You're welcome. Thanks for the feedback!