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
<colgroup><col width="87" span="11" style="width: 65pt;"></colgroup><tbody>
</tbody>
=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>