Extract data from middle of Excel with multiple categories

[srk0609]

Hi, I want to extract data from middle of Excel sheet. I want to extract rows and columns only pertaining to AMERICA. The number of rows may vary it wont always be same number and there are some hidden rows(13,14,16,17) which should not be extracted. Can anyone help me?  I am attaching Excel file as well. Thank you.

[IraWatt]

Hey @srk0609,

You can use a multi row formula to group each section like this:

Please ask if any questions :)

HTH,

Ira

[srk0609]

Hi Ira,

Thank you for prompt reply. Could you please tell me how can I show this as Header? and is there a way to skip extracting hidden rows in excel? 4 rows that David, Joy, Joy, Smith are not supposed to be extracted. Please let me know.

[IraWatt]

No worries, to make them the headers just use a dynamic rename tool and select take headers from first row:

[IraWatt]

@srk0609 In terms of removing hidden rows, Alteryx removes all formatting when taking the data. Only two options are remove them in excel before hand or use the Alteryx python tool and use code to find which rows to remove.

[srk0609]

Ok, so we have to understand the reason why those rows are filtered and apply those filters once the data is in Alteryx, right?

[IraWatt]

Yeah absolutely @srk0609 ! if you can find the logic then definitely just do that in Alteryx

[IraWatt]

If you need any help with the logic please ask! If I've solved any of your problems make sure to leave a green tick :)

[srk0609]

Hi Ira,

if there is data in the first section how can i skip extracting that. I dont want to hard code skip 2 lines or 3 lines cos you never how many rows data would be present in first section. Could you please help with this issue? 

[IraWatt · Accepted solution]

Hey @srk0609,

the multi row formula creates a new column for that top section the value is NULL so you can use a filter to filter on only values which are not NULL.