Index/ Match/ Duplicate criteria and Results/ Non number value's

[LuffyGozaru]

Good afternoon,

This is my first post on this forum so i'm curious if you guys can help me!
After searching alot on the internet and multiple forums i seem to get close to the answer i need but it just doesnt seem to fit to my situation.

I work at an Architecture Office where we want to substract quantities out of a model. Each of these quantities and values are supposed to be put on the right place in the 'Ruimteboek' or 'Zonebook' sheets.

In the added 'Formules sheet' image you see all the values i can substract from the Model. In each row you see a different wall(finish) which is located in a certain Zone.
The main problem is that i want fill in the Codes that are located in the different Zones. So in this example i want a formula in Cell C11 of the 'Ruimteboek Sheet' that returns me the different Codes (Column D of 'Formules sheet') that are located in the linked Zone (Column B of 'Formules sheet').
So for Zone No. 0.52 i want to fill in vertically 1a and 4a.

I've found some formulas that get pretty close to what i want, for example (i've filled in everything as i would in my excel sheet):

=IF(ISERROR(INDEX(Formules!B4:D10000,SMALL(IF(Formules!B4:B10000=Ruimteboek!C5,ROW(Formules!B4:B10000)),ROW(1:1)),3)),"",INDEX(Formules!B4:D10000,SMALL(IF(Formules!B4:B10000=Ruimteboek!C5,ROW(Formules!B4:B10000)),ROW(1:1)),3))

Source: excel index match return unique value (no duplication) top 3 list

This is just an example and i've seen different methods that kind of do the same thing, but in the end i always get the 1st answer correct for every zone but the the second value is:
- either a duplicate (so it doesnt skip the wallfinishes with the same code in the same zone)
- or it results in an Error or 0.

I would be very delighted if i could get some help, and if possible a small explanation of the formula (if there is one) since i'd like to understand the solution ^^.

Also if this works i'd also like to calculate the sum of the area values in Column H for each wallfinish code in each zone. If the excel guru's of Mrexcel have a solution for this too i am more then pleased^^

Thank you in advance.

 

[jasonb75]

Try this one which works on a similar principle but with newer functions that will make it a bit more efficient.

=IFERROR(INDEX(Formules!$D:$D,AGGREGATE(15,6,ROW(Formules!B4:B10000)/(Formules!$B$4:$B$10000=$C$5)/ISERROR(MATCH(Formules!$B$4:$B$10000,$C$10:$C10,0)),1)),"")

It would be much easier with a UNIQUE(FILTER(...)) formula, but that is not available in excel 2016.

 

[LuffyGozaru]

Try this one which works on a similar principle but with newer functions that will make it a bit more efficient.

=IFERROR(INDEX(Formules!$D:$D,AGGREGATE(15,6,ROW(Formules!B4:B10000)/(Formules!$B$4:$B$10000=$C$5)/ISERROR(MATCH(Formules!$B$4:$B$10000,$C$10:$C10,0)),1)),"")

It would be much easier with a UNIQUE(FILTER(...)) formula, but that is not available in excel 2016.

First off thanks for your answer!

This is one that gets close to what i want, but not fully. Then again, i dont know if my format is optimal for such a formula.

I've picked an example in which i used your formula, and the answer i would like to see is 1b and 1c below eachother.
I think that since the Zone (0.03) and Code (1b) both appear multiple times, it doesnt work.
So right now it first shows all the 1b's and then in the end you see 1c appear.

Also, on the 'formules' sheet when i for instance finish a 'zone' my plan was to copy the content of that page downwards to the next page.
Zone 0.02 would be on page 1, and Zone 0.03 on page 2 and so on.
But if i do this, the "Row" part of the function stretches from page 1 all the way down to page 2 and further.

Is there maybe an adjustment to this formula that might work?
Or is a different working format be preferable?

Thanks in advance!

Mike

 

[LuffyGozaru]

First off thanks for your answer!

This is one that gets close to what i want, but not fully. Then again, i dont know if my format is optimal for such a formula.

I've picked an example in which i used your formula, and the answer i would like to see is 1b and 1c below eachother.
I think that since the Zone (0.03) and Code (1b) both appear multiple times, it doesnt work.
So right now it first shows all the 1b's and then in the end you see 1c appear.

Also, on the 'formules' sheet when i for instance finish a 'zone' my plan was to copy the content of that page downwards to the next page.
Zone 0.02 would be on page 1, and Zone 0.03 on page 2 and so on.
But if i do this, the "Row" part of the function stretches from page 1 all the way down to page 2 and further.

Is there maybe an adjustment to this formula that might work?
Or is a different working format be preferable?

Thanks in advance!

Mike

I've added a helper column in the "Formules" tab which generated a different number for every new combination of "zone + code"?

Maybe this helps in getting a new formula.

 

[jasonb75]

A slight edit to the formula which may fox the problems, I had missed a few $ symbols in the ROW() part which would have been messing up the alignment of various parts of the formula.

=IFERROR(INDEX(Formules!$D:$D,AGGREGATE(15,6,ROW(Formules!$B$4:$B$10000)/(Formules!$B$4:$B$10000=$C$5)/ISERROR(MATCH(Formules!$B$4:$B$10000,$C$10:$C10,0)),1)),"")

It is possible that some additional tweaks may be needed if I missed anything in the description.