Make 365 Function Backward compatible with 2016 version

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
8,789
Office Version
  1. 365
Platform
  1. MacOS
This is a nice to have at the moment
and i'm always interested in alternative ways to do things - BUT the main user of a larger spreadsheet i'm putting together is using 2016 , and may update later - BUT i would like if possible to male this work in 2016 version as well

For simplicity, I have created a very simple sample - just for the forum, which also works in the real data workbook, so hopefully no issues when i apply to the larger book

I have a formula in 365 version - using a UNQUE & FILTER function

This extracts a UNIQUE list , based on 2 NON-Contiguous columns , column A will contain blanks , which need to be ignored, as there will be a thickness and i dont want a group BLANK/THICKNESS

This is part of a much bigger spreadsheet and so I do NOT want to incorporate a pivot table as its part of a larger table , although i would use a pivot table if no simple solution, and change the layout - I have the pivot table in my example - XL2BB has shown the pivot table - BUT i have also put the sample on a share dropbox

I also do not want VBA - as again its part of a larger spreadsheet and avoiding VBA as much as possible, if i do add VBA later then i can incorporate it , but the vision is not to at the moment

Not end of world if not easy with Older 2016 versions Functions to replicate , its just the main person using the spreadsheet currently has 2016 version, although may in the future upgrade.... and i see a similar possible spreadsheet for my daughter framing material - and she has 2016 Mac version i think (which maybe 2011)

Sort is not that important - just i can do it in 365
=SORT(UNIQUE(FILTER(FILTER(A2:D9,A2:A9<>""),{1,0,0,1})))

I know the {1,0,0,1} allow for the NON Contiguous ranges - so only the 2 columns are output - WOOD/THICKNESS
And i found the Filter(Filter ( Excel Filter Function Trick Using Non-Adjacent Columns - Xelplus - Leila Gharani ) - part to also be able to add a criteria so blanks are ignored - which will appear anywhere in rows - and again the main table cannot be sorted

Then once i have extracted the UNIQUE Wood Type and Thickness - i then use a simple SUMIFS() to group into material - which is what i'm after


Group-Summary.xlsx
ABCDEFGHIJKLMN
1Woodother columnsother columnsthicknessMetresGroup by Wood & Thickness
2A0.10512WoodThicknessTotalWoodthicknessSum of Metres
3C0.10512A0.10524A0.10524
4B0.05215B0.05215
5C0.03814C0.10512C0.10512
6B0.05215C0.038280.03828
7 
8C0.03814 
9A0.10512 
Sheet1
Cell Formulas
RangeFormula
G3:H6G3=SORT(UNIQUE(FILTER(FILTER(A2:D9,A2:A9<>""),{1,0,0,1})))
I3:I9I3=IF(G3="","",SUMIFS($E$2:$E$9,$A$2:$A$9,G3,$D$2:$D$9,H3))
Dynamic array formulas.



Thanks for looking
 
@*shinigamilight
Spend way too much time than I'd like to accept on this lol.
Ok, sorry about that
Whats in column B - a countif() ???

I understand if you dont want to carry out - after all this is a volunteer site, but thanks for looking and spending time on
as you see , i have updated the thread with an issue with blanks created by formula - i will see how i can add your formulas to the real data
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
@*shinigamilight

Ok, sorry about that
Whats in column B - a countif() ???

I understand if you dont want to carry out - after all this is a volunteer site, but thanks for looking and spending time on
as you see , i have updated the thread with an issue with blanks created by formula - i will see how i can add your formulas to the real data
columnB/Helper column: =COUNTIFS($A$2:A2,A2,$E$2:E2,E2)
 
Upvote 0
@shinigamilight
Thanks for spending the time , but not working , if the data is not in A1

this is a rough mock up - BUT post 9 or on the share

Group-Summary.xlsx
EFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
22lookupWoodother columnsother columnsthicknessMetres
23Wood Type AWood Type A0.105121#NUM!#NUM!
24Wood type B 120#NUM!#NUM!
25Wood Type cWood Type c0.1051#NUM!#NUM!
26Wood Type A 140#NUM!#NUM!
27Wood type BWood type B0.052151#NUM!#NUM!
28Wood Type c 0#NUM!#NUM!
29Wood Type AWood Type A0.038141#NUM!#NUM!
30Wood type BWood type B0.105121#NUM!#NUM!
Sheet2
Cell Formulas
RangeFormula
AG23:AG30AG23=COUNTIFS($I$23:I23,I23,$M$23:M23,M23)
AH23:AH30AH23=INDEX($I$23:$I$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))))
AI23:AI30AI23=INDEX($M$23:$M$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))))
I23:I30I23=IF(A26="none","",IF(A26="1st",$E$23,IF(A26="2nd",$E$24,$E$25)))
 
