Extract data from middle of Excel with multiple categories

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.

All comments

Hey @srk0609,

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

Please ask if any questions

HTH,

Ira

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.

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

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

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.

Thank you so much for prompt response. I will try what you suggested and mark it as solution as soon as I get result.

Could you please explain the code in multi formula? how is it working? Thank you

Hi Ira,

Can you please explain how is the multi formula working?

How is it filling new section with country name?

Hey @srk0609

what the multi row formula does is go line by line applying the logic in the formula bar. This formula checks the row above's Australia column, if its value is "Name" Then take the use the value of the Australia column in the current row. Else just take the value of this new column from the row above. The first row the row above is NULL so that's why the initial new column is filled with NULLS.

Hope that made some sense, there are some great videos on multi row formula on the community if you want to check them out.