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.

[srk0609]

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

[srk0609]

Hi Ira,

Can you please explain how is the multi formula working?

How is it filling new section with country name?

[IraWatt]

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.

[srk0609]

Thank you for solution, Ira