Unique combinations where order does not matter but duplicates do

[Nicolekk]

I am trying to find unique crop rotations over 5 years. A scaled down version of what I have isA B C DJ North Corn Hay BarleyM North Hay Barley CornN North Barley Corn CornJ South Corn Corn CornM South Barley Corn HayN South Corn Barley CornJ East Pasture Corn BarleyWhere column A is the field identifier, column B is year 1, column C is year 2 and column D is year 3 etcWhat I need is a list of the unique combinations, a count of # of field associated with this particular rotation and a list of the field identifies associated with that particular rotation. (Eg. Corn, Hay, Barley, Count = 3, [J North, M North, M South] )I have tried using the match and countifs functions but keep ending up with an error, what makes it even more difficult is that some cells randomly have no data. Any help would be appreciated!

 

[Nicolekk]

I'm sorry I didn't realize the formatting would be difficult to read!

 

[Nicolekk]

... I hope this is nicer...

I am trying to find unique crop rotations over 5 years. A scaled down version of what I have is
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]J North
[/TD]
[TD]Corn
[/TD]
[TD]Hay
[/TD]
[TD]Barley
[/TD]
[/TR]
[TR]
[TD]M North
[/TD]
[TD]Hay
[/TD]
[TD]Barley
[/TD]
[TD]Corn
[/TD]
[/TR]
[TR]
[TD]N North
[/TD]
[TD]Barley
[/TD]
[TD]Corn
[/TD]
[TD]Corn
[/TD]
[/TR]
[TR]
[TD]J South
[/TD]
[TD]Corn
[/TD]
[TD]Corn
[/TD]
[TD]Corn
[/TD]
[/TR]
[TR]
[TD]M South
[/TD]
[TD]Barley
[/TD]
[TD]Corn
[/TD]
[TD]Hay
[/TD]
[/TR]
[TR]
[TD]N South
[/TD]
[TD]Corn
[/TD]
[TD]Barley
[/TD]
[TD]Corn
[/TD]
[/TR]
[TR]
[TD]J East
[/TD]
[TD]Pasture
[/TD]
[TD]Corn
[/TD]
[TD]Barley
[/TD]
[/TR]
</tbody>[/TABLE]


Where column A is the field identifier, column B is year 1, column C is year 2 and column D is year 3 etc

What I need is a list of the unique combinations, a count of # of field associated with this particular rotation and a list of the field identifies associated with that particular rotation. (Eg. Corn, Hay, Barley, Count = 3, [J North, M North, M South] )

I have tried using the match and countifs functions but keep ending up with an error, what makes it even more difficult is that some cells randomly have no data. Any help would be appreciated!

 

[shg]

You mean like this?

North	Barley	Barley	Barley
North	Barley	Barley	Corn
North	Barley	Barley	Hay
North	Barley	Barley	Pasture
North	Barley	Corn	Barley
North	Barley	Corn	Corn
North	Barley	Corn	Hay
North	Barley	Corn	Pasture
North	Barley	Hay	Barley
North	Barley	Hay	Corn
North	Barley	Hay	Hay
North	Barley	Hay	Pasture
North	Barley	Pasture	Barley
North	Barley	Pasture	Corn
North	Barley	Pasture	Hay
North	Barley	Pasture	Pasture
North	Corn	Barley	Barley
North	Corn	Barley	Corn
North	Corn	Barley	Hay
North	Corn	Barley	Pasture
North	Corn	Corn	Barley
North	Corn	Corn	Corn
North	Corn	Corn	Hay
North	Corn	Corn	Pasture
North	Corn	Hay	Barley
North	Corn	Hay	Corn
North	Corn	Hay	Hay
North	Corn	Hay	Pasture
North	Corn	Pasture	Barley
North	Corn	Pasture	Corn
North	Corn	Pasture	Hay
North	Corn	Pasture	Pasture
North	Hay	Barley	Barley
North	Hay	Barley	Corn
North	Hay	Barley	Hay
North	Hay	Barley	Pasture
North	Hay	Corn	Barley
North	Hay	Corn	Corn
North	Hay	Corn	Hay
North	Hay	Corn	Pasture
North	Hay	Hay	Barley
North	Hay	Hay	Corn
North	Hay	Hay	Hay
North	Hay	Hay	Pasture
North	Hay	Pasture	Barley
North	Hay	Pasture	Corn
North	Hay	Pasture	Hay
North	Hay	Pasture	Pasture
North	Pasture	Barley	Barley
North	Pasture	Barley	Corn
North	Pasture	Barley	Hay
North	Pasture	Barley	Pasture
North	Pasture	Corn	Barley
North	Pasture	Corn	Corn
North	Pasture	Corn	Hay
North	Pasture	Corn	Pasture
North	Pasture	Hay	Barley
North	Pasture	Hay	Corn
North	Pasture	Hay	Hay
North	Pasture	Hay	Pasture
North	Pasture	Pasture	Barley
North	Pasture	Pasture	Corn
North	Pasture	Pasture	Hay
North	Pasture	Pasture	Pasture

