Forum Discussion

A_SIRAT's avatar
A_SIRAT
Iron Contributor
Nov 19, 2021

Counting items and box numbering

Hi Guys,

I would like to easily create an Invoice and Packing List using MS excel from Data extracted from a system. All the required headers needed from Consignor to Consignee, order nr, destination, etc are provided. My challenge is on the box numbering. Please have a look at the attached file.

After getting the data, I add the boxes range/numbering. I want to skip counting the boxes that are repeated.

 

excel

2 Replies

  • DKoontz's avatar
    DKoontz
    Iron Contributor
    Nov 19, 2021

    A_SIRAT 

    If the duplicate box #'s are always right next to each other, this should work for your purposes.

    =IF(F3=F2,"",F3-E3+1)

     Just checks if the above box # is the same as the current one and if so, ignores it. Otherwise uses your same formula.

    • mtarler's avatar
      mtarler
      Silver Contributor
      Nov 19, 2021

      DKoontz A variant that tries to accommodate non-adjacent and overlapping possibilities:

      =MAX(0,MIN(IF((F4>=E$2:E3)*(F4<=F$3:F3),E$3:E3-1,F4))-MAX(IF((E4>=E$3:E3)*(E4<=F$3:F3),F$3:F3+1,E4))+1)-SUMIFS(I$3:I3,E$3:E3,">"&E4,F$3:F3,"<"&F4)

      This starts in the 2nd data row.  I changed a few box numbers to really test it in the attached. 

Resources