Friday 12 December 2014

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.


2 comments:

  1. Hi Mohana,

    I Tried the above process to filter the objects and working perfectly.

    I have one doubt, Is there any count limit in filtering the objects in both NAV and Excel side

    thanks

    ReplyDelete
  2. Hi Mohana,

    I Tried the above process to filter the objects and it working perfectly.

    I have one small question, Is there any count limit in filtering the objects in both NAV and Excel.

    ReplyDelete