Reference column at level zero and ancestors to pull in data to new columns
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
Best Answer
-
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 = ....................))
Answers
-
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.
-
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
-
@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.
-
@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
-
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 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)))
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!