• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Numbering For Duplicates

Kenshin

Member
Have a nice day people, have problem with this numbering for duplicates, Im explain on excel workbook, please take a look at the file


thank you
Kenshin
 

Attachments

  • Book1(3).xlsx
    10.1 KB · Views: 19
@pecoflyer , I think it's like product version numbers; the Products in this case are column CUSTOMER, the Versions are in column LOKASI. The first LOKASI for each CUSTOMER being no.1, the second LOKASI numbered 2 etc.
Case seeems to be being ignored and I think the apostrophe in cell B11 is a typo.

I've got a Power Query solution which I'm tidying up, then I may have a look at a formula solution…
 
If you have a version of Excel with built-in sheet functions including UNIQUE and FILTER then this in cell A12 copied down.
Code:
=COUNTA(UNIQUE(FILTER(C$2:C2,B$2:B2=B2)))
Otherwise on the Power Query sheet of the attached is a table at cell K1 that can be updated by right-clicking it and choosing Refresh.

I may still have a go at putting together a formula for versions of Excel without UNIQUE and FILTER.
 

Attachments

  • Chandoo45097.xlsx
    22.7 KB · Views: 11
I'm struggling to find an unmanageable 'old-school' formula, let alone a manageable one!
 
To assign numbers to the entries under LOKASI, I used
= XMATCH(LOKASI, UNIQUE(LOKASI))
[not sure whether this is the result that is specified],
Since UNIQUE is not available in Excel 2010-2019
Code:
The Name recordNum refers to
=ROW(LOKASI)-1

= MATCH(
    LOKASI,
    INDEX(
      LOKASI,
      SMALL(
        IF(
          MATCH(LOKASI,LOKASI,0)
            =recordNum,
          recordNum),
        recordNum)
     ),
  0 )

A lot of effort goes into identifying a list of distinct names!
 
@Peter Bartholomew , both your formulae return 3 for binus/paskal because paskal is the third distinct item in the LOKASI list, but the OP seems to be wanting a 2 there because paskal is the 2nd LOKASI item that customer binus has.
 
@p45cal
Thank you for the explanation! So
Code:
= LET(list, UNIQUE(FILTER(LOKASI, CUSTOMER=@CUSTOMER)),
  XMATCH(@LOKASI, list) )
would at least be a correct formula to try to refactor?
 
So
Code:
= LET(list, UNIQUE(FILTER(LOKASI, CUSTOMER=@CUSTOMER)),
  XMATCH(@LOKASI, list) )
would at least be a correct formula to try to refactor?
It gives the same results as the OP wants (and as my formula in msg #5) so I'd guess yes!
I've assumed list is the 2-column databody of the OP's starting table - curiously the formula gave the right results when list hadn't been defined and showed as #NAME? in the XMATCH Function Arguments dialogue box.
 
There are some odd things going on with the implicit intersection / SINGLE.
The attached workbook appears to work (it uses defined names to hold array formulas and explicit relative references -- a style of programming Excel that has never been in fashion, I admit).
I cannot see the difference between
Code:
= MATCH(TRUE, list=aLOKASI, 0)
and
= MATCH(aLOKASI, list, 0)
apart from the fact that the first form works and the second doesn't.

I suppose I should start the unrewarding task of removing Names by replacing them by the content they refer to in the interest of 'simplicity' but I cannot raise much enthusiasm for that!
 

Attachments

  • indexedDuplicates.xlsx
    12.3 KB · Views: 3
Not having looked up the LET function, my question about list might have been ill-informed. I need to study the LET function.
You have list defined as a Name too - is that as it should be?
 
Peter and p45cal, Good morning.

Maybe this way

= LET(
list, UNIQUE(FILTER($C$2:$C$11, MID($B$2:$B$11,1,3)=MID(B2,1,3))),
XMATCH(C2, list) )

or

=COUNTA(UNIQUE(FILTER(C$2:C2,MID(B$2:B2,1,3)=MID(B2,1,3))))

Decio
 
@p45cal
I develop the idea for a formula step by step using LET because that provides a level of annotation that guides the next step of the calculation. That uses the fact that most references are absolute which allows me to build upon a copy of the formula without losing the original. I then reimplemented the idea using the more limited function set and using Named formulas to hold the steps as array formulas.

The apparent conflict of names is not an issue. Within the scope of the LET function, the value of the local variable 'list' takes precedence. Elsewhere the named formula 'list' is evaluated. The main named formulas are
Code:
filtered:= IF(CUSTOMER=aCUSTOMER, LOKASI,"")
ptr:=      IF((MATCH(filtered, filtered, 0)=recordNum)*(filtered<>""), recordNum)
list:=     INDEX(LOKASI, SMALL(ptr, recordNum))
I did get around to a 'back to basics' solution by eliminating the defined names
= MATCH(TRUE, INDEX($C$2:$C$11, SMALL(IF((MATCH( IF($B$2:$B$11=$B2, $C$2:$C$11, ""), IF($B$2:$B$11=$B2, $C$2:$C$11, ""), 0)=ROW($C$2:$C$11)-1)*(IF($B$2:$B$11=$B2, $C$2:$C$11, "")<>""), ROW($C$2:$C$11)-1), ROW($C$2:$C$11)-1))=$C2, 0 )
Others argue that this is simpler but I am not convinced. For me, it is more a case of revealing the complexity.
 
Back
Top