(Repeats for East & South fields)

 

[Nicolekk]

Not quite; I don't need all combinations, just a way to consolidate the combinations I already have.
The A column is a Unique Field idenitfier so "J North" is only "Corn, Hay, Barley"

I don't need "J North" as "Barley, Barley, Barley", "Barley, Barley, Corn" or "Barley, Barley, Hay"

But if a future field IS "Barley Barley Barley" I would have to be able to separate that unique combination out

In your example you have "Barley, Hay, Corn" separate from "Corn, Hay, Barley" but for my purposes they are the exact same... Just in a different order.

You mean like this?

North	Barley	Barley	Barley
North	Barley	Barley	Corn
North	Barley	Barley	Hay
North	Barley	Barley	Pasture
North	Barley	Corn	Barley
North	Barley	Corn	Corn
North	Barley	Corn	Hay
North	Barley	Corn	Pasture
North	Barley	Hay	Barley
North	Barley	Hay	Corn
North	Barley	Hay	Hay
North	Barley	Hay	Pasture
North	Barley	Pasture	Barley
North	Barley	Pasture	Corn
North	Barley	Pasture	Hay
North	Barley	Pasture	Pasture
North	Corn	Barley	Barley
North	Corn	Barley	Corn
North	Corn	Barley	Hay
North	Corn	Barley	Pasture
North	Corn	Corn	Barley
North	Corn	Corn	Corn
North	Corn	Corn	Hay
North	Corn	Corn	Pasture
North	Corn	Hay	Barley
North	Corn	Hay	Corn
North	Corn	Hay	Hay
North	Corn	Hay	Pasture
North	Corn	Pasture	Barley
North	Corn	Pasture	Corn
North	Corn	Pasture	Hay
North	Corn	Pasture	Pasture
North	Hay	Barley	Barley
North	Hay	Barley	Corn
North	Hay	Barley	Hay
North	Hay	Barley	Pasture
North	Hay	Corn	Barley
North	Hay	Corn	Corn
North	Hay	Corn	Hay
North	Hay	Corn	Pasture
North	Hay	Hay	Barley
North	Hay	Hay	Corn
North	Hay	Hay	Hay
North	Hay	Hay	Pasture
North	Hay	Pasture	Barley
North	Hay	Pasture	Corn
North	Hay	Pasture	Hay
North	Hay	Pasture	Pasture
North	Pasture	Barley	Barley
North	Pasture	Barley	Corn
North	Pasture	Barley	Hay
North	Pasture	Barley	Pasture
North	Pasture	Corn	Barley
North	Pasture	Corn	Corn
North	Pasture	Corn	Hay
North	Pasture	Corn	Pasture
North	Pasture	Hay	Barley
North	Pasture	Hay	Corn
North	Pasture	Hay	Hay
North	Pasture	Hay	Pasture
North	Pasture	Pasture	Barley
North	Pasture	Pasture	Corn
North	Pasture	Pasture	Hay
North	Pasture	Pasture	Pasture

<tbody>
</tbody>

(Repeats for East & South fields)

 

[Marcelo Branco]

Where column A is the field identifier, column B is year 1, column C is year 2 and column D is year 3 etc

What I need is a list of the unique combinations, a count of # of field associated with this particular rotation and a list of the field identifies associated with that particular rotation. (Eg. Corn, Hay, Barley, Count = 3, [J North, M North, M South] )

Hope I've understood what you need.

To avoid very complex array formulas i suggest a helper column (gray area)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

ID​

[/TD]
[TD]

Year1​

[/TD]
[TD]

Year2​

[/TD]
[TD]

Year3​

[/TD]
[TD][/TD]
[TD]

Criteria​

[/TD]
[TD]

Count​

[/TD]
[TD]

List​

[/TD]
[TD][/TD]
[TD]

Helper​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

J North​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD]

Corn​

[/TD]
[TD]

3​

[/TD]
[TD]

J North​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

M North​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]

Hay​

[/TD]
[TD][/TD]
[TD]

M North​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

N North​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD]

M South​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

J South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

M South​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

N South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

J East​

[/TD]
[TD]

Pasture​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2 copied down
=SUMPRODUCT(--(COUNTIF(B2:D2,F$2:F$4)>0))

