enter image description here

I would like to filter column D , But need to keep the id from Column C to show. The easy way would to just have the column C list the code for each row, but that gets confusing when trying to get a count of the codes. Please help :)

  • If I understand you and what I see in the example, you want to filter column D and have the proc_code in C display for whatever value you've filtered in D; is this correct? – BillDOe Dec 6 at 20:53
  • yes that is correct. – David Castanon Dec 7 at 2:03
  • @DavidCastanon,, what I've realized that, while Filter any Row or Value from Column D for example Max= you want to keep the Code in Column C A4563 visible,, am I write? I guess values in Col Dare in separate Rows,, has common Code !! – Rajesh S Dec 7 at 6:32
  • Now tell me are you also thinking to Filter more than one value from Col D like Max= & UVSP= with their respective Codes in Col C? – Rajesh S Dec 7 at 6:35
  • I have encountered this in some of my spreadsheets before, which are, granted rather small, but the only solution I know is to fill down each unique value. You could probably write a small macro to fill down until it encounters a new value, then start using the new value. – BillDOe Dec 7 at 23:02

You can solve the issue by using Helper Column:

enter image description here

How it works:

  • Insert blank column between Code & Country Name (is Helper Column).
  • In Cell B5 write this Formula =A5.
  • Write this formula in Cell B6 & fill down.

    =IF(A6="",B5,A6)
    
  • Write Country Name to filter data in Cell D2 & D3.

  • In Cell E2 enter this Array formula, finish with ctrl+Shift+Enter then fill Right and Down.

     {=IFERROR(INDEX($B$5:$C$18, SMALL(IF(COUNTIF($D$2, $C$5:$C$18)+COUNTIF($D$3, $C$5:$C$18), ROW($A$5:$C$18)-MIN(ROW($A$5:$C$18))+1), ROW(A1)), COLUMN(A1)),"")}
    

N.B.

  • In your Sheet, insert a Helper Column between Col C and D.
  • Purposely I've used 2 Country name as Criteria to justify the efficiency of the used Array Formula. This Formula will work with one Criteria also.
  • If you use one Criteria then use this one.

       {=IFERROR(INDEX($B$5:$C$18, SMALL(IF(COUNTIF($D$2, $C$5:$C$18), ROW($A$5:$C$18)-MIN(ROW($A$5:$C$18))+1), ROW(A1)), COLUMN(A1)),"")}
    
  • Adjust cell references in the Formula as needed.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.