• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I do the opposite of FILTER formula in Cell L2? [SOLVED]

arthurz11​

Did You forget Forum Rules?
Please, reread those again.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
 
vletm said:

arthurz11​

Did You forget Forum Rules?
Please, reread those again.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Click to expand...
I apologized. I didn't read Forum Rules #7. I had posted on Excel Forum, ExcelGuru, and Chandoo.org. So do I remove two and stay with one?
 

arthurz11​

Maybe You wrote that You did not read Forum Rules before the 1st posting.
Did You read those based my posting?
... If You read, then You have read next:
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
There are task, which You should do have done
# please put that in your post. ~ give a link to that posting ... which You skipped
As well as the 3rd task, which You can read above too.
You can continue with both.

Here, one more - good to read link about cross-posting:

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →
excelguru.ca excelguru.ca
 

arthurz11​

As written: You can continue with both ... with all cross-postings after You've posting links here.
... and after You've gotten a solution, do as written in Forum Rules.

arthurz11​

It's more useful to post links straight to those cross-posted pages
that others could see, what others has offered? ... that others could offer something else.
 
Last edited:
The links are:

Welcome to Chandoo.org - Learn Excel, Charting Online

Learn Microsoft Excel and Charting online. Become awesome in Excel & your work. Dicuss your excel and spreadsheet problems in forums. Learn excel, charting, formulas, functions, dashboards, pivot tables & VBA using tutorials, downloadable example and online classes - by Chandoo.org
chandoo.org chandoo.org

Excel Help Forum

Excel Help and Support from Excel Experts( MVPs). Excel2003,Excel 2007, Excel 2010 What's New in Excel and Excel Tips and Tutorials.
www.excelforum.com
excelguru.ca

Home

Passionate Instruction, Practical Skills Unlocking Your Team's Potential via Industry Leading Training Training Monkey Tools Add-in At Excelguru, our goal is to help expand your knowledge of Excel and Power BI, help you find solutions, and help you to be … Continue reading →
excelguru.ca excelguru.ca

I am currently working with ExcelGuru.
 
Have you made progress on ExcelGuru?
I have played some FILTER tricks to get a list of duplicates.
Code: 
Worksheet function
= LISTDUPSλ(GridPointTbl[@])

where the Lambda function is defined by
LISTDUPSλ = LAMBDA(
    dataRow,
    LET(
        trimmed,   TOROW(dataRow, 1),
        wrapped,   WRAPCOLS(trimmed, 2),
        distinct,  UNIQUE(wrapped, 1, ),
        unique,    UNIQUE(wrapped, 1, 1), // otherwise all duplicates
        combined,  IFERROR(HSTACK(distinct, unique), distinct),
        duplicate, UNIQUE(combined, 1, 1), // otherwise no duplicates
        return,    IFERROR(duplicate, ""),
        TOROW(return,,1)
    )
);

WRAPCOLS is used to pair up the x and y terms to make the use of UNIQUE possible. The variable 'distinct' holds a list of distinct (x, y) pairs whilst 'unique' is the list of pairs that are not duplicated. Stacking the two lists duplicates those pairs that were unique in the original, hence a further use of UNIQUE returns the original duplicates. Where things start to get painful is the fact that the required result is an array of arrays which, currently, is out of scope for the built-in helper functions.

Using a helper function BYROWλ that am developing gives
Code: 
= BYROWλ(GridPointTbl, LISTDUPSλ)
1729107190447.png
 

Attachments

  • Filter x,y duplicates A.xlsx
    29.4 KB · Views: 9
Would you please be so kind as to revise any data on your range A2:J5? As you will see, it displays #NAME? as a result. Please try it. So the formula does not recalculate accordingly. It looks good as it is BUT as soon as you change any cell in the data range it gives an error.
 
Copied from ExcelGuru:

BYROW and WRAPROWS are in Excel 2024 and 365, but NOT in 2021, hence the #NAME? error.
 
Thanks @AliGW. I had got as for as checking that the file still works when downloaded but had not identified which function or defined name was causing the problem. But then, I wasn't tracking the Excel Guru discussion, so did not know the target was Excel 2021. It is strange to see the same familiar names as contributors on the various Excel fora.
 