Criteria in F2:F4

Formula in G2
=COUNTIF(J2:J8,3)

Array formula in H2 copied down
=IF(G$2>=ROWS(H$2:H2),INDEX(A:A,SMALL(IF(J$2:J$8=3,ROW(J$2:J$8)),ROWS(H$2:H2))),"")
Ctrl+Shift+Enter

M.

 

[Nicolekk]

Once again I was not entirely clear, thank you for showing me where I can clarify myself.
I have manually typed in what I am looking for in Column F, G, and H. However I have over 200 fields so I'm hoping to avoid doing all of them one by one.
[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

ID​

[/TD]
[TD]

Year1​

[/TD]
[TD]

Year2​

[/TD]
[TD]

Year3​

[/TD]
[TD][/TD]
[TD]

Criteria​

[/TD]
[TD]

Count​

[/TD]
[TD]

List​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

J North​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD]

Corn, Hay, Barley​

[/TD]
[TD]3
[/TD]
[TD]

J North, M North, M South​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

M North​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]Corn, Corn, Barley
[/TD]
[TD]2
[/TD]
[TD]N North, N South
[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

N North​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]

Corn, Corn, Corn​

[/TD]
[TD]1
[/TD]
[TD]J South
[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

J South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

M South​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

N South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

J East​

[/TD]
[TD]

Pasture​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2 copied down

Hope I've understood what you need.

To avoid very complex array formulas i suggest a helper column (gray area)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

ID​

[/TD]
[TD]

Year1​

[/TD]
[TD]

Year2​

[/TD]
[TD]

Year3​

[/TD]
[TD][/TD]
[TD]

Criteria​

[/TD]
[TD]

Count​

[/TD]
[TD]

List​

[/TD]
[TD][/TD]
[TD]

Helper​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

J North​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD]

Corn​

[/TD]
[TD]

3​

[/TD]
[TD]

J North​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

M North​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]

Hay​

[/TD]
[TD][/TD]
[TD]

M North​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

N North​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD]

M South​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

J South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

M South​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

N South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

J East​

[/TD]
[TD]

Pasture​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

2​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2 copied down
=SUMPRODUCT(--(COUNTIF(B2:D2,F$2:F$4)>0))

Criteria in F2:F4

Formula in G2
=COUNTIF(J2:J8,3)

Array formula in H2 copied down
=IF(G$2>=ROWS(H$2:H2),INDEX(A:A,SMALL(IF(J$2:J$8=3,ROW(J$2:J$8)),ROWS(H$2:H2))),"")
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

Try this
Without helper columns...very complex formulas! Ugly...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I
​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

ID​

[/TD]
[TD]

Year1​

[/TD]
[TD]

Year2​

[/TD]
[TD]

Year3​

[/TD]
[TD][/TD]
[TD]

Crit1​

[/TD]
[TD]

Crit2​

[/TD]
[TD]

Crit3​

[/TD]
[TD]

Count
​

[/TD]
[TD]

ID1​

[/TD]
[TD]

ID2​

[/TD]
[TD]

ID3​

[/TD]
[TD]

ID4​

[/TD]
[TD]

ID5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

J North​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD]

3​

[/TD]
[TD]

J North​

[/TD]
[TD]

M North​

[/TD]
[TD]

M South​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

M North​

[/TD]
[TD]

Hay​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD]

2​

[/TD]
[TD]

N North​

[/TD]
[TD]

N South​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

N North​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD]

1​

[/TD]
[TD]

J South​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

J South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

M South​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Hay​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

N South​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD]

Corn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

J East​

[/TD]
[TD]

Pasture​

[/TD]
[TD]

Corn​

[/TD]
[TD]

Barley​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in I2 copied down
=SUM(IF(MMULT(--(COUNTIF(OFFSET(B$2,ROW($B$2:B$8)-ROW(B$2),0,1,3),F2:H2)=COUNTIF(F2:H2,F2:H2)),TRANSPOSE(COLUMN($B$2:$D$8)^0))=3,1))
Ctrl+Shift+Enter

Array Formula In J2 copied across and down
=IF(COLUMNS($J2:J2)>$I2,"",INDEX($A:$A,SMALL(IF(MMULT(--(COUNTIF(OFFSET($B$2,ROW($B$2:B$8)-ROW($B$2),0,1,3),$F2:$H2)=COUNTIF($F2:$H2,$F2:$H2)),TRANSPOSE(COLUMN($B$2:$D$8)^0))=3,ROW($A$2:$A$8)),COLUMNS($J2:J2))))
Ctrl+Shift+Enter

M.