I have a column "CodedName" that has a 8 digits number with a hierarchy structure built in the digits, see below.

Level 1 XX000000
Level 2 XXXX0000
Level 3 XXXXXX00
Level 4 XXXXXXXX

I have over 75.000 nodes and I need to build a formula that give the parent node of each node of those 75.000 (except the ones for Level 1 , because they don't have any parent nodes)

How would the formula look like to achieve this in Google Sheets?

I want to add the info about what parent code has the child. I have added under the "Parent" column the parents to the below example codes.

CodedName   Parent
13000000    
13010000    13000000    
13010100    13010000    
13010190    13010100    
13010200    13010000    
13010290    13010200    
13010300    13010000    
13010301    13010300    
13010302    13010300    
13010303    13010300    
13010390    13010300    
13010400    13010000    
13010490    13010400       
13010491    13010400

Here is a dataset to test with (including Parent column with some comments):

CodedName   Parent
16099000    16090000
16099090    16099000
16100000    16000000
16100100    16100000
16100190    16100100
16100200    16100000
16100201    16100200
16100202    16100200
16100203    16100200
16100290    16100200
16100300    16100000
16100301    16100300
16100302    16100300
16100390    16100300
16109000    16100000
16109090    16109000
16110000    16100000
16110100    16110000
16110190    16110100
16110200    16110000
16110290    16110200
16110300    16110000
16110390    16110300
16110400    16110000
16110490    16110400
16110500    16110000
16110590    16110500
16110600    16110000
16110690    16110600
16110700    16110000
16110790    16110700
16110800    16110000
16110890    16110800
16110900    16110000
16110901    16110900
16110990    16110900
16111000    16110000
16111001    16111000
16111002    16111000
16111090    16111000
16111100    16111000
16111101    16111100
16111102    16111100
16111103    16111100
16111190    16111100
16119000    16110000
16119090    16119000
16120000    16100000
16120100    16120000
16120190    16120100
16120200    16120000
16120290    16120200
16129000    16120000
16129090    16129000
16130000    16100000
16130100    16130000
16130190    16130100
16139000    16130000
16139090    16139000
16140000    16100000
16140100    16140000
16140190    16140100
16140200    16140000
16140290    16140200
16149000    16140000
16149090    16149000
16150000    16100000
16150100    16150000
16150101    16150100
16150102    16150100
16150103    16150100
16150190    16150100
16150200    16150000
16150201    16150200
16150290    16150200
16150300    16150000
16150301    16150300
16150390    16150300
16159000    16150000
16159090    16159000
16160000    16100000
16160100    16160000
16160101    16160100
16160190    16160100
16160200    16160000
16160201    16160200
16160202    16160200
16160203    16160200
16160204    16160200
16160290    16160200
16160300    16160000
16160390    16160300
16169000    16160000
16169090    16169000
16170000    16100000
16170100    16170000
16170101    16170100
16170102    16170100
16170103    16170100
16170104    16170100
16170105    16170100
16170106    16170100
16170190    16170100
16170200    16170000
16170201    16170200
16170202    16170200
16170203    16170200
16170204    16170200
16170205    16170200
16170206    16170200
16170207    16170200
16170290    16170200
16179000    16170000
16179090    16179000
16180000    16100000
16189000    16180000
16189090    16189000
17000000    10000000 xx wrong, should be blank
17010000    17000000
17010100    17010000
17010190    17010100
17010191    17010190 xx wrong, should be 00
17010192    17010190 xx wrong, should be 00
17010200    17010000
17010290    17010200
17010400    17010000
17010490    17010400
17010491    17010490 xx wrong, should be 00
17010492    17010490 xx wrong, should be 00
17010700    17010000
17010790    17010700
17010791    17010790 xx wrong, should be 00
17010792    17010790 xx wrong, should be 00
17139000    17130000

CodePudding user response：

This formula will match the desired results you show:

=arrayformula( 
  ifs( 
    isblank(D2:D)   regexmatch( to_text(D2:D), "0{6}$" ), 
      iferror(1/0), 
    regexmatch( to_text(D2:D), "[1-9]\d$" ), 
      value( left(D2:D, 6) & "00" ), 
    true, 
      value( 
        left( 
          regexextract( to_text(D2:D), "(\d ?)[1-9]0*$" ) 
          & "00000000", 
          8 
        ) 
      ) 
  ) 
)

CodePudding user response：

try:

=INDEX(
 IF(REGEXMATCH(A2:A15&"", "0{6}$"),, 
 IF(REGEXMATCH(A2:A15&"", "0{4}$"), REGEXREPLACE(A2:A15&"", "(\d{2})(. )", "$1000000"), 
 IF(REGEXMATCH(A2:A15&"", "0{2}$"), REGEXREPLACE(A2:A15&"", "(\d{4})(. )", "$10000"), 
                                    REGEXREPLACE(A2:A15&"", "(\d{6})(. )", "$100")))))

CodePudding user response：

Divide each of the numbers by each of 10^6,4,2,0 and see if there's a reminder. If there's one, get the quotient of division and multiply by the 10^6,4,2 respectively. In general, number operations should be significantly faster than string operations

=ARRAYFORMULA(
  BYROW(A2:A135,
    LAMBDA(r, 
      REDUCE(
        0,
        IF(
          MOD(r,10^{6,4,2,0})=0,
          {" ",QUOTIENT(r,10^{6,4,2})*10^{6,4,2}}
        ),
        LAMBDA(a,c,IF(a,a,c))
      )
    )
  )
)

Or as a drag fill formula for A2:

=ARRAYFORMULA(
  REDUCE(
    0,
    IF(
      MOD(A2,10^{6,4,2,0})=0,
      {" ",QUOTIENT(A2,10^{6,4,2})*10^{6,4,2}}
    ),
    LAMBDA(a,c,IF(a,a,c))
  )
)