VBA/Formula needed for a complicated filter/data pull - Excel 365

[justme101]

Hello All,

I have one file where different products are listed in a column and along those rows, for each product, the type of pricing being offered is given for all the weeks of the year. Now, the pricing names can be varied, like discounted_1, discounted_2 etc. depending on what type of discount is being given. But, the thing which remains constant is that if there is no discount for a particular week, the words "Set price" appear for that week and product (it can be set price_1, or set price_2, but the words "set price" will be in that cell). A sample of the file is shown below (sorry my xls2bb is not working properly, so can't upload the file here).

Now, the ask here is to get data for all these products (all unique line items) into a different sheet, with only the weeks where ANY kind of discount is given and the start date and end date as well from the top rows. It should look something like this.

I have tried all my *limited* knowledge of formulas and VBA to accomplish this, but I can't come up with something to solve it. If you can help me out to accomplish this, it would not be less than a miracle for me (It's that time of the year as well). Any formula, or VBA will work with me. Thank you, in anticipation.

 

[justme101]

How about

=LET(da,B4:D900,db,E1:P2,dc,E4:P900,c,COLUMNS(dc),s,SEQUENCE(c*ROWS(dc),,0),xa,INDEX(da,INT(s/c)+1,{1,2,3}),xb,INDEX(dc,INT(s/c)+1,MOD(s,c)+1),xx,CHOOSE({1,2,3,4,5,6},xa,xa,xa,xb,INDEX(db,1,MOD(s,c)+1),INDEX(db,2,MOD(s,c)+1)),FILTER(xx,(ISERR(SEARCH("set price",xb)))*(xb<>"")))

Fluff for the win!! Really appreciate it! Cheers.

 

[Fluff]

You're welcome & thanks for the feedback.