Data displaying in column instead of row.

[xFrosty]

Hi there,

I have tried using Chat GPT to assist me with this particular thing I'm trying to achieve but I feel that I am entering the same formula over and over again just different ways of entering it in.

So here I am, frustrated because I've spent way too much time trying to get Excel to do what I want it to do.

So basically, I am trying to create a calendar thats a set and forget type thing. I have all the data matching to the dates etc, but what I want now is these particular figures to go into another cell(s) so I can track how much from each pay I need to set aside each week. I want the figures to go down the column, so far I've only managed to get them to appear across.

Heres where you come in.... Please help me before I give up! hahahaha.

These are the things I've tried

=IFERROR(INDEX($E$6:$R$10,SMALL(IF(ISNUMBER(SEARCH("S -",$E$6:$R$10)),ROW($E$6:$R$10)-ROW($E$6:$R$10)+1),ROW(INDIRECT("1:"&COUNTIF($E$6:$R$10,"S -*"))))),COLUMN(A1))

This one looked to be wanting to display the text I want but I only have the number 1 showing down the array.

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		November
2	##########
3	##########				Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday			I want it to display this
4	##########																			S - Phone
5					28		29		30		1		2		3		4			S - Debt
6		` BILL & DEBT PAYMENTS CALENDAR `			S - Phone						S - Debt		S - Power							S - Power
7													S - School							S - School
8		TODAY'S DATE
9		Tuesday, 7 Nov 2023			Don’t want it to display this
10					Found	Not Found	Not Found	Not Found	Not Found	Not Found	Found	Not Found	Found	Not Found	Not Found	Not Found	Not Found	Not Found
11					Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Found	Not Found	Not Found	Not Found	Not Found	Not Found
12		MONTH	YEAR		Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found	Not Found
13		November	2023
14
15		START DAY
16		Monday			1
17					1
18					1
19					1
20
Sheet1

Cell Formulas
Range	Formula
B1	B1	=B13
A2	A2	=DATE(C13,A1,1)
A3	A3	=EOMONTH(A2,0)
A4,B9	A4	=TODAY()
E5	E5	=IF(B16="Sunday", A2 - WEEKDAY(A2, 1) + 1, IF(B16="Monday", A2 - WEEKDAY(A2, 2) + 1, ""))
G5,Q5,O5,M5,K5,I5	G5	=E5 + 1
E10:R12	E10	=IF(ISNUMBER(SEARCH("S -", E6:R8)), "Found", "Not Found")
E16:E19	E16	=IFERROR(INDEX($E$6:$R$8,SMALL(IF(ISNUMBER(SEARCH("S -",$E$6:$R$8)),ROW($E$6:$R$10)-ROW($E$6:$R$10)+1),ROW(INDIRECT("1:"&COUNTIF($E$6:$R$10,"S -*"))))),COLUMN(A1))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C13	Cell Value	beginning with "✓"	text	NO
G5	Expression	=G5=TODAY()	text	NO
G5	Expression	=MonthToDisplayNumber<>MONTH(G5)	text	NO
E6:E7	Cell Value	contains "Payday"	text	NO
E6:E7	Expression	=E6=TODAY()	text	NO
E6:E7	Expression	=MonthToDisplayNumber<>MONTH(E6)	text	NO
E8	Cell Value	contains "Payday"	text	NO
E8	Expression	=E8=TODAY()	text	NO
E8	Expression	=MonthToDisplayNumber<>MONTH(E8)	text	NO
F5,H5,J5,L5,N5,P5,R5	Cell Value	contains "Payday"	text	NO
E5	Expression	=E5=TODAY()	text	NO
E5	Expression	=MonthToDisplayNumber<>MONTH(E5)	text	NO
G6:G8,I6:I8,K6:K8,M6:M8,O6:O8,Q6:Q8	Cell Value	contains "Payday"	text	NO
F5,K5:R5,H5:I5,G6:G8,I6:I8,K6:K8,M6:M8,O6:O8,Q6:Q8	Expression	=F5=TODAY()	text	NO
O8	Expression	=MonthToDisplayNumber<>MONTH(O8)	text	NO
F5,K5:R5,O6:O7,K6:K8,H5:I5,I6:I8,G6:G8,M6:M8,Q6:Q8	Expression	=MonthToDisplayNumber<>MONTH(F5)	text	NO

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you want?

23 11 07.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday			I want it to display this
4																S - Phone	S - Phone
5	28		29		30		1		2		3		4			S - Debt	S - Debt
6	S - Phone						S - Debt		S - Power							S - Power	S - Power
7									S - School							S - School	S - School
Sheet2 (3)

Cell Formulas
Range	Formula
U4:U7	U4	=TOCOL(E6:R7,1)
Dynamic array formulas.

 

[xFrosty]

Unfortunantly that gives me a Name? error.

Thank you though.

 

[Peter_SSs]

Unfortunantly that gives me a Name? error.

Sorry, I thought that I had see you were using Microsoft 365.

Try this instead.

23 11 07.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday
4																S - Phone
5	28		29		30		1		2		3		4			S - Debt
6	S - Phone						S - Debt		S - Power							S - Power
7									S - School							S - School
List

Cell Formulas
Range	Formula
T4:T7	T4	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,E6:Q7)&"</c></p>","//c")
Dynamic array formulas.

 

