Hello,

I have a little problem with counting pairs of values in vertically adjacent
cells, hope you can help...

Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or Yyys.

Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs? (He
2 pairs)

I'll be very grateful for any clues...

--
Thank you very much -
Piotrek

=MIN(COUNTIF(A1:A8,"Xxx"),COUNTIF(A1:A8,"Aaa"))


"Piotrek" wrote:

Hello,

I have a little problem with counting pairs of values in vertically adjacent
cells, hope you can help...

Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or Yyys.

Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs? (He
2 pairs)

I'll be very grateful for any clues...

--
Thank you very much -
Piotrek

So...you want to know how many times the same 2 values appear consecutively
exactly twice? In your example, you are counting Aaa followed by Xxx because
that sequence appears 2 times, is that right?



"Piotrek" wrote:

Hello,

I have a little problem with counting pairs of values in vertically adjacent
cells, hope you can help...

Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or Yyys.

Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs? (He
2 pairs)

I'll be very grateful for any clues...

--
Thank you very much -
Piotrek

I'll try to be more precise :) I *do not know* how many times Aaa is folowed
by Xxx - that's what I want to know :) The fact the sequence appears twice
is just because I gave such example :) I have lots of different pairs and I
would like to know how many times given pair occurs within the given range
(it can be more than 8 cells - in fact the matrix has [you - just kidding
:)] several rows and columns filled with the values).

--
Piotrek


So...you want to know how many times the same 2 values appear
consecutively
exactly twice? In your example, you are counting Aaa followed by Xxx
because
that sequence appears 2 times, is that right?



"Piotrek" wrote:

Hello,

I have a little problem with counting pairs of values in vertically
adjacent
cells, hope you can help...

Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I
have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or
Yyys.

Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs?
(He
2 pairs)

I'll be very grateful for any clues...

--
Thank you very much -
Piotrek

Thank you Mama :)

But that's not exactly what I look for.
Your function for, e.g. Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Xxx shows "3"
although it should give "2", because we have just 2 pairs of Aaa and Xxx.

Thank you anyway :)

--
Piotrek


=MIN(COUNTIF(A1:A8,"Xxx"),COUNTIF(A1:A8,"Aaa"))


"Piotrek" wrote:

Hello,

I have a little problem with counting pairs of values in vertically
adjacent
cells, hope you can help...

Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I
have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or
Yyys.

Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs?
(He
2 pairs)

I'll be very grateful for any clues...

--
Thank you very much -
Piotrek

OK, so do you know the two values that you want to find in sequence, eh?

I created a new first column for some sample data containing row numbers and
titled Idx, titled your first column ColA, and then saved the file. Then I
opened Access and imported your data into a table named DataRange. Then I
created a query like so using SQL mode:

SELECT Count(*) AS NumPairs
FROM Datarange AS t1
WHERE (((Exists (select * from datarange t2
where t2.idx = t1.idx+1
and t2.ColA = "Xxx"))<False) AND ((t1.colA)="Aaa"));




"Piotrek" wrote:

I'll try to be more precise :) I *do not know* how many times Aaa is folowed
by Xxx - that's what I want to know :) The fact the sequence appears twice
is just because I gave such example :) I have lots of different pairs and I
would like to know how many times given pair occurs within the given range
(it can be more than 8 cells - in fact the matrix has [you - just kidding
:)] several rows and columns filled with the values).

--
Piotrek


So...you want to know how many times the same 2 values appear
consecutively
exactly twice? In your example, you are counting Aaa followed by Xxx
because
that sequence appears 2 times, is that right?



"Piotrek" wrote:

Hello,

I have a little problem with counting pairs of values in vertically
adjacent
cells, hope you can help...

Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I
have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or
Yyys.

Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs?
(He
2 pairs)

I'll be very grateful for any clues...

--
Thank you very much -
Piotrek

Let me translate that query for you - first Access adds needless sets of
parentheses, so I'll simplify it

SELECT Count(*) AS NumPairs
FROM Datarange AS t1
WHERE Exists (select * from datarange t2
where t2.idx = t1.idx+1
and t2.ColA = "Xxx"<False) AND t1.colA="Aaa";

The outer part is

SELECT Count(*) AS NumPairs
FROM Datarange AS t1
WHERE Exists (...) AND t1.colA="Aaa";


So we want to count the # of times Aaa shows up. However, we want to limit
it to the times it is FOLLOWED by Xxx, so the inner part says WHERE Exists
(...) and what must exist is this

select * from datarange t2
where t2.idx = t1.idx+1
and t2.ColA = "Xxx"<False

which means that anytime Aaa appears, we want to only consider the times
that the row with the next higher Idx (i.e., the next row) must have an Xxx
in it

For your purposes, change DataRange to whatever you name your table. You
have to have a column that numbers the riws. If you don't name it Idx, then
change all the Idx entries to whatever you name your column. Finally, change
all the references to ColA to whatever you name your column of actual data.
"Duke Carey" wrote:

OK, so do you know the two values that you want to find in sequence, eh?

I created a new first column for some sample data containing row numbers and
titled Idx, titled your first column ColA, and then saved the file. Then I
opened Access and imported your data into a table named DataRange. Then I
created a query like so using SQL mode:

SELECT Count(*) AS NumPairs
FROM Datarange AS t1
WHERE (((Exists (select * from datarange t2
where t2.idx = t1.idx+1
and t2.ColA = "Xxx"))<False) AND ((t1.colA)="Aaa"));




"Piotrek" wrote:

I'll try to be more precise :) I *do not know* how many times Aaa is folowed
by Xxx - that's what I want to know :) The fact the sequence appears twice
is just because I gave such example :) I have lots of different pairs and I
would like to know how many times given pair occurs within the given range
(it can be more than 8 cells - in fact the matrix has [you - just kidding
:)] several rows and columns filled with the values).

--
Piotrek


So...you want to know how many times the same 2 values appear
consecutively
exactly twice? In your example, you are counting Aaa followed by Xxx
because
that sequence appears 2 times, is that right?



"Piotrek" wrote:

Hello,

I have a little problem with counting pairs of values in vertically
adjacent
cells, hope you can help...

Cells from A1 to A8 contain Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Yyy. I
have
24 similar columns.
Generally, odd rows contain Aaas or Bbbs and even ones - only Xxxs or
Yyys.

Is there a way to count the amount of *pairs* of e.g. Aaas and Xxxs?
(He
2 pairs)

I'll be very grateful for any clues...

--
Thank you very much -
Piotrek

"Piotrek" wrote...
....
But that's not exactly what I look for.
Your function for, e.g. Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Xxx shows "3"
although it should give "2", because we have just 2 pairs of Aaa and Xxx.

=SUMPRODUCT(EXACT(A1:A7,"Aaa")*EXACT(A2:A8,"Xxx"))

assuming you want case-sensitive string comparisons.

But that's not exactly what I look for.
Your function for, e.g. Aaa, Xxx, Aaa, Xxx, Aaa, Yyy, Bbb, Xxx shows "3"
although it should give "2", because we have just 2 pairs of Aaa and Xxx.

=SUMPRODUCT(EXACT(A1:A7,"Aaa")*EXACT(A2:A8,"Xxx"))

assuming you want case-sensitive string comparisons.

Great! I've just made several tests and everything seems to work perfectly
so far!
Thank you very much for your help, Harlan. Thank you, everyone.
Dziekuje!

--
Piotrek