Help: I need to return a list of all cells (will be multiple) containing a specific text string

[usmcellis]

First time poster here and I need help completing my softball pitching signals worksheet. The table below is recreated with weighted averages every time the sheet is recalculated (F9). You read the chart horizontal then down (ie; 132 = go over to 13, then down to 2 = DB).

I'd like to have a coaches' cheat sheet on a separate tab that will list all of the 3 digit numbers by pitch. For example:

FB
121, 131, 141, 221....

CH
111, 112, 123....

DB
132, 142, 223....

I'd like to have it recalculate every time I hit F9 to repopulate the signal card, but can't figure out how. If someone can help me create a list of every cell reference that includes "CH", "FB", "DB", etc, I can probably figure out how to combine them into the 3 digit numbers.

*I've also though of naming each cell to correspond to the signal: _111, _212, etc., if that will make it easier.

 

[Herakles]

First time poster here and I need help completing my softball pitching signals worksheet. The table below is recreated with weighted averages every time the sheet is recalculated (F9). You read the chart horizontal then down (ie; 132 = go over to 13, then down to 2 = DB).

View attachment 99539

I'd like to have a coaches' cheat sheet on a separate tab that will list all of the 3 digit numbers by pitch. For example:

FB
121, 131, 141, 221....

CH
111, 112, 123....

DB
132, 142, 223....

I'd like to have it recalculate every time I hit F9 to repopulate the signal card, but can't figure out how. If someone can help me create a list of every cell reference that includes "CH", "FB", "DB", etc, I can probably figure out how to combine them into the 3 digit numbers.

*I've also though of naming each cell to correspond to the signal: _111, _212, etc., if that will make it easier.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[duggie33]

I got this far...I rearranged your data to make it easier to INDEX and MATCH. This does not include your weighted averaging. Weighted averaging is possible, but I cannot think of how to incorporate it at the moment. I am out of time to look it up with google...

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1																			COL	ROW	COMB	PITCH	CELL		FB	CELL	CH	CELL	DB	CELL	RB	CELL		FB	51.6%
2																			11	1	111	CH	$C$4		113	$C$6	111	$C$4	132	$E$5	134	$E$7		CH	23.4%
3		11	12	13	14	21	22	23	24	31	32	33	34	41	42	43	44		11	2	112	CH	$C$5		121	$D$4	112	$C$5	142	$F$5	234	$I$7		DB	9.4%
4	1	CH	FB	FB	FB	CH	FB	CH	CH	DB	FB	FB	FB	FB	FB	FB	FB		11	3	113	FB	$C$6		122	$D$5	114	$C$7	223	$H$6	312	$K$5		RB	15.6%
5	2	CH	FB	DB	DB	FB	FB	FB	FB	RB	DB	RB	CH	DB	RB	FB	RB		11	4	114	CH	$C$7		131	$E$4	123	$D$6	311	$K$4	314	$K$7
6	3	FB	CH	FB	FB	FB	DB	FB	CH	FB	CH	CH	FB	RB	FB	FB	FB		12	1	121	FB	$D$4		133	$E$6	124	$D$7	322	$L$5	332	$M$5
7	4	CH	CH	RB	CH	FB	FB	RB	FB	RB	FB	RB	FB	CH	RB	FB	CH		12	2	122	FB	$D$5		141	$F$4	144	$F$7	412	$O$5	334	$M$7
8																			12	3	123	CH	$D$6		143	$F$6	211	$G$4			413	$O$6
9																			12	4	124	CH	$D$7		212	$G$5	231	$I$4			422	$P$5
10																			13	1	131	FB	$E$4		213	$G$6	241	$J$4			424	$P$7
11																			13	2	132	DB	$E$5		214	$G$7	243	$J$6			442	$R$5
12																			13	3	133	FB	$E$6		221	$H$4	323	$L$6
13																			13	4	134	RB	$E$7		222	$H$5	333	$M$6
14																			14	1	141	FB	$F$4		224	$H$7	342	$N$5
15																			14	2	142	DB	$F$5		232	$I$5	414	$O$7
16																			14	3	143	FB	$F$6		233	$I$6	444	$R$7
17																			14	4	144	CH	$F$7		242	$J$5
18																			21	1	211	CH	$G$4		244	$J$7
19																			21	2	212	FB	$G$5		313	$K$6
20																			21	3	213	FB	$G$6		321	$L$4
21																			21	4	214	FB	$G$7		324	$L$7
22																			22	1	221	FB	$H$4		331	$M$4
23																			22	2	222	FB	$H$5		341	$N$4
24																			22	3	223	DB	$H$6		343	$N$6
25																			22	4	224	FB	$H$7		344	$N$7
26																			23	1	231	CH	$I$4		411	$O$4
27																			23	2	232	FB	$I$5		421	$P$4
28																			23	3	233	FB	$I$6		423	$P$6
29																			23	4	234	RB	$I$7		431	$Q$4
30																			24	1	241	CH	$J$4		432	$Q$5
31																			24	2	242	FB	$J$5		433	$Q$6
32																			24	3	243	CH	$J$6		434	$Q$7
33																			24	4	244	FB	$J$7		441	$R$4
34																			31	1	311	DB	$K$4		443	$R$6
35																			31	2	312	RB	$K$5
36																			31	3	313	FB	$K$6
37																			31	4	314	RB	$K$7
38																			32	1	321	FB	$L$4
39																			32	2	322	DB	$L$5
40																			32	3	323	CH	$L$6
41																			32	4	324	FB	$L$7
42																			33	1	331	FB	$M$4
43																			33	2	332	RB	$M$5
44																			33	3	333	CH	$M$6
45																			33	4	334	RB	$M$7
46																			34	1	341	FB	$N$4
47																			34	2	342	CH	$N$5
48																			34	3	343	FB	$N$6
49																			34	4	344	FB	$N$7
50																			41	1	411	FB	$O$4
51																			41	2	412	DB	$O$5
52																			41	3	413	RB	$O$6
53																			41	4	414	CH	$O$7
54																			42	1	421	FB	$P$4
55																			42	2	422	RB	$P$5
56																			42	3	423	FB	$P$6
57																			42	4	424	RB	$P$7
58																			43	1	431	FB	$Q$4
59																			43	2	432	FB	$Q$5
60																			43	3	433	FB	$Q$6
61																			43	4	434	FB	$Q$7
62																			44	1	441	FB	$R$4
63																			44	2	442	RB	$R$5
64																			44	3	443	FB	$R$6
65																			44	4	444	CH	$R$7
Sheet1

