Remove extra Rows and Columns

Hi - I have a table that will be always in the same format. However, the position of the table in excel can vary (e.g. could be random # of rows on top or columns to the left.) Does anyone have a sample workflow that can dynamically remove unneeded rows and columns so that the data starts with the table?

Example excel attached. I would like the fields to be Sample Number, Property Type, and Tenant Name

Rows and Columns remove.xlsx

Preparation

Accepted answers

danrh

Attached is a solution that gets you halfway there. It finds where "Sample number" is in the data and grabs everything to the right and below of that. Unfortunately, it looks like you have additional text below the table. Is there some sort of pattern at the bottom/on the right side that would let us filter out that noise as well?

solution.yxmd

All comments

rarmstrong

When I try it in Alteryx, it automatically starts the table where it should, no matter how many NULL columns/rows I have to the left or above the table.

If it isn't doing that for you, then you can give this a try:

- In the input data tool, select First Row Contains Data

- Add Auto Field Tool (this changes fields that are all NULL to boolean)

- Add Dynamic Select Tool. In the drop down, select 'Select Field Types' and deselect Bool

(At this point, all fields that contain only NULL cells should be removed)

To remove NULL rows:

- Add Dynamic Rename tool. In the Rename Mode dropdown, select Formula. Then make sure all fields are selected, including the Dynamic or Unknown Fields box

The expression should be 1 (this will change the name of the 3 fields that you have left over to 1, 1_2, and 1_3)

- Add a Filter tool. The expression should be:

!IsNull([1])
AND
!IsNull([1_2])
AND
!IsNull([1_3])

- Add another Dynamic Rename tool and in the rename mode select 'Take Field Names from First Row of Data'. Again, make sure all fields are selected including the Dynamic or Unknown Fields box.

I attached a copy of the sample workflow I created

remove rows and columns.yxmd

cireost

Great thank you.

Would this still work if the rows and columns that are unneeded have some junk data in there? I ask because this can occur (apologies for not mentioning it originally) and therefore, not sure if In the input data tool, select First Row Contains Data would work in these situations.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703