Multiple sheet formula

[Stuart Emerson]

I have two spreadsheets containing names in rows. I need
a formula to tell me which of these names appears six
times on the first sheet and those that don't. Of the
names that do I then need to filter into two seperate
lists those that appear in any coloumn on the second sheet
in six consecutive rows and those that don't. I will then
end up with three lists of data.

1. Those that are not on the first spreadsheet six times
2. Those who are on the first spreadsheet six times but
not the second in six consecutive rows
3. Those who are on the first spreadsheet six times and
on the second spreadsheet in six consecutive rows

 

[Harlan Grove]

...
...

1. Those that are not on the first spreadsheet six times
2. Those who are on the first spreadsheet six times but
not the second in six consecutive rows
3. Those who are on the first spreadsheet six times and
on the second spreadsheet in six consecutive rows

The second list is sorted by name? Is either list a proper superset of the other
(meaning contains all names in the other list possibly as well as other names)?

Anyway, if the first list were in Sheet1!A1:A1000, use column B for counting the
first list, with these formulas.

B1:
=--(COUNTIF(A2:A$1000,A1)>4)

B2:
=IF(COUNTIF(A$1:A1,A2)>0,-1,--(COUNTIF(A3:A$1000,A2)>4))

Fill B2 down into B3:B1000. Column B values will now be +1 for the topmost
column A entry that appears 6 or more times, 0 for the topmost column A entry
that appears fewer than 6 times, and -1 for subsequent entries no matter how
often they appear.

Use column C for counting the number of consecutive entries in the second list,
with these formulas. I'll assume the second list is in Sheet2!A1:A1200 (longer
than the first list, but this isn't material - I just wanted to use a different
size).

C1 [array formula]:
=MAX(MMULT(--(ABS(TRANSPOSE(ROW(Sheet2!A$1:A$1200))
-ROW(OFFSET(Sheet2!A$1:A$1200,0,0,ROWS(Sheet2!A$1:A$1200)-5,1))-2.5)<3),
--(Sheet2!A$1:A$1200=A1)))=6

Fill C1 down into C2:C1000.

Now AutoFilter Sheet1!A1:C1000. Select column B value 0 for (1), select column B
value 1 and column C value FALSE for (2), and select column B value 1 and column
C value TRUE for (3).