tianzl66/Sheetpedia huggingface dataset:
Spreadsheet: 163594d1340375777-how-to-obtain-the-address-of-a-column-example
Sheet: Sheet1

ID | Criteria 1 | [NAME] |  |  |  | ID | Criteria 1 | [NAME]
Data 1 | 1 | 0 |  |  |  | Data 1
Data 1 | 1 | 1 |  |  |  | Data 2
Data 2 | 1 | 0 |  |  |  | Data 3
Data 3 | 0 | 1 |  |  |  | Data 4
Data 4 | 0 | 0
Data 4 | 0 | 1 |  |  |  |  | What works :
 |  |  |  |  |  |  | SUMIF(A:A,G2,B:B)
 |  |  |  |  |  |  | Whats I want:
 |  |  |  |  |  |  | SUMIF(A:A,G2,LEFT(ADDRESS(1,MATCH(H1,1:1,0),1) & ":" & LEFT(ADDRESS(1,MATCH(H1,1:1,0),1))
 |  |  |  |  |  |  | Since
 |  |  |  |  |  |  | LEFT(ADDRESS(1,MATCH(H1,1:1,0),4) & ":" & LEFT(ADDRESS(1,MATCH(H1,1:1,0),4)
 |  |  |  |  |  |  | is
Spreadsheet: 94084d1293911383-summery-on-each-change-rqed-output-
Sheet: DATA (2)

NAME | GR | ITEM | RATE | BANKER
FORGE INFOSYS | A1 | 200 | 4521 | AMINO AMRO
FORGE INFOSYS | A1 | 50 | 2455 | AMINO AMRO
FORGE INFOSYS | A2 | 20 | 4521 | AMINO AMRO
FORGE INFOSYS | A1 | 10 | 2455 | AMINO AMRO
FRNACIS CEMENT CO | A1 | 30 | 5000 | ROYAL BANK
FRNACIS CEMENT CO | A2 | 50 | 2155 | ROYAL BANK
FRNACIS CEMENT CO | A1 | 50 | 3200 | ROYAL BANK
FRNACIS CEMENT CO | A1 | 100 | 3200 | ROYAL BANK
HIMALYA STEEL CEMENT | A1 | 100 | 3300 | USA BANK
HIMALYA STEEL CEMENT | A1 | 200 | 3300 | USA BANK
HIMALYA STEEL CEMENT | A3 | 300 | 4000 | USA BANK
HIMALYA STEEL CEMENT | A1 | 200 | 3200 | USA BANK
HIMALYA STEEL CEMENT | A1 | 100 | 3300 | USA BANK
ICCHAMANI SUPPLIER | A3 | 100 | 3200 | ROYAL BANK
K K INFOSYS | A1 | 100 | 3000 | ROYAL BANK
K K INFOSYS | A1 | 200 | 3000 | ROYAL BANK
K K INFOSYS | A2 | 300 | 4000 | ROYAL BANK
K K INFOSYS | A2 | 100 | 4000 | ROYAL BANK
K K INFOSYS | A2 | 200 | 4000 | ROYAL BANK
LUKAS STEEL | A1 | 300 | 3000 | USA BANK
LUKAS STEEL | A1 | 100 | 3000 | USA BANK
LUKAS STEEL | A1 | 200 | 3000 | USA BANK
Spreadsheet: 274998d1383306678-separate-text-string-using-search-book2-2-
Sheet: Sheet1

Black Sheep by Groove Armada
Same Mistakes by The Echo Friendly
Incredibly Still by [NAME]
Crest by The Antlers
Touch Me I'm GoingTo Scream, Part 2 by My Morning Jacket
Boy by Ski Lodge
Gramatik
The First Thirteen Minutes of Love by [NAME]
Ground Luminosity by Entheogenic
Open Your Eyes by Melorman
Leaving the House by The Fabric
Moth by Human Mesh Dance
444 by Autechre
[NAME] by Jacaszek
[NAME] by [NAME]
Tiger Mountain Peasant Song by Fleet Foxes
Activation by Lazerhawk
When It's Cold I'd Like to Die by Moby
Rain of Gold by Young Empires
Company by [NAME]
Lamplight by The Album Leaf
Pearly-Dewdrops Drops by [NAME]
Radial Summer / Signal Evening by [NAME] (Eluvium)
The Aubergine of the Sun by Ott
First Dream Called Ocean by Helios
[NAME] by [NAME]
Unravel by Bjork
Film by [NAME]
Too Late by M83
Amorph by [NAME]
Old Devil by Emancipator
Underwater by [NAME]
Third ([NAME] remix) by Hiatus
Tides by Phaeleh
Cold Tenderness by Unquote
The Informer by M-Seven
No Dice by Tipper
Daylight Goodbye by Message To Bears
Spreadsheet: 804604d1668178564-countif-to-find-bd1-in-a-range-excluding-bd10-bd11-etc-example-postcodes-worksheet
Sheet: Sheet1

 | Spreadsheet showing which postcodes attendees are from
 | Need a formula that looks for BD1 but doesn't count BD10, BD11 etc
 | As you can see, BD1 is shown occuring 3 times but this is just because the three BD15's also contain BD1.
 | Postcodes of attendees |  | Postcode | Number of attendees from each postcode
 | BD4 0QH |  | BD1
 | Bd73bw |  | BD2 | 1
 | BD73bs |  | BD3 | 5
 | Bd73ju |  | BD4 | 2
 | Bd7 1bn |  | BD5 | 8
 | BD5 9NP |  | BD6 | 0
 | [NAME] |  | BD7 | 14
 | BD5 8PT |  | BD8 | 2
 | Bd73ju |  | BD9 | 5
 | Bd5 9dw |  | BD10 | 0
 | Bd37ep |  | BD11 | 0
 | Bd7 3bw |  | [NAME] | 0
 | BD9 4AU |  | BD13 | 0
 | Bd73bw |  | BD14 | 0
 | BD5 0NG |  | BD15 | 3
 | Bd3 9nt |  | BD16 | 0
 | Bd3 8bj |  | BD17 | 0
 | BD3 8BJ |  | BD18 | 0
 | Bd7 2sb |  | BD19 | 0
 | Bd7 1bn |  | BD20 | 0
 | Bd5 0pp |  | BD21 | 0
 | BD9 4AY |  | BD22 | 0
 | BD2 3SQ |  | BD23 | 0
 | Bd88az |  | BD24 | 0
 | BD73BW
 | BD9 4BL
 | [NAME]
 | Bd9 5pt
 | Bd5 9pr
 | Bd5 9ae
 | BD3 9ED
 | BD9 4AY
 | Bd15 7ry
 | Bd7 2bl
 | Bd73dt
 | Ls167bg
 | Bd15 8he
 | BD7 3PB
 | Bd7 4hd
 | BD5 0NT
 | BD4 9RN
Spreadsheet: 0f5d1feb-673f-48ff-af58-740d1807e773
Sheet: 1.11

Table 1.11: Population by age groups (<15, 16-64, 65+), country and sex,  2012 population estimate/census (percentage of total)
Country | Age | Male | Female | Total
Bahrain*
 | <15 | 10.70191293869559 | 10.22133520777895 | 20.92324814647454
 | 15-64 | 50.23447306321233 | 26.60239995983331 | 76.83687302304565
 | 65+ | 1.111362152934679 | 1.128516677545146 | 2.239878830479825
Egypt
 | <15 | 16.13820648998422 | 14.95603103441869 | 31.09423752440291
 | 15-64 | 32.78617142841939 | 31.71064852898942 | 64.49681995740882
 | 65+ | 2.175798255583388 | 2.233144262604889 | 4.408942518188278
Iraq
 | <15 | 20.62414667207371 | 19.24352113914572 | 39.86766781121943
 | 15-64 | 28.88527308598459 | 28.35381846560706 | 57.23909155159163
 | 65+ | 1.414653409125458 | 1.478587228063479 | 2.893240637188938
Jordan
 | <15 | 19.14613337507827 | 18.14762053850971 | 37.29375391358798
 | 15-64 | 30.72792736380714 | 28.67055416405761 | 59.39848152786475
 | 65+ | 1.675720100187852 | 1.632044458359424 | 3.307764558547276
Morocco
 | <15 | 13.02880633187103 | 13.56259778507224 | 26.5944718839157
 | 15-64 | 34.11663650029143 | 33.31594932048962 | 67.42951805380864
 | 65+ | 3.190477651317606 | 2.785532410958064 | 5.97601006227567
Oman
 | <15 | 11.26592543482185 | 10.79142627970691 | 22.05735171452877
 | 15-64 | 51.69890625888427 | 23.5948990029749 | 75.29380526185918
 | 65+ | 1.420958117879821 | 1.227884905732246 | 2.648843023612067
Palestine
 | <15 | 20.64643318574723 | 19.79140584439103 | 40.43783903013826
 | 15-64 | 28.89633250592258 | 27.74577581462148 | 56.64210832054406
 | 65+ | 1.242863029087327 | 1.677189620230344 | 2.920052649317672
Qatar*
 | <15 | 7.614746089522989 | 7.252424948794295 | 14.86717103831728
 | 15-64 | 66.2033095998943 | 18.01332820073907 | 84.21663780063335
 | 65+ | 0.550118686432926 | 0.3660724746164551 | 0.9161911610493811
Saudi Arabia
 | <15 | 15.57625481253443 | 14.79705965513302 | 30.37331446766746
 | 15-64 | 39.68127368590687 | 27.21557260018917 | 66.89684628609605
 | 65+ | 1.407410185575746 | 1.322429060660754 | 2.7298392462365
Tunisia
 | <15 | 12.11456434718747 | 11.41172329722832 | 23.52628764441578
 | 15-64 | 34.25220147510197 | 35.1933159705542 | 69.44551744565617
 | 65+ | 3.446191397691222 | 3.582003512236822 | 7.028194909928043
(*) ESCWA calculated
Spreadsheet: 704837d1605771622-unable-to-make-powerquery-extract-data-correctly-onto-an-excel-sheet-tennispq17-2
Sheet: Tennis Data

Surface | Player |  | Surface | Player | Name | Age | Wins | Matches | Lost | Surface Win % | Best Surface | Best Surface Win % | Prize Money
I. Hard | [NAME] |  | I. Hard | [NAME] | [NAME] | 23 | 298 | 473 | 175 | 0.6383 | Carpet | 0.7143 | 4442257
I. Hard | Dominic Thiem |  | I. Hard | Dominic Thiem | Dominic Thiem | 27 | 445 | 664 | 219 | 0.5714 | Clay | 0.76 | 22132368
I. Hard | Stefanos Tsitsipas |  | I. Hard | Stefanos Tsitsipas | Stefanos Tsitsipas | 22 | 333 | 502 | 169 | 0.6543 | Clay | 0.7283 | 10425605
I. Hard | [NAME] |  | I. Hard | [NAME] | [NAME] | 34 | 1073 | 1303 | 230 | 0.6897 | Clay | 0.9051 | 119601561
Grass | [NAME] |  | Grass | [NAME] | [NAME] | 34 | 1073 | 1303 | 230 | 0.7802 | Clay | 0.9051 | 119601561
Grass | Dominic Thiem |  | Grass | Dominic Thiem | Dominic Thiem | 27 | 445 | 664 | 219 | 0.5152 | Clay | 0.76 | 22132368
Clay | Dominic Thiem |  | Clay | Dominic Thiem | Dominic Thiem | 27 | 445 | 664 | 219 | 0.76 | Clay | 0.76 | 22132368
Clay | [NAME] |  | Clay | [NAME] | [NAME] | 34 | 1073 | 1303 | 230 | 0.9051 | Clay | 0.9051 | 119601561
I. Hard | [NAME] |  | I. Hard | [NAME] | [NAME] | 23 | 226 | 380 | 154 | 0.4444 | Clay | 0.6652 | 599910
I. Hard | Oceane Dodin |  | I. Hard | Oceane Dodin | Oceane Dodin | 24 | 280 | 451 | 171 | 0.6667 | Carpet | 0.7778 | 1077212
I. Hard | sara Sorribes Tormo |  | I. Hard | sara Sorribes Tormo | sara Sorribes Tormo | 24 | 321 | 522 | 201 | 0.4615 | Clay | 0.6803 | 1432419
I. Hard | camila giorgi |  | I. Hard | camila giorgi | camila giorgi | 28 | 327 | 566 | 239 | 0.6136 | Grass | 0.6308 | 3754718
Spreadsheet: 79493d1279741128-golf-workbook-golf-league
Sheet: League

 |  | 2009-04-23 00:00:00 | Week 1
 |  |  | Hole | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | Gross | Net
 | Net |  | Front Par | 4 | 4 | 5 | 3 | 4 | 3 | 5 | 3 | 4 | 35
 | Rank | Name | HCP | 10 | 4 | 5 | 12 | 16 | 14 | 6 | 2 | 1
 |  |  | Low Score for [NAME] (MIN) | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  | 0
 | 2 | [NAME] | 5 | 6 | 4 | 5 | 3 | 5 | 4 | 5 | 6 | 6 | 44 | 39 | Double | Par | Par | Par | [NAME] | [NAME] | Par | Other | Double
 | 6 | [NAME] | 12 | 6 | 6 | 5 | 3 | 6 | 6 | 9 | 5 | 7 | 53 | 41 | Double | Double | Par | Par | Double | Other | Other | Double | Other
 |  | Carrie | 13 |  |  |  |  |  |  |  |  |  | 0 | 0
 | 3 | George | 5 | 5 | 5 | 5 | 5 | 5 | 3 | 6 | 4 | 7 | 45 | 40 | [NAME] | [NAME] | Par | Double | [NAME] | Par | [NAME] | [NAME] | Other
 | 3 | [NAME] | 9 | 5 | 6 | 6 | 5 | 6 | 5 | 6 | 5 | 5 | 49 | 40 | [NAME] | Double | [NAME] | Double | Double | Double | [NAME] | Double | [NAME]
 | 10 | [NAME] | 10 | 6 | 6 | 10 | 4 | 4 | 3 | 7 | 5 | 7 | 52 | 42 | Double | Double | Other | [NAME] | Par | Par | Double | Double | Other
 | 1 | [NAME] | 3 | 3 | 4 | 6 | 5 | 4 | 4 | 5 | 5 | 5 | 41 | 38 | [NAME] | Par | [NAME] | Double | Par | [NAME] | Par | Double | [NAME]
 | 6 | [NAME] | 14 | 6 | 8 | 6 | 4 | 7 | 5 | 7 | 5 | 7 | 55 | 41 | Double | Other | [NAME] | [NAME] | Other | Double | Double | Double | Other
 | 6 | [NAME] | 14 | 6 | 5 | 7 | 5 | 7 | 6 | 6 | 5 | 8 | 55 | 41 | Double | [NAME] | Double | Double | Other | Other | [NAME] | Double | Other
 | 6 | Rich | 14 | 6 | 7 | 6 | 5 | 8 | 6 | 6 | 6 | 5 | 55 | 41 | Double | Other | [NAME] | Double | Other | Other | [NAME] | Other | [NAME]
 | 3 | Stevie | 9 | 4 | 7 | 6 | 4 | 6 | 5 | 8 | 4 | 5 | 49 | 40 | Par | Other | [NAME] | [NAME] | Double | Double | Other | [NAME] | [NAME]
Spreadsheet: 747476d1631531754-formula-to-show-volume-of-2nd-identifier-if-volume-of-1st-identifier-is-missing-sample-workbook-v3
Sheet: Sheet1

RM Code | CalendarQuarter | Volume | Identifier | Expected Result
APPLE | CY17Q1 | 10000 | 1 | 10000
APPLE | CY17Q2 | 5400 | 1 | 5400
APPLE | CY17Q3 | 3300 | 1 | 3300
APPLE | CY17Q4 | 2600 | 1 | 2600
APPLE | CY18Q2 | 12800 | 1 | 12800
APPLE | CY18Q3 | 20 | 1 | 20
APPLE | CY18Q4 | 40120 | 1 | 40120
APPLE | CY19Q1 | 20000 | 1 | 20000
APPLE | CY19Q2 | 4000 | 1 | 4000
APPLE | CY19Q3 | 14000 | 1 | 14000
APPLE | [NAME] | 32000 | 1 | 32000
APPLE | CY17Q1 | 100 | 2
APPLE | CY17Q2 | 9890 | 2
APPLE | CY17Q3 | 2020 | 2
APPLE | CY17Q4 | 500 | 2
APPLE | CY18Q1 | 21120 | 2 | 21120
APPLE | CY18Q2 | 24000 | 2
APPLE | CY18Q3 | 4.04 | 2
APPLE | CY18Q4 | 31860 | 2
APPLE | CY19Q1 | 6400 | 2
APPLE | CY19Q2 | 1.02 | 2
APPLE | CY19Q3 | 41280 | 2
APPLE | [NAME] | 10000 | 2
PEAR | CY17Q1 | 10000 | 1 | 10000
PEAR | CY17Q2 | 5400 | 1 | 5400
PEAR | CY17Q3 | 3300 | 1 | 3300
PEAR | CY17Q4 | 2600 | 1 | 2600
PEAR | CY18Q2 | 12800 | 1 | 12800
PEAR | CY18Q3 | 20 | 1 | 20
PEAR | CY18Q4 | 40120 | 1 | 40120
PEAR | CY19Q1 | 20000 | 1 | 20000
PEAR | CY19Q2 | 4000 | 1 | 4000
PEAR | CY19Q3 | 14000 | 1 | 14000
PEAR | [NAME] | 32000 | 1 | 32000
PEAR | CY17Q1 | 100 | 2
PEAR | CY17Q2 | 9890 | 2
PEAR | CY17Q3 | 2020 | 2
PEAR | CY17Q4 | 500 | 2
PEAR | CY18Q1 | 21120 | 2 | 21120
PEAR | CY18Q2 | 24000 | 2
PEAR | CY18Q3 | 4.04 | 2
PEAR | CY18Q4 | 31860 | 2
PEAR | CY19Q1 | 6400 | 2
PEAR | CY19Q2 | 1.02 | 2
PEAR | CY19Q3 | 41280 | 2
PEAR | [NAME] | 10000 | 2
Spreadsheet: 840510d1692787504-find-and-match-from-two-row-book13
Sheet: Sheet2

 |  |  |  | Supplier | TransType | Ref# | Expected result | [NAME]'s [NAME] |  | Reasoning
 |  |  |  | A | BT | 123 | False |  | Okay | coz, for ref 123 supplier is different for trans type BT & GT
 |  |  |  | B | GT | 123 | False |  | Okay | coz, for ref 123 supplier is different for trans type BT & GT
 |  |  |  | A | BT | 456 | True |  | Okay | coz, for ref 456 supplier is same for trans type BT & GT
 |  |  |  | A | GT | 456 | True |  | Okay | coz, for ref 456 supplier is same for trans type BT & GT
 |  |  |  | A | BT | 789 | False |  |  | coz, for Ref 789 ther are no GT trans
 |  |  |  | A | BT | 789 | False |  |  | coz, for Ref 789 ther are no GT trans
 |  |  |  | A | BT | 101 | False
 |  |  |  | A | BT | 101 | False
 |  |  |  | B | GT | 101 | False
Spreadsheet: 108940d1305852904-need-help-to-auto-calculate-and-auto-fill-test-roster
Sheet: Jan

 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 11 | 11 |  | Work
 | We | Th | Fr | Sa | Su | Mo | [NAME] | We | Th | Fr | Sa | Su | Mo | [NAME] | We | Th | Fr | Sa | Su | Mo | [NAME] | We | Th | Fr | Sa | Su | Mo | [NAME] | We | Th | PH | AL |  | Hour
[NAME] | NZ | M2 | M2 | M1 | X | X | C | C | C | C | X | X | M2 | DT | DT | EGR | X | X | C | C | C | X | X | X | M2 | M2 | M1 | M2 | X | X | 10 | 20
[NAME] | M2 | M2 | X | X | C | C | C | C | X | X | M2 | NZ | M2 | M2 | X | X | C | C | C | F | X | X | X | M2 | M2 | NZ | X | X | M2 | M3 | 10 | 20
[NAME] | X | M3 | M2 | M2 | NZ | X | X | M2 | M1 | M2 | C | X | X | C | C | C | X | X | X | M1 | M2 | NZ | M2 | X | X | C | C | C | C | X | 10 | 20
Wayne | C | C | X | X | M2 | M1 | M2 | NZ | X | X | M2 | M2 | C | C | X | X | M2 | M2 | M2 | M2 | X | X | X | M1 | C | C | X | X | L | L | 10 | 18
[NAME] | L | L | L | X | X | D | M3 | D | D | D | X | X | M1 | M3 | M1 | D | X | X | X | M1 | D | M1 | D | X | X | X | M1 | D | M1 | D | 10 | 17
Wonder if it is possilbe to create a formular to
1. using the lookup table it can autosum the total work hour in Column AI
2. auto update the balance of PH and AL in Column AF and AG when it sees the L or PH in the cells of Row 3-7
3. auto update the value in column AF and AG to the next worksheets eg Feb,Mar etc
Spreadsheet: richard_shapiro__32458__m010508c
Sheet: Summary Table

Spot Prices (1=$104 from DWR, 0= $82.75 from Blend) |  |  | 1 |  |  | 1 |  |  | 0 |  |  | 0
Past Utility split (1=Pro rata,  0=Pro rata + 1/2 to nc |  |  | 1 |  |  | 0 |  |  | 1 |  |  | 0
Cost to Core: |  | Cents per kw: |  |  | Cents per kw: |  |  | Cents per kw: |  |  | Cents per kw:
 |  | PG&E | SCE |  | PG&E | SCE |  | PG&E | SCE |  | PG&E | SCE
Share of past DWR |  | 0.9920604559302073 | 0.4774409166865601 |  | 0.9920604559302073 | 0.4774409166865601 |  | 0.9920604559302073 | 0.4774409166865601 |  | 0.9920604559302073 | 0.4774409166865601
Cost of short |  | 0 | 0.6456262905396387 |  | 0 | 0.6456262905396387 |  | 0 | 0.5137074571361068 |  | 0 | 0.5137074571361068
Value of long |  | -0.3823980706062193 | 0 |  | -0.3823980706062193 | 0 |  | -0.3042638494486985 | 0 |  | -0.3042638494486985 | 0
Cost of generation + QF |  | 6.722699569984051 | 6.67741902345315 |  | 6.722699569984051 | 6.67741902345315 |  | 6.722699569984051 | 6.67741902345315 |  | 6.722699569984051 | 6.67741902345315
Share of Past Utility |  | 0.6058314792274591 | 0.3123895917880162 |  | 0.3029157396137295 | 0.1561947958940081 |  | 0.6058314792274591 | 0.3123895917880162 |  | 0.3029157396137295 | 0.1561947958940081
Total Gen |  | 7.938193434535498 | 8.112875822467364 |  | 7.635277694921768 | 7.956681026573357 |  | 8.016327655693017 | 7.980956989063833 |  | 7.71341191607929 | 7.824762193169825
Other T&D |  | 4.8875 | 5.25 |  | 4.8875 | 5.25 |  | 4.8875 | 5.25 |  | 4.8875 | 5.25
Total Core per proposal |  | 12.8256934345355 | 13.36287582246737 |  | 12.52277769492177 | 13.20668102657336 |  | 12.90382765569302 | 13.23095698906383 |  | 12.60091191607929 | 13.07476219316982
Total Core per current CPUC
Cost to Non-Core: |  | Cents per kw: |  |  | Cents per kw: |  |  | Cents per kw: |  |  | Cents per kw:
 |  | PG&E | SCE |  | PG&E | SCE |  | PG&E | SCE |  | PG&E | SCE
Share of past DWR |  | 0.9920604559302076 | 0.4774409166865599 |  | 0.9920604559302075 | 0.47744091668656 |  | 0.9920604559302076 | 0.4774409166865599 |  | 0.9920604559302076 | 0.4774409166865599
Cost of short |  | 10.4 | 10.4 |  | 10.4 | 10.4 |  | 8.275 | 8.275 |  | 8.275 | 8.275
Share of Past Utility |  | 0.605831479227459 | 0.3123895917880162 |  | 1.131790162095947 | 0.6719154885660925 |  | 0.605831479227459 | 0.3123895917880162 |  | 1.131790162095948 | 0.6719154885660925
Total Gen |  | 11.99789193515767 | 11.18983050847458 |  | 12.52385061802616 | 11.54935640525265 |  | 9.872891935157668 | 9.064830508474575 |  | 10.39885061802616 | 9.424356405252652
Other T&D |  | 2.212747116542884 | 1.783132079040916 |  | 2.212747116542884 | 1.783132079040916 |  | 2.212747116542884 | 1.783132079040916 |  | 2.212747116542884 | 1.783132079040916
Total Non-Core per proposal |  | 14.21063905170055 | 12.97296258751549 |  | 14.73659773456904 | 13.33248848429357 |  | 12.08563905170055 | 10.84796258751549 |  | 12.61159773456904 | 11.20748848429357
Total Non-Core per current CPUC
Spreadsheet: 376658d1423673437-indirect-function-on-this-formula-single-cell-array-formulas
Sheet: Sheet18

Ranking data with an array formula
Salesperson | Sales | Excel's Rank Function | Ranks With Array Formula |  |  |  | XL 2010 RANK.AVG
Adams | 123000
Bigelow | 98000
Fredericks | 98000 |  |  | Assigned middle rank
Georgio | 98000
[NAME] | 25000
[NAME] | 101000
Klein | 305000
Lynch | 145000
[NAME] | 145000 |  |  | Assigned average rank
Roberton | 121000
Slokum | 124000
[NAME] | 150000
Spreadsheet: 875332d1721917334-how-to-automatically-filter-multiple-arrays-of-data-by-their-relevant-headings-lookup-sheet-test
Sheet: ReferenceControlData

ControlReferenceID | [NAME] | ControlID
730690 | 1486192 | 353170
730628 | 1486204 | 353163
730759 | 1486204 | 353182
730800 | 1486206 | 353206
732688 | 1486207 | 353182
730626 | 1486216 | 353163
730749 | 1486218 | 353181
733101 | 1486223 | 353192
730632 | 1486225 | 353163
730806 | 1486231 | 353267
730813 | 1486231 | 353276
730700 | 1486239 | 353172
730770 | 1486240 | 353184
730765 | 1486249 | 353184
730750 | 1486250 | 353181
730771 | 1486256 | 353184
730766 | 1486258 | 353184
730789 | 1486260 | 353198
730767 | 1486265 | 353184
730801 | 1486268 | 353207
730697 | 1486272 | 353171
730798 | 1486274 | 353204
730807 | 1486275 | 353267
730681 | 1486276 | 353169
730659 | 1486277 | 353163
730694 | 1486278 | 353170
730768 | 1486281 | 353184
730740 | 1486287 | 353179
730797 | 1486290 | 353203
730647 | 1486291 | 353163
730660 | 1486292 | 353163
730799 | 1486293 | 353205
730790 | 1486296 | 353199
730792 | 1486296 | 353200
730788 | 1486299 | 353198
730787 | 1486304 | 353197
730769 | 1486305 | 353184
730748 | 1486306 | 353181
730791 | 1486307 | 353199
730814 | 1486316 | 353314
730816 | 1486339 | 353332
730817 | 1486340 | 353333
730664 | 1486350 | 353163
730772 | 1486471 | 353185
730794 | 1486473 | 353202
730657 | 1486474 | 353163
730668 | 1486474 | 353164
730672 | 1486474 | 353165
730678 | 1486474 | 353166
730679 | 1486474 | 353167
730680 | 1486474 | 353168
730688 | 1486474 | 353169
730696 | 1486474 | 353170
730698 | 1486474 | 353171
730716 | 1486474 | 353172
730795 | 1486480 | 353202
730810 | 1486481 | 353270
730812 | 1486481 | 353271
730734 | 1486482 | 353179
730773 | 1486484 | 353186
730774 | 1486484 | 353187
730793 | 1486487 | 353201
730635 | 1486489 | 353163
730775 | 1486489 | 353188
730776 | 1486489 | 353189
730777 | 1486489 | 353190
730778 | 1486489 | 353191
730781 | 1486492 | 353194
730796 | 1486494 | 353202
730809 | 1486497 | 353269
730802 | 1486505 | 353210
730805 | 1486506 | 353267
730808 | 1486507 | 353269
733104 | 1486509 | 353315
730815 | 1486556 | 353331
Spreadsheet: 804990d1668461252-search-bottom-to-top-of-a-column-to-displaying-all-results-that-match-a-criteria-sample-workbook
Sheet: Sheet1

Date | Invoice | What I Would Like to See
Week Beginning: 7/11/22 | POV-478 | POV-280 - Finished 30/09/22
 | POV-779 | [NAME] 25/10/22 |  | [NAME] 25/10/22
 | POV-680 | POV-311 - Finished 28/10/22 |  | POV-311 - Finished 28/10/22
 | POV-681 | POV-182 - Finished 10/11/22 |  | POV-182 - Finished 10/11/22
 | POV-182 - Finished 10/11/22
Week Beginning: 31/10/22 | POV-554
 | POV-455
 | POV-656
 | POV-257
 | POV-658
Week Beginning: 24/10/11 | POV-110
 | POV-311 - Finished 28/10/22
 | POV-399
 | [NAME] 25/10/22
 | POV-901
 | POV-182
Week Beginning: 26/09/22 | POV-280 - Finished 30/09/22
 | POV-581
 | POV-900
 | POV-483
Ideally when invoices have been marked with " - Finished DDMMYY" in Column B, I want a list in column C that searches for the "- Finished " flag in Column B and returns all the cells with " - Finished " in them, taking the results from the bottom of Column B, up to the top of [NAME]
Currently data is added to Column A and B via a macro each week so I can see what invoices were started/ finished in any given week. (It makes sense for this data to run from newest week down to oldest week and I would prefer not to change that)
Side note: If possible I would prefer to avoid VBA or a helper column.
Reference
Column A = Lists the week beginning dates
Column B = Lists the invoices I started or finished that week (Shows all invoices in any given week up until "Week Beginning: DDMMYY" appears again in Column A)
Column C = What I would like the result to look like (I've manually put this here to show my desired outcome)
Column D = List of finished invoices created using a "FILTER" formula to look for the " - Finished " flag in Column B (I ideally need this list inverted)
Column E = List of finished invoices created using a "UNIQUE" formula to look for the " - Finished " flag in Column B (I ideally need this list inverted)
Column F = My attempts (There are 3 different attempts here) trying to get previous formulas in column D and E to search from bottom to top.
Spreadsheet: 758371d1638833751-formula-to-add-closed-when-all-actions-are-competed-audit-tracker-2021
Sheet: Sheet1

Audit | Audit Number | Lead Auditor | Due | Auditee | Date Completed | NCR's raised | Department | Close | Right? | wrong | Audit Number | NCR Ref | Action Owner | Department | Action target Date | Completed | Repeat NCR | Closed | On time
HR | SHA-MEG-1234 | [NAME] | 2022-01-01 00:00:00 | [NAME] |  |  |  |  |  |  | SHA-MEG-1237 | NCR-1 | [NAME] | Mod 1 | 2021-04-01 00:00:00 | 2021-04-01 00:00:00 | Yes | Yes
QUALITY | SHA-MEG-1235 | [NAME] | 2021-12-01 00:00:00 | [NAME] |  |  |  |  |  |  | SHA-MEG-1237 | NCR-2 | [NAME] | Mod 1 | 2021-04-01 00:00:00 | 2021-06-01 00:00:00 | No
ENGINEERING | SHA-MEG-1236 | [NAME] | 2021-12-01 00:00:00 | [NAME] |  |  |  |  |  |  | SHA-MEG-1237 | NCR-3 | [NAME] | [NAME] | 2021-04-01 00:00:00 | 2021-04-01 00:00:00 | Yes | Yes
MANUFACTURING | SHA-MEG-1237 | shrek | 2021-03-01 00:00:00 | [NAME] | 2021-03-01 00:00:00 | 3 | MANU |  |  |  | SHA-MEG-1239 | NCR-4 | [NAME] | [NAME] | 2021-09-01 00:00:00 | 2021-09-02 00:00:00 | No
MANAGEMENT | SHA-MEG-1238 | [NAME] | 2021-04-01 00:00:00 | [NAME] | 2021-05-01 00:00:00 | 0 | SLC |  |  |  | SHA-MEG-1239 | NCR-5 | [NAME] | Mod 1 | 2021-09-01 00:00:00 | 2021-09-01 00:00:00 | Yes
BUSSINESS REVIEW | SHA-MEG-1239 | [NAME] | 2021-08-01 00:00:00 | [NAME] | 2021-08-01 00:00:00 | 2 | SLC |  |  |  | SHA-MEG-1240 | NCR-6 | [NAME] | Mod 1 | 2022-01-01 00:00:00 |  | No
HS | SHA-MEG-1240 | [NAME] | 2021-11-01 00:00:00 | Doe | 2021-12-01 00:00:00 | 1 | HSE
INSPECTION | SHA-MEG-1241 | [NAME] | 2022-03-01 00:00:00 | Will
RELEASE | SHA-MEG-1242 | [NAME] | 2022-04-02 00:00:00 | Huge
RCCA | SHA-MEG-1243 | [NAME] | 2022-05-03 00:00:00 | [NAME]
 | I = Yes if B exists in L, and S is Yes |  |  |  |  |  |  |  |  | K = Yes if L exists in B, and S is Yes
 |  |  |  |  |  |  | J = Yes if B exists in L, and all occurences of L have S=Yes
Spreadsheet: 129511d1321643342-if-and-to-extact-item-from-table-or-not-solved-location2
Sheet: Sheet1

 | mileage
location | HLN | MSLA | BZN | SPO | GRF
big sky | 6 | 34 | 2 | 34 | 556
Lakeside | 13 | 45 | 334 | 23 | 34
I90 Truck Stop | 56 | 23 | 45 | 45 | 23
Stromberg's | 87 | 1 | 67 | 56 | 115
GF Sinclair | 22 | 2 | 87 | 65 | 5
H&R #5 | 34 | 45 | 7 | 76 | 1
H&R #4 | 45 | 5 | 68 | 86 | 4
H&R #1 | 6 | 6 | 7 | 87 | 2
GF Sinclair | 7 | 78 | 65 | 34 | 5
Joyco #1 -stn | 87 | 87 | 55 | 87 | 41
[NAME] (bulk) | 97 | 56 | 9 | 87 | 42