Good Day all and Happy Monday.
I think there is a simple solution to my issue but I am just not getting it right.
The solution I am trying to get is too filter the data in range A20:E20, which works if full range has data in ALL of the cells.
BUT the problem is when there is a blank cell in this range it returns the full database.
I am struggling to find a fix to this issue.
To give some scope on intentions.
I want to filter a large database based on a row range in one sheet when found in all of the columns in my records sheet range YY,AA,AC,AE,AG and return the full row in records sheet.
Original Formula:
=ArrayFormula(FILTER(Records!A:AE,(Records!Y:Y=A1)+(Records!AA:AA=A1)+(Records!AC:AC=A1)+(Records!AE:AE=A1)+(Records!AG:AG=A1)+(Records!Y:Y=B1)+(Records!AA:AA=B1)+(Records!AC:AC=B1)+(Records!AE:AE=B1)+(Records!AG:AG=B1)+(Records!Y:Y=C1)+(Records!AA:AA=C1)+(Records!AC:AC=C1)+(Records!AE:AE=C1)+(Records!AG:AG=C1)+(Records!Y:Y=D1)+(Records!AA:AA=D1)+(Records!AC:AC=D1)+(Records!AE:AE=D1)+(Records!AG:AG=D1)+(Records!Y:Y=E1)+(Records!AA:AA=E1)+(Records!AC:AC=E1)+(Records!AE:AE=E1)+(Records!AG:AG=E1)))
>>> Lastly - as you can see, I have written this out in the most basic form but am sure there is a neater and more compact version to achieve the same result I am working towards, bu using an alternative formula.
I think there is a simple solution to my issue but I am just not getting it right.
The solution I am trying to get is too filter the data in range A20:E20, which works if full range has data in ALL of the cells.
BUT the problem is when there is a blank cell in this range it returns the full database.
I am struggling to find a fix to this issue.
To give some scope on intentions.
I want to filter a large database based on a row range in one sheet when found in all of the columns in my records sheet range YY,AA,AC,AE,AG and return the full row in records sheet.
Original Formula:
=ArrayFormula(FILTER(Records!A:AE,(Records!Y:Y=A1)+(Records!AA:AA=A1)+(Records!AC:AC=A1)+(Records!AE:AE=A1)+(Records!AG:AG=A1)+(Records!Y:Y=B1)+(Records!AA:AA=B1)+(Records!AC:AC=B1)+(Records!AE:AE=B1)+(Records!AG:AG=B1)+(Records!Y:Y=C1)+(Records!AA:AA=C1)+(Records!AC:AC=C1)+(Records!AE:AE=C1)+(Records!AG:AG=C1)+(Records!Y:Y=D1)+(Records!AA:AA=D1)+(Records!AC:AC=D1)+(Records!AE:AE=D1)+(Records!AG:AG=D1)+(Records!Y:Y=E1)+(Records!AA:AA=E1)+(Records!AC:AC=E1)+(Records!AE:AE=E1)+(Records!AG:AG=E1)))
>>> Lastly - as you can see, I have written this out in the most basic form but am sure there is a neater and more compact version to achieve the same result I am working towards, bu using an alternative formula.
| DATA RANGE (records sheet) | |||||||||||||
| Company | Website | Contact | GEO1 | GEO1% | GEO2 | GEO2% | GEO3 | GEO3% | GEO4 | GEO4% | GEO5 | GEO5% | |
| Business Name 1 | www.website1.com | Mr.Name | no data | no data | no data | no data | no data | no data | no data | no data | no data | no data | |
| Business Name 2 | Website2.com | Mrs.Name | United States | 19.04% | Germany | 18.57% | Brazil | 10.69% | France | 6.87% | Others | 4.57% | |
| Business Name 3 | Website3.com | Name | United States | 46.08% | Canada | 36.02% | India | 17.90% | Belarus | 12,6% | Pakistan | 9,21% | |
| Business Name 4 | Website4.com | Mr.Name | Vietnam | 22.95% | United Kingdom | 19.49% | Netherlands | 16.95% | Bulgaria | 15.76% | Latvia | 12.40% | |
| Business Name 5 | Website5.com | Mrs.Name | Poland | 24.92% | United Kingdom | 18.46% | Serbia | 14.12% | Portugal | 9.02% | Others | 2.88% | |
| Business Name 6 | Website6.com | Name | Serbia | 78.79% | Poland | 11.83% | Turkey | 1.71% | India | 1.62% | Others | 1.41% | |
| Business Name 7 | Website7.com | Mr.Name | United Kingdom | 51.92% | Serbia | 16.42% | Canada | 6.76% | United States | 5.38% | Others | 4.77% | |
| Business Name 8 | Website8.com | Mrs.Name | Germany | 13.36% | Brazil | 10.36% | Netherlands | 9.58% | Pakistan | 9.34% | Belarus | 8.44% | |
| Business Name 9 | Website9.com | Name | Sweden | no data | no data | no data | no data | no data | no data | no data | no data | no data | |
| Business Name 10 | Website10.com | Mr.Name | no data | no data | no data | no data | no data | no data | no data | no data | no data | no data | |
| Formula Current (report sheet) | |||||||||||||
| =ArrayFormula(FILTER(Records!A:AH,(Records!Y:Y=A1)+(Records!AA:AA=A1)+(Records!AC:AC=A1)+(Records!AE:AE=A1)+(Records!AG:AG=A1)+(Records!Y:Y=B1)+(Records!AA:AA=B1)+(Records!AC:AC=B1)+(Records!AE:AE=B1)+(Records!AG:AG=B1)+(Records!Y:Y=C1)+(Records!AA:AA=C1)+(Records!AC:AC=C1)+(Records!AE:AE=C1)+(Records!AG:AG=C1)+(Records!Y:Y=D1)+(Records!AA:AA=D1)+(Records!AC:AC=D1)+(Records!AE:AE=D1)+(Records!AG:AG=D1)+(Records!Y:Y=E1)+(Records!AA:AA=E1)+(Records!AC:AC=E1)+(Records!AE:AE=E1)+(Records!AG:AG=E1))) | |||||||||||||
| Filter range A20:E20 (A20 will always have data in it but B to E may or may not all have data in them) | |||||||||||||
| brazil | poland | canada | portugal | Serbia | Option 1 | ||||||||
| brazil | poland | canada | portugal | Option 2 | |||||||||
| brazil | poland | canada | Option 3 | ||||||||||
| brazil | poland | Option 4 | |||||||||||
| brazil | Option 5 | ||||||||||||
