excel sheet sub headings as columns

I am totally new to this kind of challenges and not sure any thing available ( not sure even on what base I have to search )

In the below excel s开发者_StackOverflow中文版heet image the column 'A' has headings in two places ( row numbers 2,3 and 9,10) . The actual excel sheet has more than six thousand rows and too many sub headings like this ( If it is small file I can do it manually.. but more than 6 thousand rows)

The challenge :- I want to populate E column with "Make" value and F column with " Model" from sub headings . Can I write any rule or macro to populate these columns ? could some one help me ? Thanks for your help

Image Link

or below

Regards

Kiran

If you want to do this solely in Excel you can use the following. This assumes

1. All Headings are the same for "S.No"
2. Change the SUBSTITUTE clause to match the text for Make and Model eg I have used exact spacing of "Model: " and "Make : " to match the spreadsheet and substitute with ""

In cell G5 Enter =IF(ISNUMBER(A5),IF(ISERROR(FIND("Model",A3,1)),MAX($G$1:G4),MAX($G$1:G4)+1),"")

In cell F5 Enter =SUBSTITUTE(IF(ISNUMBER(A5),INDIRECT(ADDRESS(MATCH($G5,$G:$G,0)-2,COLUMN(A1),1)),""),"Model: ","")

In cell E5 Enter = =SUBSTITUTE(IF(ISNUMBER(A5),INDIRECT(ADDRESS(MATCH($G5,$G:$G,0)-3,COLUMN(A1),1)),""),"Make : ","")

Then drag down the formula in E5:G5 to wherever you need. However, I only recommend using this once only as the formulae will be slow to update over large ranges. Also if your headings are out of sync then VBA is the way forward