Find repeating patterns in a range of data

[remigh]

Finding the same team of 4 out of 10 horses, to keep track of different teams during chuckwagon race season. I have the names of 10 horses in a column, then the next 30 columns are the race dates, if a horse is on the team of 4 that races that day, a racing time is entered into it's corresponding row. Anyway to find repeating sets of the same team of 4 horses and return its team name or recognize a new team and assign a new team name?

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[remigh]

Here is an example spreadsheet

[AliGW]

Would you have a problem with a helper row?

[remigh]

Not at all, whatever works, thanks!

[AliGW]

How many rows could there be (maximum), and how many teams?

I'm not quite clear how you expect the sheet to be populated - which cells in row 2 are you expecting to be populated manually and which automatically? Or does Excel have to do it all, creating names for teams as it goes?

[shg]

	A	B	C	D	E	F	G	H	I	J
1		Race 1	Race 2	Race 3	Race 4	Race 5	Race 6	Race 7
2	Team Number	15	113	153	774	209	153	209		B2: =SUMPRODUCT(ISNUMBER(B5:B14) * 2^(ROW($A$5:$A$14) - ROW($A$5)))
3	Team Name	Team A	Team B	Team C	Team D	Team E	Team C	Team E		B3: =IF(COUNTIF($A2:A2, B2) = 0, "Team " & CHAR(63 + SUMPRODUCT(1/COUNTIF(rng, rng))), INDEX($A3:A3, MATCH(B2, rng, 0)))
4
5	Sky	01:18.72	01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
6	Tommy	01:18.72			01:19.26
7	Reese	01:18.72			01:19.26
8	Ben	01:18.72		01:18.14			01:18.14
9	Kyle		01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
10	JB		01:18.54
11	Mickey		01:18.54			01:18.54		01:16.64
12	Frankie			01:18.14		01:18.54	01:18.14	01:16.64
13	Bob				01:19.26
14	Don				01:19.26

[remigh]

How many rows could there be (maximum), and how many teams?

I'm not quite clear how you expect the sheet to be populated - which cells in row 2 are you expecting to be populated manually and which automatically? Or does Excel have to do it all, creating names for teams as it goes?

Maximum 50 races, so if each race had a different team (not likely) then max 50 different teams.
Maximum 20 rows (different horses)
I would like excel to do it automatically, search each race column and if it recognizes the same team put the existing team name in the cell, if it is a new combination team, then assign a new team name and put it in the cell.

[AliGW]

You have a solution offered in post #7 - I think it does what you want.

[remigh]

Even assigning each team a unique number works, doesn't have to be "Team A", that was just a quick choice from me. So on your solution if each team gets a number,like in your row 2, i can work with that.

[shg]

Oops -- left a named range in there:

	A	B	C	D	E	F	G	H	I	J
1		Race 1	Race 2	Race 3	Race 4	Race 5	Race 6	Race 7
2	Team Number	15	113	153	774	209	153	209		B2: =SUMPRODUCT(ISNUMBER(B5:B14) * 2^(ROW($A$5:$A$14) - ROW($A$5)))
3	Team Name	Team A	Team B	Team C	Team D	Team E	Team C	Team E		B3: =IF(COUNTIF($A2:A2, B2) = 0, "Team " & CHAR(63 + SUMPRODUCT(1/COUNTIF($A$2:B$2, $A$2:B$2))), INDEX($A3:A3, MATCH(B2, $A$2:B$2, 0)))
4
5	Sky	01:18.72	01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
6	Tommy	01:18.72			01:19.26
7	Reese	01:18.72			01:19.26
8	Ben	01:18.72		01:18.14			01:18.14
9	Kyle		01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
10	JB		01:18.54
11	Mickey		01:18.54			01:18.54		01:16.64
12	Frankie			01:18.14		01:18.54	01:18.14	01:16.64
13	Bob				01:19.26
14	Don				01:19.26

[AliGW]

In that case, maybe all you need is this, then? Adapted from SHG's suggestion:

="Team "&SUMPRODUCT(ISNUMBER(B5:B14) * 2^(ROW($A$5:$A$14) - ROW($A$5)))

[remigh]

I will try it, looks very promising thanks so much for the help!!

[shg]

If you do that, you'll end up with some awkward team names, like "Team 1966080"

[remigh]

hmmm anyway to get more manageable numbers?

[remigh]

Tried your formula works great thanks

[shg]

If you just want the teams numbered sequentially,

	A	B	C	D	E	F	G	H	I	J
1		Race 1	Race 2	Race 3	Race 4	Race 5	Race 6	Race 7
2	Team Makeup	15	113	153	774	209	153	209		B2: =SUMPRODUCT(ISNUMBER(B5:B14) * 2^(ROW($A$5:$A$14) - ROW($A$5)))
3	Team Name	Team 1	Team 2	Team 3	Team 4	Team 5	Team 3	Team 5		B3: =IF(COUNTIF($A2:A2, B2) = 0, MAX($A3:A3) + 1, INDEX($A3:A3, MATCH(B2, $A$2:B$2, 0)))
4										Format of B3: "Team " 0
5	Sky	01:18.72	01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
6	Tommy	01:18.72			01:19.26
7	Reese	01:18.72			01:19.26
8	Ben	01:18.72		01:18.14			01:18.14
9	Kyle		01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
10	JB		01:18.54
11	Mickey		01:18.54			01:18.54		01:16.64
12	Frankie			01:18.14		01:18.54	01:18.14	01:16.64
13	Bob				01:19.26
14	Don				01:19.26

Note that the values calculated in row 3 are numbers; the "Team" part comes from the number format. You'd probably want to hide row 2 to avoid confusion.

[shg]

You're welcome.

If you have 50 events, you don't want the team names going past Z, so maybe just assign sequential numbers:

	A	B	C	D	E	F	G	H	I	J
1		Race 1	Race 2	Race 3	Race 4	Race 5	Race 6	Race 7
2	Team Makeup	15	113	153	774	209	153	209		B2: =SUMPRODUCT(ISNUMBER(B5:B14) * 2^(ROW($A$5:$A$14) - ROW($A$5)))
3	Team Name	Team 1	Team 2	Team 3	Team 4	Team 5	Team 3	Team 5		B3: =IF(COUNTIF($A2:A2, B2) = 0, MAX($A3:A3) + 1, INDEX($A3:A3, MATCH(B2, $A$2:B$2, 0)))
4										Format of B3: "Team " 0
5	Sky	01:18.72	01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
6	Tommy	01:18.72			01:19.26
7	Reese	01:18.72			01:19.26
8	Ben	01:18.72		01:18.14			01:18.14
9	Kyle		01:18.54	01:18.14		01:18.54	01:18.14	01:16.64
10	JB		01:18.54
11	Mickey		01:18.54			01:18.54		01:16.64
12	Frankie			01:18.14		01:18.54	01:18.14	01:16.64
13	Bob				01:19.26
14	Don				01:19.26