Pipe Filter with Excel | Alternative 2

I looked at the video by Kerry Rosvold on how to make a pipe filter in excel here and MVP Mark Brummel’s alternative here  . Both are awesome.

 

 

I have also an alternative way.



So this is how I do it.


You copy a few lines to excel like this:



And in Excel you paste, and Remove all other columns except No.





Add pipe (|) in B1 cell and =A1 in B2 cell and Enter




Add below formula in B3 cell and Enter

 =B2&B1&A3



Now it looks like




Re Select B3 Cell and select B1 value and Press F4 (which makes Pipe value Constant)




Now formula in B3 looks like

=B2&$B$1&A3


B1 is changed as $B$1



Copy B3 cell and paste in all below cells



The last row has the final filter.



Comment List
  • Hi Mohana,

    I find it faster to use =A2 in field B2, followed by =A3&"|"&B2 in field B3.

    You can then extend the formula to all consecutive rows by clicking in field B3 and double-clicking the lower right corner.

    Don't forget to copy the last row and "Paste as value" to get actual filter usable in NAV.

    Cheers,

    Jurica

Related
Recommended