Hi Community,
I am trying to enhance a formula I created, but I am stuck now.
Situation:
There is an Input sheet "SellerInput"
and there is an Output Sheet called "Seller Side"
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.
I am trying to enhance a formula I created, but I am stuck now.
Situation:
There is an Input sheet "SellerInput"

and there is an Output Sheet called "Seller Side"

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.