Peter Bartholomew said:
Have you made progress on ExcelGuru?
I have played some FILTER tricks to get a list of duplicates.
Code: 
Worksheet function
= LISTDUPSλ(GridPointTbl[@])

where the Lambda function is defined by
LISTDUPSλ = LAMBDA(
    dataRow,
    LET(
        trimmed,   TOROW(dataRow, 1),
        wrapped,   WRAPCOLS(trimmed, 2),
        distinct,  UNIQUE(wrapped, 1, ),
        unique,    UNIQUE(wrapped, 1, 1), // otherwise all duplicates
        combined,  IFERROR(HSTACK(distinct, unique), distinct),
        duplicate, UNIQUE(combined, 1, 1), // otherwise no duplicates
        return,    IFERROR(duplicate, ""),
        TOROW(return,,1)
    )
);

WRAPCOLS is used to pair up the x and y terms to make the use of UNIQUE possible. The variable 'distinct' holds a list of distinct (x, y) pairs whilst 'unique' is the list of pairs that are not duplicated. Stacking the two lists duplicates those pairs that were unique in the original, hence a further use of UNIQUE returns the original duplicates. Where things start to get painful is the fact that the required result is an array of arrays which, currently, is out of scope for the built-in helper functions.

Using a helper function BYROWλ that am developing gives
Code: 
= BYROWλ(GridPointTbl, LISTDUPSλ)
View attachment 88779
Click to expand...
Man oh man. I haven't made any progress from Excel Guru or the other forums like Mr. Excel, ExcelGuru and even three other Excel paying websites for months. Very frustrating. An administrator mentioned that BYROW and WRAPROWS are in Excel 2024 and 365, but NOT in 2021, hence the #NAME? error. In that case, would someone please be so kind as to help write the Excel FILTER formula that works only for Version 2021 and NOT on versions 365 nor 2024? On the three different paying websites, I was scammed by one but the other two, could not do it and my money was refunded. I even had an excel consultant from Mr. Excel for consultanting services (so that's actually 4 paying Excel help websites) but he decided not to ask for any payment after it didn't work. I been trying for months for someone to help. One of the Mr.Excel that helped me with a FILTER formula worked great but I wanted him to do the opposite. No reply from him for a very good while.
 
It is possible using INDEX/SEQUENCE (or perhaps a completely different approach from mine) but I don't like the code or particularly wish to be associated with it :eek:! I would prefer someone else did it.
1732226713869.png
I worked through an approach this far (multiple duplicate pairs are still stacked) but there is no telling how robust the solution is. It would be better if you have LAMBDA but I think 2021 was frozen between LET and LAMBDA.
 
2021 doesn't have LAMBDA or BYROW - they are in 2024 and 365.

I worked on this for a couple of hours on and off yesterday and couldn't find an automated way without LAMBDA.

There are solutions, but they'll be very clunky. Looking at a VBA option might be the optimal route here (but I can't help with that).
 
Last edited:
Building on what @Peter Bartholomew has already done, this in L3 copied down:

Code: 
=LET(n, QUOTIENT(COUNTA(GridPointTbl[@]), 2),
k, SEQUENCE(n, 2),
list, INDEX(GridPointTbl[@], k),
distinct, UNIQUE(list),
unique, UNIQUE(list, , 1),
na, ROWS(distinct),
nb, ROWS(unique),
r, {1,1} * SEQUENCE(na + nb),
c, SIGN(r) * {1,2},
combined, IF(r <= na, INDEX(distinct, r, c), INDEX(unique, r - na, c)),
duplicate, IF(ISERROR(nb),distinct, UNIQUE(combined, , 1)),
x, IFERROR(duplicate, ""),
IFERROR(CHOOSE({1,2,3,4,5,6,7,8,9,10},INDEX(x,1,1),INDEX(x,1,2),INDEX(x,2,1),INDEX(x,2,2),INDEX(x,3,1),INDEX(x,3,2),INDEX(x,4,1),INDEX(x,4,2),INDEX(x,5,1),INDEX(x,5,2)),""))

Boy, it's clunky, but it seems to work!
 
Thank you sooo much for your effort. However, I do not know VBA codes on Excel. I had cross posted and I was told to show the links from the previous threads. So it is the utlmost importance that I utilize a FILTER formula. So here are the following links to other threads...

Excelguru.ca:

How do I write the FILTER formula to do the opposite in Cell L2?

forums.excelguru.ca forums.excelguru.ca

Chandoo.org

MrExcel.com:
www.mrexcel.com

Please help with FILTER Formula.

I’ve been working on this for months and yet not one person from the following paying and help Excel forums have not come up with a solution. I was even scammed by two paying help websites. The following that had helped: (1) Mrexcel.com, (2) Excel Help Forum, (3) Paperial.com, (4) Chandoo.org...
www.mrexcel.com www.mrexcel.com

Msofficeforums.com:

Please help with FILTER formula

Please help with FILTER formula Excel
www.msofficeforums.com

Paid website my.paperial.com (Unable to write a solution):
https://my.paperial.com/customer/orders

Paid website student.tutorbin.com (Unable to write a solution):

Paid website Nerdifyit.com (Unable to write a solution):
Nerdify | #1 Learning Help for College Students

Pay MrExcel.com consultant services (Brian Field email a FILTER formula and he told me that I didn’t have to pay. But I email back to show the errors on the formula but he never reply by email:
www.mrexcel.com

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.
www.mrexcel.com www.mrexcel.com
 

arthurz11​

With my 'without formula' Your need to know that range where is Your data and press that button.
If You cannot do those then ... okay.
Do that mean, You can only accept FILTER-formula?
or do You would like to get results?

Cross-posting is okay, if user follows basic rules.
You start to follow rules after others has reported about cross-posting.
You should read next:

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →
excelguru.ca excelguru.ca
 
vletm said:

arthurz11​

With my 'without formula' Your need to know that range where is Your data and press that button.
If You cannot do those then ... okay.
Do that mean, You can only accept FILTER-formula?
or do You would like to get results?

Cross-posting is okay, if user follows basic rules.
You start to follow rules after others has reported about cross-posting.
You should read next:

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →
excelguru.ca excelguru.ca
Click to expand...
Sorry for the very late reply. I needed to back off from Excel formulas. I was completely burned out. I would very much just like to utilize the FILTER formula because I can understand it better than not using a FILTER formula at all. I have to use it on other parts of the worksheet that uses 200 columns and 10 rows. And that's just one table of an array out of 20 to 30 arrays. So that's why I just sent a short version ( Oct 14, 2024 - #1 post of this forum) so it would be easier to do but found out that the formulas that excel helpers wrote didn't work accordingly to plan.
 
Try either of the following in cell L2:

Code: 
=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    v, INDEX(f,,s),
    x, INDEX(v,,1),
    y, INDEX(v,,2),
    u, UNIQUE(FILTER(v,MMULT((x=TRANSPOSE(x))*(y=TRANSPOSE(y)),IF({1},1,x))>1,"")),
    n, SEQUENCE(COUNTA(u),,0),
    TRANSPOSE(INDEX(u,INT(n/2)+1,MOD(n,2)+1))
)

Or:

Code: 
=LET(
    f, FILTER(A2:J2,A2:J2<>""),
    s, SEQUENCE(COLUMNS(f)/2,2),
    v, INDEX(f,,s),
    a, UNIQUE(v),
    b, UNIQUE(v,,1),
    u, FILTER(a,ISERROR(XMATCH(INDEX(a,,1)&"|"&INDEX(a,,2),INDEX(b,,1)&"|"&INDEX(b,,2))),""),
    n, SEQUENCE(COUNTA(u),,0),
    TRANSPOSE(INDEX(u,INT(n/2)+1,MOD(n,2)+1))
)

See attached...
 

Attachments

  • Filter x,y duplicates B.xlsx
    14.9 KB · Views: 0
Thank you so much. However, I now have the formula to display both duplicates AND non-duplicates. And I have provided the formula to display duplicates only. I used both formulas which you sent/created and I test it and it works great and both formulas worked the same to display duplicates only. But I do not have the formula to display non-duplicates only. Would you please be so kind as to show me how I can revised the formula to do that? Thank you!
 
You must log in or register to reply here.
Back
Top