Combine complex arrays

[drluke]

I am combining data in several columns into one single column using this formula:
=OFFSET($F$2:$F$509,MOD(ROW()-ROW(ColumnData1),ROWS($F$2:$F$509)),TRUNC((ROW()-ROW(ColumnData1))/ROWS($F$2:$F$509),0),1,1)

Then I'm using this formula to ignore blank and zero cells, round the number and only look at cells that match a criteria:
{=IFERROR(INDEX(ROUND($R$2:$R$1527,2),SMALL(IF(ISNUMBER(1/$R$2:$R$1527),ROW($R$2:$R$1527)-ROW($F$2)+1),ROWS($S$2:S2))),"")}

I’m using named ranges:AccRange = columns I,J & K; ColumnData = column R, where I’m placing all my combined data; Period = col F.

Is there a way I can combine these formulas into one less complex formula? Or can I make my AccRange into into one single column using an array formula?

What can I add to my second formula to change negative numbers into positive ones (reverse the sign, but only for negative ones)?

Below is a sample of my data col A-K

Client Code	Client Name	Loc	BS	Yr	Period	Fee %	Invoice Value	Mgt Fee	Payments	Adjmts
CBAN700	BANES RECRUITMENT	400	014	2018	1	1. %	2,405.00	24.05
CBBC703	BOURNEMOUTH BC PNs	500	031	2018	1	1. %	10,012.75	100.13
CBCC001	Birmingham City Council REC	300	014	2018	1	1. %	47,384.92	473.85
CBCC001	Birmingham City Council REC	300	014	2018	1	2. %	46,455.80	929.12
CBCC002	Birmingham City Council PNs	500	031	2018	1	1. %	20,004.24	200.04	-2678.75
CBCC002	Birmingham City Council PNs	300	014	2018	2	2. %	19,612.00	392.24	-435.21
CBPN700	BANES PUBLIC NOTICES	500	031	2018	2	1. %	14,054.01	140.54	-2172.28
CBUC006	Buckinghamshire County Council	300	014	2018	2	1. %	2,816.25	28.16	-1554.69
CCCC604	Coventry City Council REC	300	014	2018	2	1. %	8,541.70	85.42	-494.86
CCCC700	CAMBRIDGE CITY COUNCIL PN	500	031	2018	3	1. %	4,309.07	43.09	-1239.12
CCCC701	Cambridgeshire County Council	500	031	2018	3	1. %	25,504.40	255.04	-1481.00
CCCC704	COVENTRY CITY COUNCIL PN	500	031	2018	3	1. %	15,873.28	158.73	-138.34
CCCC706	COVENTRY CITY COUNCIL - Schools	400	014	2018	3	1. %	919.81	9.20	-639.31
CCDC700	CRAVEN DISTRICT COUNCIL	500	031	2018	3	1. %	3,232.64	32.33	-8.92
CCEC700	CHESHIRE EAST COUNCIL (PN)	500	031	2018	3	1. %	11,620.54	116.21	-99.55
CCSR001	Government Recruitment Sevice	316	007	2018	3	1. %	257,895.48	2578.95	-113.56
CCSR002	Government Recruitment service	208	007	2018	3	1. %	4,230.79	42.31	-195.80
CDCC700	Derby City Council	300	014	2018	3	1. %	4,944.04	49.44	-1322.77
CDDC001	Daventry District Council PNS	500	031	2018	3	1. %	6,992.12	69.92	-385.31
CDDC700	DAVENTRY DISTRICT COUNCIL REC	400	014	2018	3	1. %	16,122.00	161.22	-92.45
CDPS003	Parliamentary Digital Service	107	021	2018	3	1. %	3,500.00	35.00	-177.45
CESS900	Essex C C (Public Notice/Tenders)	500	031	2018	3	1. %	45,663.05	456.63	-235.94
CHBB700	HINCKLEY & BOSWORTH BC	400	014	2018	3	1. %	5,550.55	55.51	-796.31
CHIG006	Highways England	104	007	2018	3	1. %	10,376.22	103.76	-0.19
CHSC700	Herts for Learning Ltd	400	009	2018	4	1. %	559.65	5.60	-0.11
CHST002	High Speed Two (HS2) Ltd	320	007	2018	4	1. %	819.05	8.19	-15678.54
CKCC702	Kent County Council	307	014	2018	4	1. %	12,700.05	127.00	-99.01
CLBO700	LB Barnet Corp & Schools	308	014	2018	4	1. %	4,871.42	48.71	-159.92
CLCC702	LEICESTERSHIRE COUNTY COUNCIL (REC)	400	014	2018	4	1. %	1,855.00	18.55	-140.00
CLGA700	LOCAL GOVERNMENT ASSOCIATION	300	014	2018	4	1. %	1,681.00	16.81	-2985.19
CMOJ001	Ministry of Justice	100	007	2018	4	1. %	9,920.00	99.20	-19.47
CNCC705	NORTHUMBERLAND COUNTY COUNCIL REC	400	014	2018	4	1. %	3,214.86	32.15	-659.98	27.75
CNTC700	NORTH TYNESIDE COUNCIL PNS	500	031	2018	4	1. %	7,271.64	72.72	-238.17	33.55
CPCC700	PETERBOROUGH CITY COUNCIL	400	014	2018	4	1. %	5,706.10	57.06	-59.95	142.95
CRCB700	REDCAR & CLEVELAND PNs	500	031	2018	5	1. %	3,318.90	33.19	-37.44	212.39
CRED600	Redcar & Cleveland BC REC	400	014	2018	5	1. %	7,267.50	72.68	-2282.97	46.35
CSBC700	Slough Borough Council	400	014	2018	5	1. %	23,556.62	235.57	-395.70	361.77
CSGC600	South Gloucestershire Council REC	400	014	2018	5	1. %	4,171.34	41.71	-1.86	489.26
CSGC700	South Gloucestershire Council PN	500	031	2018	5	1. %	12,847.58	128.48	-1080.06	101.34
CSMB700	SANDWELL MBC - PNs	500	031	2018	5	1. %	2,629.45	26.29	-544.43	1.57
CSOL700	Solihull MBC Schools	300	014	2018	5	1. %	985.62	9.86	-206.69	512.83
CSOL701	Solihull MBC Rec	300	014	2018	5	1. %	6,521.08	65.21	-361.43	236.35
CSOL702	Solihull MBC PN	500	031	2018	5	1. %	3,066.61	30.67	-1.19
CSOM005	Somerset County Council	300	044	2018	5	1. %	1,200.00	12.00	-11.02
CSOT010	Stoke on Trent City Council	400	014	2018	5	1. %	4,265.23	42.65
CSOT010	Stoke on Trent City Council	500	031	2018	5	1. %	4,223.00	42.23
CTLR002	The Land Registry	110	007	2018	5	1. %	-	0.00	-398.35
CSTS700	Stoke on Trent City Council	400	014	2018	6	1. %	10,666.00	106.66		-218.12
CTOH002	London Borough of Tower Hamlets	500	031	2018	6	1. %	9,506.00	95.06
CWCC004	Warwickshire County Council - Rec	300	014	2018	6	1. %	12,948.00	129.48
CWCC007	Warwickshire CC PNs	500	031	2018	6	1. %	20,913.28	209.13
CWCC010	Warwickshire CC SCHOOLS	400	014	2018	6	1. %	8,355.00	83.55
CWMP700	West Midlands Police	112	044	2018	6	1. %	1,117.70	11.18
CWOR009	Worcestershire CC PN	500	031	2018	6	1. %	22,434.91	224.35
CWOR009	Worcestershire CC PN	400	014	2018	6	1. %	22,212.80	222.13
CWOR700	Worcestershire CC Rec	400	014	2018	6	1. %	485.00	4.85

<colgroup><col width="87" span="11" style="width: 65pt;"></colgroup><tbody>
</tbody>

 

[DRSteele]

I only know part of the answer. If you wish to convert the negative numbers to postive numbers, there are several ways. One involves function IF, but that can mean large array forumlas. A simple way to remove the negation is to square the number and use function SQRT. E.g., If A1 contains the topic number of -72.5, then =SQRT(A1^2) will convert it to 72.5 but will not alter positive numbers.