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

LuffyGozaru

New Member
Joined
Oct 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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):
Excel Formula:
=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.
 

Attachments

  • Formules Sheet.PNG
    Formules Sheet.PNG
    139.7 KB · Views: 9
  • Ruimteboek Sheet.PNG
    Ruimteboek Sheet.PNG
    29.8 KB · Views: 9

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
Try this one which works on a similar principle but with newer functions that will make it a bit more efficient.
Excel Formula:
=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

New Member
Joined
Oct 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Try this one which works on a similar principle but with newer functions that will make it a bit more efficient.
Excel Formula:
=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
 

Attachments

  • Formules Sheet 2.PNG
    Formules Sheet 2.PNG
    90.1 KB · Views: 4
  • Ruimteboek Sheet 2.PNG
    Ruimteboek Sheet 2.PNG
    30.5 KB · Views: 4

LuffyGozaru

New Member
Joined
Oct 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Formules Sheet 2 helper column.PNG
    Formules Sheet 2 helper column.PNG
    94.9 KB · Views: 5

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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.
Excel 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.
 

Forum statistics

Threads
1,148,193
Messages
5,745,272
Members
423,941
Latest member
CluelessAboutExcel

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
Top