Autofil pairs of rows

[AutoTV]

Dear Excelforum community,

I am far from expert so this might be as simple as a function I don't know about.

I think the attached is self-explanatory, but in case you need a real-life example :
A, B and C would be 3 persons
a and b would be 2 restaurants
lines would be dates or 1 2 3...

A, B and C are most of the time in the same city, but sometimes they travel. When they are out of town, this is recorded with a 3.
I record every time A, B or C are going to either a or b restaurant. This is recorded with a 1.
Then I want to calculate a matrix of how many times A+B were in the same restaurant in the same day, then A+C, then B+C, etc... for 30+ people.
I already have that matrix.

now I want to know how many times they have been in town together. Meaning counting all the lines with no 3 in either pair, for all the pairs.

Final things will be easy, but will be to calculate a %age of times each pair have been to the restaurant the same day KNOWING they were both in town.
If A and B have been to the restaurant together 5 times over a period of 15 days, but B was out of town for 5 days during that period, then my final matrix will show AB : 5/10.

[biex]

I try

J10

Please Login or Register  to view this content.

J11

Please Login or Register  to view this content.

J12

Please Login or Register  to view this content.

K11

Please Login or Register  to view this content.

K12

Please Login or Register  to view this content.

L12

Please Login or Register  to view this content.

[AutoTV]

Thanks biex ! It does work for that example, but I can't find out how to autofill those formulas for them to work over the 961 cases I need in the real-life dataset. Any ideas ?

[biex]

Thanks biex ! It does work for that example, but I can't find out how to autofill those formulas for them to work over the 961 cases I need in the real-life dataset. Any ideas ?

For that... it will be easy if you show us the real template that you use with minimal data or without data so we could adjust the formula with it.

Then you could copy and paste the data on that template

[AutoTV]

Original data is same as example (ABC) but from cell D4 to BM4 (31 individuals with 2 rows for each) over 651 lines (end at D654 to BM654).

If I try to autofill your provided formulas down or right to fill up a 31x31 = 961 cells matrix, the formulas excels autofills with are no good.

For example, your formula =J10-COUNTIF(D:E;3)
will autofill down with =J11-COUNTIF(D:E;3)
instead of with the desired =L12-COUNTIF(D:G;3)

[AutoTV]

Hi everyone,

I'm still looking for help on that one.
In order to help you help me : ) I have attached an updated dataset with 2 new sheets :

the sheet1 is still the same and explain what I want

the sheet2 is a longer example with just a few data filled but the whole frame with number of id is there,

the sheet3 is the results I'm looking for : to be able to autofill that matrix from one formula to autofill all.

If you can find a formula that would fill sheet3 based on sheet2 dataset it would be awesome !


Thanks again !