I have attached an excel file with what I want to achieve. In excel it is pretty straightforward (see attached), and I suppose in Alteryx it will be too, if only I knew how to do it :-)
If possible, I would like your help with resolving this with a normal workflow first (although it feels like a batch macro would be a fit here).
The columns in yellow represent my table, it has a sequence number, a caption name, and a number identifying its place in the caption hierarchy. For each of the rows in the yellow table I want to be able to determine the caption of level just above it.
Eg, TOTAL CASH, level 4, i want to look for the name of the level just above it (3). It has to be the first immediate occurrence of 3, as there are several entries in the yellow table with 3 that wont correspond correctly.
The way I am doing this in excel is to have the range of the vlookup move with the rows in the table (so that it doesnt find the first value in the table, but, the next occurrence).
The Join and find and replace tools dont seem to be the tools here I have also tried the summation tool to get the first occurrence, but, I havent been successful.
Thanks for any guidance you can provide.
