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.

 

[Fluff]

How about

=LET(a,LEFT(C4:N9,9)="Set price",HSTACK(TOCOL(IF(a,x,B4:B9),3),TOCOL(IF(a,x,C4:N9),3),TOCOL(IF(a,x,C1:N1),3),TOCOL(IF(a,x,C2:N2),3)))

 

[justme101]

How about

=LET(a,LEFT(C4:N9,9)="Set price",HSTACK(TOCOL(IF(a,x,B4:B9),3),TOCOL(IF(a,x,C4:N9),3),TOCOL(IF(a,x,C1:N1),3),TOCOL(IF(a,x,C2:N2),3)))

Hi Fluff,

I tried this, but it gave me a #NAME? error. I put the formula at the end of the data in the 1st screenshot, in cell O4.

 

[Fluff]

Ok, you probably don't have some of those functions yet.
How about

=LET(da,B4:B9,db,C1:N2,dc,C4:N9,c,COLUMNS(dc),s,SEQUENCE(c*ROWS(dc),,0),x,INDEX(dc,INT(s/c)+1,MOD(s,c)+1),xx,CHOOSE({1,2,3,4},INDEX(da,INT(s/c)+1),x,INDEX(db,1,MOD(s,c)+1),INDEX(db,2,MOD(s,c)+1)),FILTER(xx,LEFT(x,9)<>"set price"))

 

[justme101]

Ok, you probably don't have some of those functions yet.
How about

=LET(da,B4:B9,db,C1:N2,dc,C4:N9,c,COLUMNS(dc),s,SEQUENCE(c*ROWS(dc),,0),x,INDEX(dc,INT(s/c)+1,MOD(s,c)+1),xx,CHOOSE({1,2,3,4},INDEX(da,INT(s/c)+1),x,INDEX(db,1,MOD(s,c)+1),INDEX(db,2,MOD(s,c)+1)),FILTER(xx,LEFT(x,9)<>"set price"))

This works great, but probably just needs a minor modification. The words "set price" might not be always just that, it may be like for e.g. "set price 1", "2_set price", "set price $2" etc. Basically the cell will have the words "set price" but with numbers or prices before or after them.

 

[Fluff]

In that case try

=LET(da,B4:B9,db,C1:N2,dc,C4:N9,c,COLUMNS(dc),s,SEQUENCE(c*ROWS(dc),,0),x,INDEX(dc,INT(s/c)+1,MOD(s,c)+1),xx,CHOOSE({1,2,3,4},INDEX(da,INT(s/c)+1),x,INDEX(db,1,MOD(s,c)+1),INDEX(db,2,MOD(s,c)+1)),FILTER(xx,ISERR(SEARCH("set price",x))))

 

[justme101]

In that case try

=LET(da,B4:B9,db,C1:N2,dc,C4:N9,c,COLUMNS(dc),s,SEQUENCE(c*ROWS(dc),,0),x,INDEX(dc,INT(s/c)+1,MOD(s,c)+1),xx,CHOOSE({1,2,3,4},INDEX(da,INT(s/c)+1),x,INDEX(db,1,MOD(s,c)+1),INDEX(db,2,MOD(s,c)+1)),FILTER(xx,ISERR(SEARCH("set price",x))))

Well, Mr. Fluff, you, sir, are a genius. This works perfectly. and 80% of this formula is completely new to me. I thought I knew excel, but I better get to studying again . Any references, where I can improve my skills, with formulas specifically?

In that case try

=LET(da,B4:B9,db,C1:N2,dc,C4:N9,c,COLUMNS(dc),s,SEQUENCE(c*ROWS(dc),,0),x,INDEX(dc,INT(s/c)+1,MOD(s,c)+1),xx,CHOOSE({1,2,3,4},INDEX(da,INT(s/c)+1),x,INDEX(db,1,MOD(s,c)+1),INDEX(db,2,MOD(s,c)+1)),FILTER(xx,ISERR(SEARCH("set price",x))))

Also, the data was modified (by the HQ) and now they need have some extra columns on the left, which also needs to be pulled in along with the solution you provided. I am putting the updated screenshot below, but if you want me to create a new thread, I'll be happy to do that.

So, in this case along with the product, the type of pricing, the start and end date, the individual time slot, and stage allotment also needs to be given, which is important. So, if I take product A as an e.g. for wk1, there will be two lines created in the output, something like this:

Really appreciate the help here. Thank you.

 

[Fluff]

How about

=LET(da,B4:D9,db,E1:P2,dc,E4:P9,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))))

 

[justme101]

How about

=LET(da,B4:D9,db,E1:P2,dc,E4:P9,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))))

This formula definitely works, but assumes the data is till row 9 (as I gave you the sample data till that row no.), but it will definitely be much more than that, not sure of the exact number. So, I tried to modify the number of rows to an estimate, like 900, but it pulls up the blanks as well and shows them as 'zeroes'. Anything to remove those extra non-data cells?

 

[Fluff]

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<>"")))