Using a batch macro for different sections of rows of a single sheet

[rvanderhave]

Hi all,

Unfortunately, I am not allowed to share my actual dataset, so I made a sample dataset (Dataset.xlsx) that is as similar as possible to my actual one. What you will see in the .xlsx file, is several categories on the same sheet underneath each other. What I am trying to do, is to create the table as can be found in ResultingTable.xlsx.

I understand how to do this per category, see for Category 1 the workflow Example.yxmd. However, creating the same workflow multiple times (5 times in this case) is not optimal, and therefore I want to create a batch macro that I can run for all categories. The attached workflow can easily be transformed in such a macro, but the main problem that occurs is that an increasingly different number of lines needs to be skipped and a different number of lines needs to be kept as first lines. Now my question is, how can I change the macro in such a way that it is able to overcome this problem? And more importantly, what should be the input of the macro in order to skip and keep different number of lines? 

Hopefully some of you have experienced such a problem and can provide me with a solution. Many thanks in advance! 

[LordNeilLord]

Hey @rvanderhave

I don't think you need to build a macro to achieve this, just a good old Multirow formula :)

Example attached

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[rvanderhave]

Hi @LordNeilLord,

Thanks a lot for your answer! I think it provides a good solution for the dataset that I attached. However, I made a small error in the sample dataset by making all the category names follow the format of 'Category X', while in my actual dataset, all the categories have a completely different name. Also, there are lines that do not contain data within the different categories (therefore I can't search for Null()'s), which makes it more complicated.

I have attached a (hopefully better) sample dataset. Could you please take a look again? 

Once again, thanks a lot!

Kind regards,

Robbert

[LordNeilLord · Accepted solution]

Hey @rvanderhave

No worries, try this instead:

IF IsNull([F2]) AND  !Isnull([Text1])  Then [Text1]
Elseif IsNull([F2]) and !IsNull([Row-1:Category]) Then [Row-1:Category] 

Else [Row-1:Category] 
Endif	

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[rvanderhave]

Hi @LordNeilLord,

Yes, this works indeed! I will implement this after the weekend and validate the performance of the workflow.

Thank you very much for your help. Have a great weekend!

Kind regards,

Robbert

[LordNeilLord]

Excellent!

Once you've validated it, I'd appreciate an accepted solution :)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome