Show only columns/rows where information where data is entered

[anjadevilliers]

I have a large sheet with column headings and row labels. I want to create a second sheet and, automatically, only show the columns and rows where there is information completed. This has to be automated, thus I can't use a filter on the sheet.

In the image example - on a secondary tab I want to show the result should exclude the columns Tuesday and Saturday (as they have no data) and it should exclude the road Simon (as again there is no data for that row)

I hope someone can help.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Mon	Tue	Wed	Thu	Fri	Sat	Sun
2	John	School				Swim					John	Mon	School
3	Peter			Gym				Park			John	Fri	Swim
4	Paul			Gym							Peter	Wed	Gym
5	Simon										Peter	Sun	Park
6	Felix				Swim			Park			Paul	Wed	Gym
7											Felix	Thu	Swim
8											Felix	Sun	Park
Sheet5

Cell Formulas
Range	Formula
K2:M8	K2	=LET(d,B2:H6,HSTACK(TOCOL(IF(d<>"",A2:A6,1/0),2),TOCOL(IF(d<>"",B1:H1,1/0),2),TOCOL(d,1)))
Dynamic array formulas.

 

[anjadevilliers]

Hi - Thank you for the welcome

Is there a any way to get the results in the same format, just excluding the columns and rows without the data - like the image attached

 

[Fluff]

How about

=LET(d,A1:H100,FILTER(FILTER(d&"",BYROW(d,LAMBDA(br,COUNTA(br)>1))),BYCOL(d,LAMBDA(bc,COUNTA(bc)>1))))