How to read input excel file with repeated headers

[jesemmvilla]

Hi, 

I need your support on this question, as I'm not sure how to handle it. I have an excel file that contains several headers in the same sheet, this is because is separated by company, bank and contract. 

My idea is to transpose those fields into the lines as an aditional column. 

I'm attaching the excel file but the main idea is this:

Original format:

Bank: Bank Name 1
Company: Company Name 1
Currency 1
Date	Income	Outcome	Current Balance	Taxes
01/01/2019	100	10	90	30
01/02/2019	500	50	550	150
01/03/2019	100	100	200	60

Bank: Bank Name 2
Company: Company Name 2
Currency
Date	Income	Outcome	Current Balance	Taxes
01/01/2019	100	10	90	30
01/02/2019	500	50	550	150
01/03/2019	100	100	200	60

Desired format:

Bank	Company	Currency	Date	Income	Outcome	Current Balance	Taxes
Bank Name1	Company1	Currency1	01/01/2019	100	10	90	30
Bank Name1	Company1	Currency1	01/02/2019	500	50	550	150
Bank Name1	Company1	Currency1	01/03/2019	100	100	200	60
Bank Name2	Company2	Currency2	01/01/2019	100	10	90	30
Bank Name2	Company2	Currency2	01/02/2019	500	50	550	150
Bank Name2	Company2	Currency2	01/03/2019	100	100	200	60

Thanks in advance.

Emmanuel Villanueva

[echuong1 · Accepted solution]

You can use a multirow formula to "drag down" the headers until they change and then filter out the headers. See attached for an example. 

[jesemmvilla]

I appreciate the quick reply, this helps me to start working on the data, there is some additional step that I missed before, there's a contract number on each header, that this is the unique value for each bucket of headers/lines. 

How can I do the multirow formula with this? if the contract number is different on each row?

Thanks!