[xFrosty]

=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,E6:Q7)&"</c></p>","//c")

You are amazing! Thank you so much! I have been bashing my head against the desk with the answers chat gpt has been giving me for weeks.

I didnt realise different formulas worked on different products, you would think it all worked the same.

Whats the </c> and </p> represent?

Ok, so the next question is, how do I extract the dollar amounts out of that list and or have it add the amount I need to set aside? Is there a way the S - can be omitted too?

 

[Peter_SSs]

I have been bashing my head against the desk with the answers chat gpt has been giving me for weeks.

I would give ChatGPT away for excel problems. It gives too many problematic suggestions.

I didnt realise different formulas worked on different products, you would think it all worked the same.

As new versions of Excel are released, new features are sometimes introduced. In general there is some reason that those new features are not compatible with older versions.

Whats the </c> and </p> represent?

Not a simple explanation. You can look here for more information about FILTERXML

, how do I extract the dollar amounts out of that list

What dollar amounts? I didn't see any. Perhaps some more small sample data and expected results?

Is there a way the S - can be omitted too?

23 11 07.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday
4																Phone
5	28		29		30		1		2		3		4			Debt
6	S - Phone						S - Debt		S - Power							Power
7									S - School							School
List

Cell Formulas
Range	Formula
T4:T7	T4	=FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,E6:Q7),"S - ","")&"</c></p>","//c")
Dynamic array formulas.

 

[xFrosty]

Appologies.

Sample.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
3	Sunday		Monday		Tuesday		Wednesday		Thursday		Friday		Saturday			I want it to display this
4
5	30		31		1		2		3		4		5			S - Phone - $34
6	S - Phone - $34						S - Debt - $30		S - Power - $100							S - Debt - $30
7									S - School - $56							S - Power - $100
8																S - School - $56
9
10
11
12
13																Total Needed	$220.00
14
15
16
Sheet1

Cell Formulas
Range	Formula
E5	E5	=IF(B16="Sunday", A2 - WEEKDAY(A2, 1) + 1, IF(B16="Monday", A2 - WEEKDAY(A2, 2) + 1, ""))
G5,Q5,O5,M5,K5,I5	G5	=E5 + 1
T5	T5	=E6
T6	T6	=K6
T7:T8	T7	=M6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5	Expression	=G5=TODAY()	text	NO
G5	Expression	=MonthToDisplayNumber<>MONTH(G5)	text	NO
E6:E7	Cell Value	contains "Payday"	text	NO
E6:E7	Expression	=E6=TODAY()	text	NO
E6:E7	Expression	=MonthToDisplayNumber<>MONTH(E6)	text	NO
E8	Cell Value	contains "Payday"	text	NO
E8	Expression	=E8=TODAY()	text	NO
E8	Expression	=MonthToDisplayNumber<>MONTH(E8)	text	NO
F5,H5,J5,L5,N5,P5,R5	Cell Value	contains "Payday"	text	NO
E5	Expression	=E5=TODAY()	text	NO
E5	Expression	=MonthToDisplayNumber<>MONTH(E5)	text	NO
G6:G8,I6:I8,K6:K8,M6:M8,O6:O8,Q6:Q8	Cell Value	contains "Payday"	text	NO
F5,K5:R5,H5:I5,G6:G8,I6:I8,K6:K8,M6:M8,O6:O8,Q6:Q8	Expression	=F5=TODAY()	text	NO
O8	Expression	=MonthToDisplayNumber<>MONTH(O8)	text	NO
F5,K5:R5,O6:O7,K6:K8,H5:I5,I6:I8,G6:G8,M6:M8,Q6:Q8	Expression	=MonthToDisplayNumber<>MONTH(F5)	text	NO

 

[xFrosty]

Actually, Im sorry is there a way that it can be displayed like this instead? I think this would be an easier option.

Obviosuly using the same formula as above.

Sample.xlsx
	T	U
3
4
5	Phone	$34.00
6	Debt	$30.00
7	Power	$100.00
8	School	$56.00
9
10
11
12
13	Total Needed	$220.00
Sheet1

Cell Formulas
Range	Formula
U13	U13	=SUM(U5:U12)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
T5:U8	Cell Value	contains "Payday"	text	NO
T5:U8	Expression	=T5=TODAY()	text	NO
T5:U8	Expression	=MonthToDisplayNumber<>MONTH(T5)	text	NO

 

[Peter_SSs]

Try like this

xFrosty.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
5	30		31		1		2		3		4		5			Phone	34
6	S - Phone - $34						S - Debt - $30		S - Power - $100							Debt	30
7									S - School - $56							Power	100
8																School	56
9
Sheet2

Cell Formulas
Range	Formula
T5:T8	T5	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,TRIM(MID(SUBSTITUTE(E6:Q7,"-",REPT(" ",100)),100,100)))&"</c></p>","//c")
U5:U8	U5	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,TRIM(RIGHT(SUBSTITUTE(E6:Q7,"-",REPT(" ",100)),100)))&"</c></p>","//c")
Dynamic array formulas.

 

[xFrosty]

Thank you, can this be used on different areas, such as E6:R10,E13:R17, E20:J24

Otherwise my other idea might just have to work. Leaving it as is and finding the amounts in my cells