Databases
StackOverflow2013
Postsdbo
POExcel Generating a formula based sequence based on levels
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel Generating a formula based sequence based on levels
    primarykey
    data
    text
    <p>What I am trying to achieve is the following</p> <pre><code> ID Name Level 1 ABC 1 11 ABC 2 11001 ABC 3 1100101 ABC 4 110010101 ABC 5 11001010101 ABC 6 11002 ABC 3 1100201 ABC 4 110020101 ABC 5 11002010101 ABC 6 11002010102 ABC 6 110020102 ABC 5 11002010201 ABC 6 11002010202 ABC 6 11002010203 ABC 6 2 ABC 1 21 ABC 2 21001 ABC 3 2100101 ABC 4 210010101 ABC 5 210010102 ABC 6 21002 ABC 3 </code></pre> <p>I have the following code in excel to generate this code but I m not able to achieve the above, can anyone help me with this, please find below the formula I am using:</p> <pre><code>=CONCATENATE(COUNTIF($D$9:D18,1),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=1,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,2),COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=1,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,2),""),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=2,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,3),TEXT(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=2,ROW(D9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,3),"000"),""),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=3,ROW(D9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,4),TEXT(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=3,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,4),"00"),""),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=4,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,5),TEXT(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=4,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,5),"00"),""),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=5,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,6),TEXT(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=5,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,6),"00"),""),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=6,ROW(D9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,7),TEXT(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=6,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,7),"00"),""),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=7,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,8),TEXT(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=7,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,8),"00"),""),IF(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=8,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,9),TEXT(COUNTIF(INDIRECT("D"&amp;MAX(IF(INDIRECT(ADDRESS(9,4)):INDIRECT(ADDRESS(ROW(),4))=8,ROW($D$9:INDIRECT(ADDRESS(ROW(),4))),0))):D18,9),"000"),"")) </code></pre> <p>Basically I am trying to generate the code in the ID field automatically, let me explain how it works: ID are set using the Levels field for Level 1 its the first digit in the ID field it represents, for Level 2 its the 2nd digit for level 3 its the 3 digits after 2 digits , and afterwards 2 digits for each levels. Furthermore once a level 1 has been assigned it cannot to assigned to another level 1 therefore the second level 1 will be give the next number, its first level 2 will have 1 as number and 2nd level 2 within will give it 2 and the same goes for the following code.</p> <p>The problem with my code is that it calculates the value fine till the first level 1 and its below code but however its start behaving incorrectly as I go down the codes, it should not print the level code for 4,5,6 and more if I am generating a level 3. these are some of the problems I am facing</p> <p>Thanks in advance</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USuser1799524
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel Generating a formula based sequence based on levels
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POExcel Generating a formula based sequence based on levels
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POExcel Generating a formula based sequence based on levels
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou need to explain precisely how the required output is generated. Since you say your formula doesn't work, that not helping anyone understand what you're trying to do.
      singulars
      1. POExcel Generating a formula based sequence based on levels
      1. USTim Williams
    2. COwhat columns do you have? what column values are you going to generate?
      singulars
      1. POExcel Generating a formula based sequence based on levels
      1. USbmanvelyan
    3. COLevels is basically the Column "D" which I m using in my formula
      singulars
      1. POExcel Generating a formula based sequence based on levels
      1. USuser1799524
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload