Hello everybody,
I'll kick off with an introduction first; after having left my 'go-to' Excel forum - which shall remain unnamed - a couple of years ago, due to an increasingly dictatorial rhetoric by a select few administrators, I spent the last year or two in the Excel wilderness, feeding on the scraps of Google search engine results, for solutions to my Excel problems. But as of today, I am happy to say I am a new Chandoo member!
I exaggerate of course, I found a lot of resources over the last couple of years that proved helpful to me, but the source that proved to be more helpful than any other was Chandoo, so here I am.
Anyway, apologies in advance for doing my intro here, but I don't currently have the 'New Thread' option in the forum for New Users. So, on to my Excel query....
Attached is a sample spreadsheet containing captions found on the Statement of Financial Position / Balance Sheet. The logic of the layout is simple:
- GL Class 1 are the overarching captions
- GL Class 2 are sub-categories of GL Class 1
- GL Class 3 are sub-categories of GL Class 2
- GL Class 4 are sub-categories of GL Class 3
- GL Class 5 are sub-categories of GL Class 4
I would like to know if there is any (quick) way of vertically stacking unique caption descriptions into a single column, in such a way that results are produced according to the below logic. I've made a number of attempts with UNIQUE, SORT, VSTACK, HSTACK functions, but none of them giving me exactly what I need. I say 'quick' because I can achieve the desired result with helper columns etc. but this drags on my spreadsheet so ideally I'm looking to use the minimum number of functions in a singular column, to get this done.
- Formula operates from left to right, detecting if there is a unique value in that row that hasn't already appeared in the rows above
- It picks up any unique values and stacks them into the single column
- It then moves on to the next row and repeats the task i.e. scanning from left (GL Class 1) to right (GL Class 5) and stacking any new, unique values
I have included a [partial] example of what the result should look like in column I, with explanations of the logic in column J.
I'll kick off with an introduction first; after having left my 'go-to' Excel forum - which shall remain unnamed - a couple of years ago, due to an increasingly dictatorial rhetoric by a select few administrators, I spent the last year or two in the Excel wilderness, feeding on the scraps of Google search engine results, for solutions to my Excel problems. But as of today, I am happy to say I am a new Chandoo member!
I exaggerate of course, I found a lot of resources over the last couple of years that proved helpful to me, but the source that proved to be more helpful than any other was Chandoo, so here I am.
Anyway, apologies in advance for doing my intro here, but I don't currently have the 'New Thread' option in the forum for New Users. So, on to my Excel query....
Attached is a sample spreadsheet containing captions found on the Statement of Financial Position / Balance Sheet. The logic of the layout is simple:
- GL Class 1 are the overarching captions
- GL Class 2 are sub-categories of GL Class 1
- GL Class 3 are sub-categories of GL Class 2
- GL Class 4 are sub-categories of GL Class 3
- GL Class 5 are sub-categories of GL Class 4
I would like to know if there is any (quick) way of vertically stacking unique caption descriptions into a single column, in such a way that results are produced according to the below logic. I've made a number of attempts with UNIQUE, SORT, VSTACK, HSTACK functions, but none of them giving me exactly what I need. I say 'quick' because I can achieve the desired result with helper columns etc. but this drags on my spreadsheet so ideally I'm looking to use the minimum number of functions in a singular column, to get this done.
- Formula operates from left to right, detecting if there is a unique value in that row that hasn't already appeared in the rows above
- It picks up any unique values and stacks them into the single column
- It then moves on to the next row and repeats the task i.e. scanning from left (GL Class 1) to right (GL Class 5) and stacking any new, unique values
I have included a [partial] example of what the result should look like in column I, with explanations of the logic in column J.
Last edited:
