Batch Macro for Data Cleansing

[kathleenanne]

I need to create separate outputs per ID. But before I do that, I need to remove all the columns with null values, which is different for each row. A colleague shared a batch macro to use for the data cleansing, but it's not working and I'm not sure how to fix it.

Current:

Product	ID	Timestamp1	Prev Identifier	Market	Venue
A	123456	Null	-1	101	Null
B	234567	2021-07-07	Null	ABCD	Null
B	234567	2021-07-07	Null	ABCD	Null

Desired:

Output 1

Product	ID	Prev Identifier	Market
A	123456	-1	101

Output 2

Product	ID	Timestamp1	Market
B	234567	2021-07-07	ABCD
B	234567	2021-07-07	ABCD

[atcodedog05]

Hi @kathleenanne 

The issue is changing column hence they cannot be grouped under single table.

Is the output 1 and output 2 are written to an output file seperatley. How is the output used?

[kathleenanne]

Yes Output 1 and 2 need to be on separate files. And that is the end of the process.

[atcodedog05]

Hi @kathleenanne 

Thank you for the clarification.

Workflow:

Macro:

Here i am writing each IDs to its own sheet. Please check the output file data cleanse.xlsx.

Hope this helps : )

[kathleenanne]

Thanks for this.

I looked at the macro, and saw you used a filter tool for the IDs. I'm sorry I should have clarified that there can be instances when there are more than 2 IDs. Will the macro still work in that case?

[atcodedog05]

Hi @kathleenanne 

The filter value will be dynamically changed on each batch iteration. And it should support multiple Ids.

[atcodedog05 · Accepted solution]

Hi @kathleenanne 

Here is an example with 3 ids. Refer the highlighted output log.

Workflow:

Hope this helps : )

[kathleenanne]

Thanks so much @atcodedog05! The iterations worked for multiple IDs 🙂

[atcodedog05]

Happy to help : ) @kathleenanne 

Cheers and have a nice day!