Greetings
I have a sample file where I would like to use formulas to break/split column A into multiple columns that follows a hierarchy structure. I did use the search but most results were coming up in VBA and macros. My original file (not the sample provided) is 2000+rows and was originally a CSV file.
Thank you for any insight into this.
Are the 2000+ rows a EXACT repetition of your file i.e range of A1:B27 ? If not, please post file showing more than one "period".
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Hi John,
Thanks for replying. For this file, they are an exact repetition. Although I will be working with other files where the year might change. How would you handle the exact repetition and how would it be different if there were different periods?
Exact repetition offers the chance to use "row" calculations to index to the data. If the data is "irregular" then it is more difficult to determine the start of a given period.
I recommend you post a sample of both: in theory the "irregular" solution will work for the "regular" but not vice-versa.
I understand and thanks again. I have an example of a different hierarchy that is not a repetition in the columns and it is completely different from the first file.
*removed file
Last edited by Statzz; 10-29-2021 at 08:34 AM.
Given your latest file and its complexity, my choice would be a VBA solution. I don't have 365 so I cannot comment on whether a formulaic solution is more viable with 365.
A result page on your latest file will help.
I cannot use VBA. Could you provide a solution for my first example, the repetition hierarchy? This would be a lot of help.
I haven't worked out a "flexible" hierarchy which needs to based on "parent/child" relationships. I'll try and look at this over the weekend but cannot promise a solution.
Hopefully others will pick up the challenge!
I appreciate the replies and hopefully someone else can jump in. To get the ball rolling though, what formulas could I use? I am just trying to approach the problem and I am coming up blank on where to even start.
Have a great weekend John!
Edit: Could I use something like IF column A has a dash move cell to new column B, else move other cells to column C?
Last edited by Statzz; 10-29-2021 at 10:00 AM.
The only ideea that I have is to use PowerQuery:
HTML Code:
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
Thank you tanasedn! This was really helpful. Very advanced for me because I am a beginner excel user.
I did ask my instructor for clarification and she said it could be done using IFs statements and thinking about the parent-child problem in generation terms.
Okay, so using my instructors clarification, I came up with some formula rules for the sample book in post 1. Sorry I cannot link them because it is a URL and my account is new. Please go to post 1 for file
rules.JPG
So using my rules, is there a way to move the words in column A to new columns based on the conditions I provided?
See "before" in attached using solution offered by your instructor!:
Thank you John for not giving up! You solved the problem in post 1 and I believe this is the starting point for me to go on for the more complex sheets. I appreciate the help and I feel so much better!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks