Index Match with sort capability?

[Gochix2018]

Hi all,

I came to a dead end and can't figure this out.
Explaining the issue:

I have 3 sheets on my Excel file -> Sheet 1: main dashboard where all the results will appear, Sheet 2: Info sheet where is listed all possible product SKU's with descriptions and types + in what kind of order the SKU should come ( from 1 to 2), Sheet 3: user paste inside the data.

So my goal is when the user paste in the data in sheet 3 then in sheet 1 automatically filters out the data and sorts it in the order specifically so it is in order from 1 to 2. There could be multiple type 1 SKU's but never the same so first I would need the list to contain all order type 1 sku's and then all order type 2 sku's.

Untitled.png

I've attached the sample file.
If anyone has an idea how to do this I would really appreciate it.

Thanks

[AliGW]

Do you still have Excel 2016?

[Glenn Kennedy]

Unless you have upgraded... this is quite complicated. No surprises that you came to a dead end.

If you have upgraded to O365 - say so and ignore this. If not, play with it and see if it works as planned. I'll then explain HOW it works. Don't copy it into your real sheet YET. It uses 2 named Ranges (CTRL-F3 to view/edit) to deliver a "virtual interim table".

[Glenn Kennedy]

If you DO have a recent version of O365, you can do the lot in a single cell, using this:

Please Login or Register  to view this content.

Delete ALL expected results, first. the only bits you (should) need to adjust are the bits in red (above).

[Gochix2018]

Unless you have upgraded... this is quite complicated. No surprises that you came to a dead end.

If you have upgraded to O365 - say so and ignore this. If not, play with it and see if it works as planned. I'll then explain HOW it works. Don't copy it into your real sheet YET. It uses 2 named Ranges (CTRL-F3 to view/edit) to deliver a "virtual interim table".

Thank you, Glenn,

This worked perfectly.

[AliGW]

In that case, you need to update your forum profile to O365, please.

[Glenn Kennedy]

I'm a bit confused was it the O365 version that worked, or the earlier one (which may need further explanation).

If all's good...

You're welcome. If not, ask.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Gochix2018]

I'm a bit confused was it the O365 version that worked, or the earlier one (which may need further explanation).

If all's good...

You're welcome. If not, ask.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Glenn,

The first solution worked well - I have an O365 version but the second solution is way complex for me to understand the LAMDA formulas etc. First one is more clear and I can edit it if I really would need to.

A question - would it be possible to implement a nested SORT ( or any other function ) that would still make a list in "order" but would look for the TYPE from another column in INFO sheet that contains types : from S7 till S1 and S7 always on top etc. If that makes sense?

Attachment 829079

I've attached the example file.

[Glenn Kennedy]

SORT is available in O365. Modifying the existing O365 formula would be easy enough.

But... you want a formula that uses some O365 functions, but not all the ones that make the job easy.

You're asking me to fight with one hand tied behind my back!!

What functions do you not want me to use?

[Gochix2018]

SORT is available in O365. Modifying the existing O365 formula would be easy enough.

But... you want a formula that uses some O365 functions, but not all the ones that make the job easy.

You're asking me to fight with one hand tied behind my back!!

What functions do you not want me to use?

If you can achieve the end result with modifying the existing O365 formula then please go ahead. I will try to understand it and for sure will learn something new.

[Glenn Kennedy]

Here's the optimim solution...

Please Login or Register  to view this content.

[Glenn Kennedy]

If you want to see what it is doing at each stage... use this slight variant:

Please Login or Register  to view this content.

Replace the final F (in red) with ANY of the earlier abbreviations (Inf and Pst are obvious...) so start with A and see what it does...

REDUCE is a hard one to try to explain... but I can try, if/when you get stuck

[Glenn Kennedy]

And... if it makes feel any better... I hadn't a clue what REDUCE did until about 6 months ago... when I determined to pick it up from others on this site who can make it sit up and whistle...

I stand on the shoulders of giants...

[Gochix2018]

If you want to see what it is doing at each stage... use this slight variant:

Please Login or Register  to view this content.

Replace the final F (in red) with ANY of the earlier abbreviations (Inf and Pst are obvious...) so start with A and see what it does...

REDUCE is a hard one to try to explain... but I can try, if/when you get stuck

Thank you. This simple explanation was all I needed to understand how it works. It reminds me a bit of javascript to be honest with Let function, didn't know we have on in excel. Thanks again.

[Glenn Kennedy]

Well there you are now... I know nothing about Javascript. 1-0 to you!!

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.