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

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!

Dataset.xlsx

Example.yxmd

ResultingTable.xlsx

Workflow

Input

Transformation

Iterative Macro

Accepted answers

LordNeilLord

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

Data Lover

Example (1).yxmd

All comments

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

Data Lover

Example (1).yxmd

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

Dataset.xlsx

rvanderhave

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703