large/complicated =(Sort(Unique)) or similar

[Mr_Stern]

Hi,

Every month I have to put together a report.
This report comes showing all figures for a client and roll on one line, with columns showing data for the related weeks.

E.g client 1 - admin - Wk1 hour: Wk1 rate: Wk1 mark up%: Wk2 hours: Wk2 rate: Wk2 mark up%

This can be an estimated 1000 rows long, and obviously will get to 52 weeks of data.
Where I run into the problem, is I need to split out the rows for each mark up% reported (there are generally only 2, but up to 5 unique %'s)

So if client 1 has a mark up of 10% in week 1, and a mark up of 20% in week 2,
the client will need to show on two rows with the corresponding weeks figures in separate rows.
The non relating week could show either blanks or 0's.

Realistically there are 23 columns of distinct information before the data follows.

Any help would be great.
'I've done (sort(Unique)) on files like this, but not one that grows every month and not one with in the end 52 columns that need to be accounted for.

Kind regards,

Also asked here splitting rows of data based on one specific weekly figure (10 columns x52 wks)

 

[KRice]

This isn't what you asked for, but have you considered transforming your data into a flat table that resembles this...

Monthly report split.xlsx
	I	J	K	L	M	N	O	P	Q	R	S	T	U
29	example		Service Structure3	County4	Client Name (Work Site)	Client TSS ID	Grade5	Roll	Total Net Hours	Total of Basic Hourly Pay	Agency Fee Percentage	Total Agency Fee	Week
30	1	0	Private	Dublin	Sprockets galore	12456	3029	Admin	10	20	0.095	19	1
31	1	0	Private	Dublin	Sprockets galore	12456	3029	Admin	10	20	0.085	17	2
32	1	0	Private	Dublin	Sprockets galore	12456	3029	Admin	10	20	0.2	40	3
33	2	0	Private	Dublin	Sprockets galore	12456	3088	IT tecnitian	10	20	0.095	19	1
34	2	0	Private	Dublin	Sprockets galore	12456	3088	IT tecnitian	10	20	0.085	17	2
35	2	0	Private	Dublin	Sprockets galore	12456	3088	IT tecnitian	10	20	0.085	17	3
36	3	0	Private	Dublin	Sprockets galore	12456	3072	Engineer	10	20	0.095	19	1
37	3	0	Private	Dublin	Sprockets galore	12456	3072	Engineer	10	20	0.095	19	2
38	3	0	Private	Dublin	Sprockets galore	12456	3072	Engineer	10	20	0.095	19	3
39	4	0	Private	Cork	Cogs plus	12884	1488	Director	10	20	0.095	19	1
40	4	0	Private	Cork	Cogs plus	12884	1488	Director	10	20	0.095	19	2
41	4	0	Private	Cork	Cogs plus	12884	1488	Director	10	20	0.095	19	3
42	5	0	Private	Cork	Cogs plus	12884	3029	Admin	10	20	0.095	19	1
43	5	0	Private	Cork	Cogs plus	12884	3029	Admin	10	20	0.095	19	2
44	5	0	Private	Cork	Cogs plus	12884	3029	Admin	10	20	0.095	19	3
Sheet1

Cell Formulas
Range	Formula
I30:U44	I30	=LET(a,A2:H6, b,I2:T6, bhdrs,I1:T1, ccnt,4, ri,INT(SEQUENCE(ROWS(a)*COLUMNS(b)/ccnt,1,0)/3)+1, ablk,INDEX(a,ri,SEQUENCE(,COLUMNS(a))), bblk, WRAPROWS(TOROW(b),ccnt), wkhds,TAKE(WRAPROWS(--TEXTAFTER(TEXTBEFORE(bhdrs," ",2)," "),ccnt),,1), wkhstk,REDUCE(wkhds,SEQUENCE(ROWS(a)-1),LAMBDA(x,y,VSTACK(x,wkhds))), HSTACK(ablk,bblk,wkhstk) )
Dynamic array formulas.

That has a lot of advantages for subsequent analysis. For example, you can apply an autofilter to the results columns or construct other formulas that take advantage of the table structure.