Poker hand analyzer out of a 7 card hand

[chrisbroe411]

3 questions
Okay so I'm trying to make a sheet that represents a 100 full table of players and their hands and then summarizes the results for analysis I've got an almost complete. I used the forums to find formulas that work. This in the spreadsheet that I attached. the way I got the cards to layout using RANDBETWEEN. Eight players are dealt cards in turn then the burn and the flop and burn and turn and the burn and the river. I did that so that not only does a player get a random card but if it were actually being dealt one of the other cards remaining would've been wasted on the Burns.
I have a formula to determine if there's a flush. I found a formula to determine the various forms of matching cards in a five card hand and thought I had adapted it for the seven card hand properly until I started analyzing the data. I used
=LOOKUP(SUMPRODUCT(COUNTIF(H1:H7,"="&LEFT(H1:H7,1)&"*")),{7,9,11,13,15,19},{"no","one pair","two pair","3 of kind","full house","4 of kind"})
which actually works great except when there's three pairs. Now three pairs is not a valid poker hand. But the SUMPRODUCT returns a value of 13 for both the condition of three pairs or one three of a kind. I also have a problem when there is two 3 of the kinds, it should be a full house but it registers as four a kind.

question 1. Is there a way to fix it?

I also have a formula for finding straight that's also in the sheet that is also in the sheet. What I did was break out the left-hand value of the cards and then check them against a helper section.

Question 2. Is there a way to use this formula with the original seven cards or going to need to go through the step of breaking out the left-hand number?

Question 3. Is there a way to pull out just the five cards used for each one of these hands?

Side note I want to do this all with formulas.

formula error.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	JS	JS	JS	JS	JS	JS	JS	JS	JH	JS									1
2	JH	JH	JH	JH	JH	JH	JH	ks	ks	ks								cards	hands
3	TD	jc	5h	5h	TD	5h	5h	5h	5h	9S								P1A	5
4	jd	TH	5c	TH	TH	TH	TH	TH	TH	TH								P2A	8
5	5d	5d	jc	jc	5d	TC	ks	qc	qc	qc								P3A	9
6	jc	3c	5d	2c	3c	2c	2c	2c	2H	2c								P4A	42
7	3S	3S	3S	3S	3S	3S	3S	3S	3H	3S								P5A	50
8																		P6A	36
9	x	x	x	x	x	x	x	x	flush	x								P7A	19
10																		P8A	29
11	4 of kind	full house	4 of kind	3 of kind	3 of kind	two pair	one pair	no	no	no								P1B	25
12	19	15	19	13	13	11	9	7	7	7	the resulting numbers from the sumproduct used above							P2B	16
13																		P3B	46
14	J	J	J	J	J	J	J	J	J	J								P4B	48
15	J	J	J	J	J	J	J	k	k	k								P5B	39
16	T	j	5	5	T	5	5	5	5	9								P6B	2
17	j	T	5	T	T	T	T	T	T	T								P7B	27
18	5	5	j	j	5	T	k	q	q	q								P8B	45
19	j	3	5	2	3	2	2	2	2	2								B1	18
20	3	3	3	3	3	3	3	3	3	3								F1	24
21																		F2	37
22	1	1	1	1	1	1	1	1	1	1								F3	33
23	1	1	1	1	1	1	1	1	1	1								B2	23
24	1	1	1	1	1	1	1	1	1	1								T	13
25	1	1	1	1	1	1	1	1	1	1								B3	49
26	1	1	1	1	1	1	1	1	1	1								R	11
27	1	1	1	1	1	1	1	1	1	1								Duplicate check	FALSE
28	1	1	1	1	1	1	1	1	1	1
29	1	1	1	1	1	1	1	1	1	1
30	1	1	1	1	1	1	1	1	1	9TJQK
31	1	1	1	1	1	1	1	1	1	1
32
33	A	2	3	4	5	A2345
34	2	3	4	5	6	23456
35	3	4	5	6	7	34567
36	4	5	6	7	8	45678
37	5	6	7	8	9	56789
38	6	7	8	9	T	6789T
39	7	8	9	T	J	789TJ
40	8	9	T	J	Q	89TJQ
41	9	T	J	Q	K	9TJQK
42	T	J	Q	K	A	TJQKA
43
Sheet1

Cell Formulas
Range	Formula
A9:J9	A9	=IF(OR(COUNTIF(A1:A7,"=*"&RIGHT(A1,1))>4,IF(RIGHT(A2,1)=RIGHT(A1,1),,(COUNTIF(A2:A7,"=*"&RIGHT(A2,1))>4)),IF(RIGHT(A3,1)=RIGHT(A2,1),,(COUNTIF(A3:A7,"=*"&RIGHT(A3,1))>4))),"flush","x")
A11:J11	A11	=LOOKUP(SUMPRODUCT(COUNTIF(A1:A7,"="&LEFT(A1:A7,1)&"*")),{7,9,11,13,15,19},{"no","one pair","two pair","3 of kind","full house","4 of kind"})
A12:J12	A12	=SUMPRODUCT(COUNTIF(A1:A7,"="&LEFT(A1:A7,1)&"*"))
A14:J20	A14	=LEFT(A1,1)
A22:J31	A22	=IF(AND(COUNTIF(A$14:A$20,Sheet1!$A33)>0,COUNTIF(A$14:A$20,Sheet1!$B33)>0,COUNTIF(A$14:A$20,Sheet1!$C33)>0,COUNTIF(A$14:A$20,Sheet1!$D33)>0,COUNTIF(A$14:A$20,Sheet1!$E33)>0),Sheet1!$F33,1)
S3:S26	S3	=IFERROR(LARGE(ROW($1:$52)*NOT(COUNTIF(S$2:S2,ROW($1:$52))),RANDBETWEEN(1,52-1-ROW(S2)+2)),"")
S27	S27	=SUMPRODUCT(COUNTIF(S3:S26,S3:S26)-1)>0
F33:F42	F33	=A33&B33&C33&D33&E33
Press CTRL+SHIFT+ENTER to enter array formulas.