Alternative for Pivot table - row data

[mmor79]

Hi,

Can anyone help me with the following. I've prepared a file with a data (download) where I've a pivot table linked to. Now I would like to replace the pivot table by a VBA script so the user need to fill in one field (cell L1), which is the parameter and the result should appear on the screen (cell K4 untill last row possible). On the attached excel file I would like to replace the pivot table on column F and G.
Anyone can support me with this. I've been looking for an answer for a long time on internet but unsuccesfull. I know for some of you this would be simple query but I reaaly appreciate if someone can help me with this.

[rylo]

Hi

You don't need VBA to do this, it can be done with formulas.

D2: =IF(AND(B2=$L$1,COUNTIF($A$2:A2,A2)=1),ROW(),"")
Copy down to cover your data
K4: =IF(ROW()-3>COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()-3)))
Copy down.

Now if you change the value in L1, the results will be updated.

HTH

rylo

[mmor79]

Hi Rylo,

I tried the formulas but there's an error. Could you save the updated file on the forum, so I can check on that?
Thanks for your help
Mmor

[rylo]

Hi

Here goes.
822813.xlsx

rylo

[mmor79]

Hi Rylo,

Thanks a lot for your help and sending the updated file. I really appreciate your help. This will help me through building my file further.

Mmor