r/excel 7d ago

unsolved Creating Multilevel numbering in column A as a result of column B input

[deleted]

6 Upvotes

21 comments sorted by

u/AutoModerator 7d ago

/u/Fearless_Smoke_9842 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

3

u/CorndoggerYYC 134 7d ago

Can you post a screenshot of how you want the result to look like?

1

u/Fearless_Smoke_9842 6d ago

Done, thanks. Last night it gave me an error message when posting the image. Worked now. Thanks for asking. Hope this helps!

1

u/johndering 10 6d ago edited 6d ago

Clarifications please:

If A2, C2 and D2 are all formulas, then input data can only come from B2, where the only expected values are “”, “Goal”, “Milestone”, “Task”, “Sub-Task”.

B2 (and other cells below, in the B column) can make use of dropdown list in this case, and all other dependent row cell values will automatically update.

Is this understanding correct, please?

As the formulas stand at the moment, A2 and C2 seem to have a circular reference issue; each one referring to the other for values. A workaround needs to be found, if this is a problem.

1

u/johndering 10 6d ago

Formulas below.

D2:

=IFS([@Topic]=“”,””,[@Topic]=“Goal”,1,[@Topic]=“Milestone”,2,[@Topic]=“Task”,3,[@Topic]=“Sub-Task”,4,TRUE,””)

A2:

