Forum Discussion

Donna Hernandez's avatar
Donna Hernandez
Copper Contributor
Sep 22, 2017

Formula help please

Hi all, I am trying to create a few formula's for the following # of New Boxes QAed:     >>>> 0 # of Those Boxes Failed QA:     >>>> 0 Failed Box #'s: Part #: PG #: Reason For Failure: ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 22, 2017

Hi Donna,

 

The formula is in attached files, if you open them and save Production Sheet in some folder full path substitutes, thus works with closed file.

 

Formula is well-known, i'll find the link some later with detailed explanation how it works. Please note that's array formula, that means you shall use Ctrl+Shift+Enter to enter it.

For Box# it'll be

=IFERROR(INDEX('[Production Sheet Template.xlsx]Sheet1'!$A$4:$H$33,SMALL(IF('[Production Sheet Template.xlsx]Sheet1'!$E$4:$E$33="X",ROW('[Production Sheet Template.xlsx]Sheet1'!$A$4:$A$33)-ROW('[Production Sheet Template.xlsx]Sheet1'!$A$4)+1),ROW(1:1)),3),"")

put it in A8 and drag down. Latest number 3 in the formula is the column number in Production Sheet, for other fields you shall change only it.

 

Important: array formulas don't work within merged cells, so i unmerged them in DPR. In general, better never to use merged cells, you have only issues with them.

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 23, 2017

    Hi Donna,

     

    Step by step explanation of the pattern is here http://www.exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/4/ (not only, at many other places). In addition here are some comments within your formula

    =IFERROR(
   INDEX('[Production Sheet Template.xlsx]Sheet1'!$A$4:$H$33,
      N("range to find is entire template from row 4 to 33")+
      N("could be change on dynamic or take with the gap, e.g")+
      N("change 33 on 333 everywhere below")+

      N("SMALL returns the first row number there the X is found")+
      N("for the first cell in destinitian list, defines by ROW(1:1)")+
      N("when the copy down that will be second position ROW(2:2), etc")+

      SMALL(
         IF('[Production Sheet Template.xlsx]Sheet1'!$E$4:$E$33="X",
            N("IF returns array with row numbers where X is found")+
            N("and zeros otherwise. Here we make 1 for the")+
            N("first row in the range")+

            ROW('[Production Sheet Template.xlsx]Sheet1'!$A$4:$A$33) - 
            ROW('[Production Sheet Template.xlsx]Sheet1'!$A$4)+1
         ),ROW(1:1) + N("counter for the SMALL - 1,2,..")
         
      ),3 + N("number of the column in template range from which")+
            N("we pickup data")
   ),""
)

    Above is in the cell A8 in the attached file, for the rest i removed comments.

    Result shall look as

     

Resources