CODEWITHSUNDEEP
excelexcel-formula
TK4795
TK4795

Reputation: 109

Excel Function - Filter data on specific worksheet using dropdown to select worksheet

I am trying to filter the data from specific worksheet when I select from dropdown menu which will be specific month from January to December.

When select specific month e.g. April:

enter image description here

Then print the data below which then sorted by Column E

enter image description here

The formula I am currently using is:

=LET(baseData,VSTACK(April:May!A2:D5),
baseCrew,VSTACK(April:May!Q2:Z5),
fBaseData,FILTER(baseData,CHOOSECOLS(baseData,1)<>""),
fBaseCrew,FILTER(baseCrew,CHOOSECOLS(baseData,1)<>""),
result1,HSTACK(fBaseData,BYROW(fBaseCrew,LAMBDA(r,FILTER(April!Q1:Z1,r=$A$1,"no result")))),
FILTER(result1,CHOOSECOLS(result1,5)<>"no result"))

This print the data from all the worksheets from January - December when selecting Crew Initial:

enter image description here

Is there a way to improve this to achieve the result above?

Thank you in advance.

List Sheet

INITITAL MONTH
AG JANUARY
GH FEBRUARY
ML MARCH
SM APRIL
RP MAY
JUNE
JULY
AUGUST
SEPTEMBER
OCTOBER
NOVEMBER
DECEMBER

Data Sheet (April Month - Each month have its own sheet)

DAY DATE MONTH VENUE CODE INVOICE ORDER NO. VALUE PROFIT EXTRA INITIAL NO. TYPE VEHICLE CREW REQ. INSTALLATION TIME CREW 1 CREW 2 CREW 3 CREW 4 CREW 5 CREW 6 CREW 7 CREW 8 CREW 9 CREW 10 INFO
Thursday 6th April London LND0001 5000 11001 2000 3000 0 OG 1 LOW VAN 3 1 ML SM AG
Friday 7th April Birmingham BRUM0001 2450 11002 4450 2000 0 OG 2 LOW VAN 4 2 SM ML GH RP
Monday 20th April London LDN0001 4000 11003 1000 3000 0 OG 3 LOW VAN 3 1 GH SM ML

Report Sheet, when selecting month from the dropdown (A1). It would then print the report from April sheet.

April
DAY DATE MONTH VENUE CREW CREW NO
Thursday 6th April London AG CREW 3
Friday 7th April Birmingham GH CREW 3
Monday 20th April London GH CREW 1
Thursday 6th April London ML CREW 1
Friday 7th April Birmingham ML CREW 2
Monday 20th April London ML CREW 3
Thursday 6th April London SM CREW 2
Friday 7th April Birmingham SM CREW 1
Monday 20th April London SM CREW 2
Friday 7th April Birmingham RP CREW 4

Upvotes: 1

Views: 147

Answers (2)

P.b
P.b

Reputation: 11468

If I understood things correctly, you wanted to be able to select a value varying from Januari to December as well as select a crew member by it's initials from two separate drop downs.

As a result from these selections you want a report which dynamically only lists the data from selected month where the crew member's initials are mentioned in any of the crew columns. You want to return the first 4 columns values and the header value of the column where the crew member initial is found.

Then how about using this formula:

=LET(y, VSTACK(January:December!A2:AA99),
     m, FILTER(y,INDEX(y,,3)=C2),
     s, SEQUENCE(,10,17),
     e, FILTER(m,BYROW(CHOOSECOLS(m,s),LAMBDA(r,OR(C4=r)))),
IF(OR(C2="",C4=""),
   "",
   IFERROR( HSTACK(TAKE(e,,4),
                   BYROW(CHOOSECOLS(e,s),LAMBDA(r,CONCAT(REPT("Crew "&s-16,r=C4))))),
            "No results")))

Where C2 is the month selection and C4 is the initial selection. enter image description here

First all data of tabs January through December are stacked and named y. Secondly y is filtered on the third column to equal the month selection C2 and named m. A sequence of 17 to 27 (column numbers of the different crew #'s) is created as s. e filters the month filter m where row wise the selected intial C4 is found.

Finally (if both are selected) we stack the first 4 columns of e and (simulate) the header value where the initials where found in each row.

(Link to sample of file: https://1drv.ms/x/s!AhxknrNHLDPCgYgS8dIx4V1ps60INA?e=lEM1RZ I also made the initials drop down selection dependent on the month selection in this file)

Upvotes: 0

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

If I have understood correctly, then the following formula should work to accomplish the desired output.

enter image description here

=LET(
     _baseData, VSTACK(April:May!A2:D5),
     _baseCrew, VSTACK(April:May!Q2:Z5),
     _fBaseData, FILTER(_baseData,TAKE(_baseData,,1)<>""),
     _fBaseCrew, FILTER(_baseCrew,TAKE(_baseData,,1)<>""),
     _fBaseCrewN, HSTACK(_fBaseData,BYROW(_fBaseCrew,LAMBDA(r,FILTER(April!Q1:Z1,r=$A$1,"no result")))),
     _tempOutput, HSTACK(WRAPCOLS(TOCOL(DROP(REDUCE("",SEQUENCE(COLUMNS(_fBaseCrewN)),
                  LAMBDA(x,y,VSTACK(x,IF(_fBaseCrew<>"",INDEX(_fBaseCrewN,,y),NA())))),1),2),
                  SUM(N(_fBaseCrew<>""))),TOCOL(IFS(_fBaseCrew<>"",_fBaseCrew),2)),
     _output, SORT(FILTER(_tempOutput,(INDEX(_tempOutput,,3)=B1)*(TAKE(_tempOutput,,-1)<>"no result")),6),
     VSTACK({"DAY","DATE","MONTH","VENUE","CREW","CREW NO"},CHOOSECOLS(_output,1,2,3,4,6,5)))

Note: The changes you may need for the initials & month cell references as well need to change ranges accordingly as per your data.

As mentioned by OP in comments:

I have tested it and it work but a few issue when selecting initial, the "CREW NO." is wrong for other initial. Really sorry, is it possible to remove the initial selection and only use month selection? As well as the removing the header?

enter image description here

=LET(
     _BaseData, VSTACK(April:May!A2:D5),
     _BaseCrew, VSTACK(April:May!Q2:Z5),
     _BaseDataCrewN, WRAPCOLS(TOCOL(DROP(REDUCE("",SEQUENCE(COLUMNS(_BaseCrew)),LAMBDA(x,y,
       VSTACK(x,IF(_BaseCrew<>"",INDEX(_BaseData,,y),NA())))),1),2),SUM(N(_BaseCrew<>""))),
     _CrewNoCrew, TOCOL(IFS(_BaseCrew<>"",HSTACK(_BaseCrew&"|"&April!Q1:Z1)),2),
     _TempOutput, HSTACK(_BaseDataCrewN, TEXTBEFORE(_CrewNoCrew,"|"),TEXTAFTER(_CrewNoCrew,"|")),
     IFERROR(SORT(FILTER(_TempOutput,(INDEX(_TempOutput,,3)=B1)),5),"Select Month Name"))

Upvotes: 1

Related Questions