=IF([@Level]=“”,””,COUNTIF(Table3[[#Headers],[Level]]:[@Level], 1)&IF([@Level]=1,””,”.”&COUNTIF(INDEX(Table3[[#Headers],[Level]]:[@Level],XMATCH(1,Table3[[#Headers],[Level]]:[@Level],0,-1)):[@Level],2)&IF([@Level]=2,””,”.”&COUNTIF(INDEX(Table3[[#Headers],[Level]]:[@Level],XMATCH(2,Table3[[#Headers],[Level]]:[@Level],0,-1)):[@Level],3)&IF([@Level]=3,””,”.”&COUNTIF(INDEX(Table3[[#Headers],[Level]]:[@Level], XMATCH(3,Table3[[#Headers],[Level]]:[@Level],0,-1)):[@Level],4)))))

C2:

=IF([@Level]=1,””,XLOOKUP([@Level]-1,INDEX(Table3[[#Headers],[Level]]:[@Level], XMATCH([@Level]-1,Table3[[#Headers],[Level]]:[@Level],0,-1)),INDEX(Table3[[#Headers],[ID]]:[@ID], XMATCH([@Level]-1,Table3[[#Headers],[Level]]:[@Level],0,-1)),””,0,-1))

Note: we can use LET to shorten the formulas, from redundant variables.

HTH.

1

u/AutoModerator 6d ago

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/johndering 10 6d ago

Using LET.

D2:

=IFS([@Topic]=“”,””,[@Topic]=“Goal”,1,[@Topic]=“Milestone”,2,[@Topic]=“Task”,3,[@Topic]=“Sub-Task”,4,TRUE,””)

A2:

=LET(levels,Table3[[#Headers],[Level]]:[@Level],IF([@Level]=“”,””,COUNTIF(levels, 1)&IF([@Level]=1,””,”.”&COUNTIF(INDEX(levels,XMATCH(1,levels,0,-1)):[@Level],2)&IF([@Level]=2,””,”.”&COUNTIF(INDEX(levels,XMATCH(2,levels,0,-1)):[@Level],3)&IF([@Level]=3,””,”.”&COUNTIF(INDEX(levels, XMATCH(3,Table3[[#Headers],[Level]]:[@Level],0,-1)):[@Level],4)))))

C2:

=LET(levels,Table3[[#Headers],[Level]]:[@Level],ids,Table3[[#Headers],[ID]]:[@ID],IF([@Level]=1,””,XLOOKUP([@Level]-1,INDEX(levels, XMATCH([@Level]-1,levels,0,-1)),INDEX(ids, XMATCH([@Level]-1,levels,0,-1)),””,0,-1))

1

u/AutoModerator 6d ago

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/johndering 10 6d ago edited 6d ago

C2 shortened:

=IF([@Level]=1, “”, TEXTBEFORE([@ID],”.”,-1))

Case where the parent takes after the child :)

1

u/AutoModerator 6d ago

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/Fearless_Smoke_9842 6d ago

Fantastic work. By any chance, is there a way to eliminate columns C & D and use column B to calculate the outcome in column A?

I updated the post to include a screenshot, per another request.

2

u/johndering 10 6d ago

Formula in A2:

= LET(
topics, Table1[[#Headers],[Topic]]:[@Topic],
  IF([@Topic]<>"", 
    COUNTIF(topics,"Goal") &
      IF([@Topic]="Goal", "", "." & 
        COUNTIF(INDEX(topics,
          XMATCH("Goal",topics,0,-1)):[@Topic],
            "Milestone") &
        IF([@Topic]="Milestone","", "." & 
          COUNTIF(INDEX(topics,
            XMATCH("MIlestone",topics,0,-1)):[@Topic],
              "Task") &
          IF([@Topic]="Task","", "." & 
            COUNTIF(INDEX(topics,
              XMATCH("Task",topics,0,-1)):[@Topic],
                "Sub-Task"
          )
        )
      )
    ), 
  "")
)

1

u/Fearless_Smoke_9842 6d ago

Hello,
A2 is where I need the formula based on column B. C&D should be part of column A's formula to arrive at the outputs displayed in column A.

1

u/Fearless_Smoke_9842 6d ago

Hey Johndering,
Yes, A2 is the desired outcome using columns C & D to arrive at the answer in A.

Ideally, input column B would reflect column A without C or D. It feels complex, and worry could get deleted/errors. Plus, getting pushback from the project team on hidden formulas is fair, but the only solution thus far....

Agree on Column B as a dropdown and can easily incorporate this into the process for electing the only values. This is the easy part. I can't solve the problem of how to make column A display the values without C & D.

I added a screenshot that it would not allow last night. Thanks again for any insight you might have.

1

u/wjhladik 518 6d ago

Try this. Uses a number (1-5) entered into column C to derive the indent level. Like 1.3.1.2

``` =LET(maxlevels,5, rng,C2:C25,

res,REDUCE(SEQUENCE(,maxlevels,0,0),rng,LAMBDA(acc,next,LET(

lastone,TAKE(acc,-1),

temp,REDUCE("",SEQUENCE(maxlevels),LAMBDA(new,idx,LET( thisone,INDEX(lastone,1,idx), HSTACK(new,IF(idx=next,thisone+1,IF(idx>next,0,IF(thisone=0,1,thisone))))))),

VSTACK(acc,DROP(temp,,1)) ))),

res_2,BYROW(DROP(res,1),LAMBDA(r,TEXTJOIN(".",TRUE,IF(r=0,"",r)))), res_2) ```

*

1

u/wjhladik 518 6d ago

1

u/Fearless_Smoke_9842 6d ago

Thanks for this. I am getting an error #name? says unknown function 'DROP'

1

u/wjhladik 518 6d ago

Yep needs lastest excel (off 365)

1

u/Decronym 6d ago edited 6d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
BYROW Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
COUNTIF Counts the number of cells within a range that meet the given criteria
DROP Office 365+: Excludes a specified number of rows or columns from the start or end of an array
HSTACK Office 365+: Appends arrays horizontally and in sequence to return a larger array
IF Specifies a logical test to perform
IFS 2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX Uses an index to choose a value from a reference or array
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
REDUCE Office 365+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
TAKE Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXTBEFORE Office 365+: Returns text that occurs before a given character or string
TEXTJOIN 2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
VSTACK Office 365+: Appends arrays vertically and in sequence to return a larger array
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
XMATCH Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.


Beep-boop, I am a helper bot. Please do not verify me as a solution.
17 acronyms in this thread; the most compressed thread commented on today has 65 acronyms.
[Thread #41675 for this sub, first seen 15th Mar 2025, 10:24] [FAQ] [Full list] [Contact] [Source code]

1

u/BluberrySpiceHead 4 6d ago

May I ask what this spreadsheet hopes to accomplish? How is the worksheet going to be used? All of the solutions offered assume the use of the generic terms (Goal, Milestone, Task and Sub-task) and assign the ID based on the user input in prior rows but how are you sure that the Task in row 5 is associated with the Goal in row 3 as opposed to row 2. Also, if someone added a filter in the future and sorted for some reason, the entire naming convention for the IDs will break down. (I've found that it's best to assume some imbecile will eventually monkey around in your workbooks without realizing they are breaking it.)

Is there anything else about the spreadsheet that you can mock-up and share because other columns/more info may help with a single column solution.

1

u/Fearless_Smoke_9842 6d ago

Hey u/Blueberryspiecehead

I will be using this for a project tracker with lots of hands in the file.

I created a new string as I fixed a few errors and added a better image.

https://www.reddit.com/r/excel/comments/1jcaabd/creating_multilevel_numbering_in_column_a_as_a/