Reference column at level zero and ancestors to pull in data to new columns

PDunn

I want to pull series and item from the series column into their own columns

The series is part of the item number as well: B060 (series), item in series is B060-122 (item)

In a new column titled Series ID I want the series number on every row level 0 and level 1

In a new column titled Item ID I only want the item # on the level 1 rows and on level 0 it should have NA as the result

I have provided image of what I am trying to achieve

Paul Newcome

You would drop the first one into the third portion of the second one to create a nested IF.

=IF(AND(...............), 0, IF(Level@row = ....................))

Paul Newcome

It looks like we may not necessarily be able to use levels or indentation as a starting point.

B060 is on level zero and has B060 in the first column and NA in the second column, but A3000316 is on the same level and has NA in the first column and A3000316 in the second column. Both entries are on the same level, but they are being treated differently.

PDunn

Can I utilize the category and subcategory columns so if category is Dining = series level 0, If accessories @ level 0 , which have no child rows. I'll try to create something and if I need additional assistance I'll reach back out to you. Thanks Paul

PDunn

@Paul Newcome I wanted to follow up with my final solution that worked perfectly.

The above formula places the series # into the Series ID column

The above formula places a zero in Item ID column if a parent or a single level 0 row. Then if a it is a child row it places item # into Item ID column.

PDunn

@Paul Newcome I am back for additional assistance.

I now need to have a zero for a specific category in the series column. I have these two separate formulas that work independently how can I combine them so it brings back series # for everything except the category of home accents.

=IF(Level@row >= "1", INDEX(ANCESTORS(Series@row), 1), Series@row) - places the parent series in each row

IF(AND(Level@row >= "0", Category@row = "Home Accents"), 0) - places a zero in Series ID if category = Home Accents

Paul Newcome

You would drop the first one into the third portion of the second one to create a nested IF.

=IF(AND(...............), 0, IF(Level@row = ....................))

PDunn

@Paul Newcome success thanks so much for the simple way you explain complex formulas.

Final formulas that work for both columns

Series ID:

=IF(AND(Level@row = "0", Category@row = "Home Accents"), 0, IF(Level@row = "1", INDEX(ANCESTORS(Series@row), 1), Series@row))

  

Item ID:

=IF(AND(Level@row = "0", Category@row = "Home Accents"), Series@row, IF(Level@row = "1", Series@row, IF(Level@row = "0", 0)))

Paul Newcome

Happy to help. 👍️