Cell Formulas
Range	Formula
V2:V65	V2	=T2&U2
W2	W2	=INDEX($C$4:$R$7,$U2,MATCH($T2,$C$3:$R$3,0))
X2:X65	X2	=ADDRESS($U2+3,MATCH($T2,$C$3:$R$3,0)+2)
W3:W65	W3	=INDEX($C$4:$R$7,U3,MATCH($T3,$C$3:$R$3,0))
Z2:AA34,AF2:AG11,AD2:AE7,AB2:AC16	Z2	=FILTER(CHOOSE({1,2},$V$2:$V$65,$X$2:$X$65),$W$2:$W$65=Z$1)
AJ1	AJ1	=COUNTA(INDEX(Z2#,,1))/64
AJ2	AJ2	=COUNTA(INDEX(AB2#,,1))/64
AJ3	AJ3	=COUNTA(INDEX(AD2#,,1))/64
AJ4	AJ4	=COUNTA(INDEX(AF2#,,1))/64
Dynamic array formulas.

Hope that helps,

Doug

 

[usmcellis]

Wristband Creator.xlsx
	A	B	C	D	E
1	2024 Cyclones Pitch Card #				9676
2	#	Pitch	Desired %	Cumulative	Actual %
3	1	FB	50%	0.00	41%
4	2	CH	30%	0.50	31%
5	3	CV	0%	0.80	0%
6	4	DB	10%	0.80	9%
7	5	SB	0%	0.90	0%
8	6	RB	10%	0.90	19%
Entry

Cell Formulas
Range	Formula
E1	E1	=RAND()*10000
D3:D8	D3	=SUM(D2,C2)
E3:E8	E3	=COUNTIF('Wrist Bands'!$B$3:$J$11,B3)/64

Named Ranges
Name	Refers To	Cells
_111	='Wrist Bands'!$B$3	E3:E8
Cumulative	=Entry!$D$3:$D$10	D4
Pitches	=Entry!$B$3:$B$8	E3

 

[usmcellis]

Wristband Creator.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	9676	Carolina Cyclones Pitching
2		11	12	13	14		21	22	23	24
3	1	RB	DB	CH	FB	1	CH	FB	FB	RB
4	2	FB	FB	FB	CH	2	RB	FB	CH	FB
5	3	RB	RB	DB	RB	3	CH	CH	CH	FB
6	4	FB	DB	CH	DB	4	FB	FB	RB	CH
7		31	32	33	34		41	42	43	44
8	1	FB	DB	CH	CH	1	CH	FB	FB	FB
9	2	FB	FB	FB	CH	2	RB	CH	CH	FB
10	3	CH	CH	RB	RB	3	FB	FB	RB	CH
11	4	FB	FB	CH	DB	4	FB	FB	CH	RB
12
Wrist Bands

Cell Formulas
Range	Formula
A1	A1	=Entry!$E$1
B3:E6,G8:J11,B8:E11,G3:J6	B3	=VLOOKUP(MATCH(RAND(),Cumulative),Entry!$A$2:$B$8,2)

 

[usmcellis]

@Herakles and @duggie33,

I've the first post above is the "input" worksheet where I put the weights for my pitches, or the total percentage each pitch should fill the card.

The secon post is the actual card with the pitches.

I'm wanting a third sheet that will give me every cell that each pitch is located. In this particular card, FB would be listed as 141, 112, etc. I just need to know how to produce the list of cell addresses and can create a VLOOKUP table to convert to the 141, 112, etc.

 

[duggie33]

My example shows how to get the cell addresses and return a list for each pitch. You will have to adjust for the layout of your data.

Doug

 

[usmcellis]

My example shows how to get the cell addresses and return a list for each pitch. You will have to adjust for the layout of your data.

Doug

Got it to work. Thank you, very much!

 

[duggie33]

Glad to help.

Doug