Complex Search Formula

[Nevec90]

Hi Community,

I am trying to enhance a formula I created, but I am stuck now.

File: E&V Florida Listing Log Template MASTER_Prototype_1.0.xlsx

Situation:
There is an Input sheet "SellerInput"

Pic1.jpg

and there is an Output Sheet called "Seller Side"
pic2tiny.jpg


My formula is searching in SellerInput all rows with a specific "Office" and a specific "Range" and pastes the content of the row in the Seller Side sheet.

Formula:
=IFERROR(INDEX('Seller Input'!B$2:B$1000,AGGREGATE(15,6,ROW('Seller Input'!B$2:B$1000)/(('Seller Input'!$A$2:$A$1000="Anna Maria Island")*('Seller Input'!$K$2:$K$1000="30 Days")),ROW()-5)-1,1),""))

That works very well. Now the formular has to search for another criterium on top: "Seller/Buyer". It should search first for all rows with the criterias above plus search for "s" as seller and list all these first, after it is finished it should run through the same procedure, but with "b" instead and list them under the found "s" rows.

My try:
=IFERROR(INDEX('Seller Input'!B$2:B$1000,AGGREGATE(15,6,ROW('Seller Input'!B$2:B$1000)/(('Seller Input'!$A$2:$A$1000="Anna Maria Island")*('Seller Input'!$K$2:$K$1000="30 Days")*('Seller Input'!$B$2:$B$1000="b")),ROW()-5)-1,1),IFERROR(INDEX('Seller Input'!B$2:B$1000,AGGREGATE(15,6,ROW('Seller Input'!B$2:B$1000)/('Seller Input'!$B$2:$B$1000="s"),ROW()-9)-1,1),""))

It works, but I have to manually change the number in bolt, depending on how many results were found...What is wrong here? Hope somebody can help me.


PS:
Maybe that would be all easier with VBA or other stuff, but because I am absolutely not familiar with these things I had to do it this way.

[azumi]

Dont' put image, more clear about what you need if you upload your sample workbook, click :Go Advanced" button and find paperclip button to attach the file.

regards

[Nevec90]

Hi,

Here the uploaded workbook. I also attached it in the beginning of the original post.

[azumi]

Pls find file attach, hope this works......

regards

[Nevec90]

Wow, that was fast. Works perfectly, thank you a lot!

I will work on an enhanced version of this excel these days. Maybe I will have another question then. Thank you again!

[Nevec90]

Hi,

I adjusted the workbook now a bit. Locations are now single tabs to get a better overview. I applied the formula but it creates now empty rows again. Can you give me some comments on the formula (Would like to understand it better, now it is too complicated for me...). Is it also possible to optimize it? (Calculation takes now very long). Thank you in advance!

Excel-File (Was too big for the forum upload...)

[azumi]

Fix it

https://www.dropbox.com/s/7uazrjtzlp..._4.0.xlsx?dl=0