Upvote 0
@shinigamilight
Thanks for spending the time , but not working , if the data is not in A1

this is a rough mock up - BUT post 9 or on the share

Group-Summary.xlsx
EFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
22lookupWoodother columnsother columnsthicknessMetres
23Wood Type AWood Type A0.105121#NUM!#NUM!
24Wood type B 120#NUM!#NUM!
25Wood Type cWood Type c0.1051#NUM!#NUM!
26Wood Type A 140#NUM!#NUM!
27Wood type BWood type B0.052151#NUM!#NUM!
28Wood Type c 0#NUM!#NUM!
29Wood Type AWood Type A0.038141#NUM!#NUM!
30Wood type BWood type B0.105121#NUM!#NUM!
Sheet2
Cell Formulas
RangeFormula
AG23:AG30AG23=COUNTIFS($I$23:I23,I23,$M$23:M23,M23)
AH23:AH30AH23=INDEX($I$23:$I$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))))
AI23:AI30AI23=INDEX($M$23:$M$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($I$22:$I$29),ROW(I22))))
I23:I30I23=IF(A26="none","",IF(A26="1st",$E$23,IF(A26="2nd",$E$24,$E$25)))
You have to hit ctrl shift enter while entering the formula.
 
Upvote 0
i have version 365 - so does not need {} as 365 versions works out that its an array automatically - but i did try that in case
 
Upvote 0
But why does it work for me?
 

Attachments

  • weccc.PNG
    weccc.PNG
    57 KB · Views: 23
Upvote 0
try starting in row 23
which is where it stops working for me
Works with row 1 OK
Not withing the real data which starts in I23
as shown in cutdown version post 13 above - or real info in post 9
 
Upvote 0
try starting in row 23
which is where it stops working for me
Works with row 1 OK
Not withing the real data which starts in I23
as shown in cutdown version post 13 above - or real info in post 9
Now i get it
 
Last edited:
Upvote 0
Enter this

AH23: =INDEX($I$23:$I$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW(A1))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW(A1))))
AI23: =INDEX($M$23:$M$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW(A1))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW(A1))))
 
Upvote 0
really sorry , i know you are spending more time then needed on this
not working

New Template 2022 - V103-2.xlsx
AFAGAHAIAJ
13
14AH23: =INDEX($I$23:$I$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))))
15AI23: =INDEX($M$23:$M$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))))
16
17
18
19
20
21
22Helper
231Roble Liston0.18
241Roble Liston0.18
252Roble Liston0.18
261Roble Liston0.18
273Roble Liston0.18
282Roble Liston0.18
294Roble Liston0.18
301Roble Liston0.18
31
Test Template
Cell Formulas
RangeFormula
AG23:AG30AG23=COUNTIFS($I$23:I23,I23,$M$23:M23,M23)
AH23:AH30AH23=INDEX($I$23:$I$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))))
AI23:AI30AI23=INDEX($M$23:$M$30,IF(LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))=0,"",LARGE(--($AG$23:$AG$30=1)*ROW($A$1:$A$8),ROW($A$1))))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top