logo
down
shadow

Creating hierarchical sequence in Excel using several functions in formula


Creating hierarchical sequence in Excel using several functions in formula

By : treasure ngaobiwu
Date : November 21 2020, 01:01 AM
With these it helps Ok, first of check this article out here, you should be able to find everything you need to create what you need. Now here is all formulas per every level in your example
Level 1
code :
=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A1,-1,0,1,1),".",""))),1,IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))),VALUE(OFFSET(A10,-1,0,1,1))+1,VALUE(LEFT(OFFSET(A1,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A1,-1,0,1,1),".","@",1))-1))+1))
=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A2,-1,0,1,1),".",""))),"0.1",IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))),OFFSET(A2,-1,0,1,1)&".1",LEFT(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1)))&IF(ISERROR(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))),VALUE(RIGHT(OFFSET(A2,-1,0,1,1),LEN(OFFSET(A2,-1,0,1,1))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))))+1,VALUE(MID(OFFSET(A2,-1,0,1,1),FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))+1,(FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",2))-FIND("@",SUBSTITUTE(OFFSET(A2,-1,0,1,1),".","@",1))-1)))+1)))
=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A3,-1,0,1,1),".",""))),"0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))),OFFSET(A3,-1,0,1,1)&".1",LEFT(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))),VALUE(RIGHT(OFFSET(A3,-1,0,1,1),LEN(OFFSET(A3,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))))+1,VALUE(MID(OFFSET(A3,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))+1,(FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",3))-FIND("`",SUBSTITUTE(OFFSET(A3,-1,0,1,1),".","`",2))-1)))+1)))
=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A4,-1,0,1,1),".",""))),"0.0.0.1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))),OFFSET(A4,-1,0,1,1)&".1",LEFT(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))),VALUE(RIGHT(OFFSET(A4,-1,0,1,1),LEN(OFFSET(A4,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))))+1,VALUE(MID(OFFSET(A4,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))+1,(FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",4))-FIND("`",SUBSTITUTE(OFFSET(A4,-1,0,1,1),".","`",3))-1)))+1)))


Share : facebook icon twitter icon
Using Excel Formula functions (ERF, ERFC) in Excel VBA code?

Using Excel Formula functions (ERF, ERFC) in Excel VBA code?


By : Omneya Atef
Date : March 29 2020, 07:55 AM
help you fix your problem To use a worksheet formula in vba, you need to put Application.WorksheetFunction. in front of it.
Some functions do have vba equivalents, but (as far as I know) not in the case of erf and erfc
Creating a hierarchical structure from a list of sequence strings

Creating a hierarchical structure from a list of sequence strings


By : Maryam Hammami
Date : March 29 2020, 07:55 AM
Hope that helps It would help a bit to add Sequence property to the class and use it to find the parent:
code :
public class Hierarchy
{
    public int ID { get; set; }
    public int ParentID { get; set; }
    public string Sequence { get; set; }
}

public static IList<Hierarchy> GetHierarchy(IList<string> sequenceList)
{
    int iD = 0;
    List<Hierarchy> hierarchy = new List<Hierarchy>();

    foreach (string sequence in sequenceList)
    {
        iD++;
        List<string> childSequence = new List<string>();

        string[] sequenceParts = sequence.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
        // If the sequence contains sub-sequence, i.e. "2.1" is a sub-sequence of "2".
        if (sequenceParts.Count() > 1)
        {
            var parentSequence = sequence.Substring(0, sequence.LastIndexOf("."));
            var parent = hierarchy.Single(x => x.Sequence == parentSequence);

            hierarchy.Add(new Hierarchy() { ID = iD, ParentID = parent.ID, Sequence = sequence });
        }
        else
            // Add top level.
            hierarchy.Add(new Hierarchy() { ID = iD, ParentID = 0, Sequence = sequence });
    }

    return (IList<Hierarchy>)hierarchy;
}
public class Hierarchy
{
    public int ID { get; set; }
    public int ParentID { get; set; }
}

public static IList<Hierarchy> GetHierarchy(IList<string> sequenceList)
{
    int iD = 0;
    List<Hierarchy> hierarchy = new List<Hierarchy>();

    foreach (string sequence in sequenceList.Where(x => x.Count(f => f == '.') == 0)) // get the root nodes
    {
        iD++;

        var item = new Hierarchy() { ID = iD, ParentID = 0 };
        hierarchy.Add(item);
        hierarchy.AddRange(GetChildsRecursive(sequence, item.ID, sequenceList, () => ++iD));
    }

    return (IList<Hierarchy>)hierarchy;
}

private static IList<Hierarchy> GetChildsRecursive(string parentSequence, int parentId, IList<string> sequences, Func<int> idGenerator)
{
    var parentDots = parentSequence.Count(f => f == '.');
    var childSequences = sequences.Where(x => x.StartsWith(parentSequence) && x.Count(f => f == '.') == parentDots + 1);

    var list = new List<Hierarchy>();
    foreach (var childSequence in childSequences)
    {
        var item = new Hierarchy() { ID = idGenerator(), ParentID = parentId };
        list.Add(item);
        list.AddRange(GetChildsRecursive(childSequence, item.ID, sequences, idGenerator));
    }
    return list;
}
Creating recurrent sequence by conditional formula in F#

Creating recurrent sequence by conditional formula in F#


By : mark
Date : March 29 2020, 07:55 AM
Hope that helps You could do it using a mutable value and a for loop. Alternatively, it is possible to use unfold:
code :
Seq.unfold (fun (a,i) -> 
    let a' = 
        if i % 2 = 0
        then a*2
        else a+1
    Some (a', (a',i+1)))
    (1,1)
|> Seq.append [1]
Is there a way to record down the sequence of Excel formula calculation

Is there a way to record down the sequence of Excel formula calculation


By : daimatz
Date : March 29 2020, 07:55 AM
hop of those help? To determine the last calculation sequence used you could extract the calculation chain part from the XLSX/XLSM file and decode it. See https://msdn.microsoft.com/en-us/library/office/gg278336.aspx as a starting point
For small scale tests you can use a Calculation Sequence tracking UDF like this one
Excel Formula Series of #'s and Sequence

Excel Formula Series of #'s and Sequence


By : user3658149
Date : March 29 2020, 07:55 AM
This might help you I need a formula that uses a series of numbers in one column (numbers 1-21) and returns just a number for every 21 cells. , Put this in the first column first cell:
code :
=MOD(ROW(A1)-1,21)+1
=INT((ROW(A1)-1)/21)+1
Related Posts Related Posts :
  • Excel - VB - Re-ordering /Renaming columns
  • "Overload" F9 (or shift+F9) in Excel spreadsheet with VBA function?
  • ShowAllData method of Worksheet class failed
  • VBA If cell is under a certain length, highlight and display message
  • VBA: Unable to get the Match property of the WorkSheet function class
  • Highlighting the second and more duplicate rows in excel but not first occurrence?
  • Excel add- in focus not returned to the active sheet
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org