Excel: Group rows based on other 2 column parameters -

i have 2 columns this

maharashtra     mumbai maharashtra     vashi maharashtra     amravati maharashtra     panvel goa             panjim goa             magaon 

i want sort in excel how should proceed

maharashtra 1 column                  goa column mumbai row in maharashtra column        panjimas row in goa column vashias row in maharashtra column          magaon row in goa column amravati row in maharashtra column  panvel row in maharashtra column  

lets have there a1 b6

• first thing paste desired headers in new row. lets d1:e1.
• in column c2 , down type in counting index list of 0 6 example purposes here
• in d1 type in following formula

=index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c2,2)

• now should able drag formula down , right , read index count list , header properly.

this return "0" if there no value header after row. can add if statement make blank instead of 0 if like.

-explained:

look @ formulas inside out. use vlookup tell first matching vaule our header. note* work if data sorted column , there unique values in column b column a. have first value our header use match find out row in, give number, add our index count list change row number incrementally. index data row match gave us. drag down formula further down list matching values.

hope helps. -scheballs

maharashtra mumbai index maharashtra goa maharashtra vashi 0 =index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c2,2) =index($a:$b,match(vlookup(e$1,$a:$b,2,false),$b:$b,0)+$c2,2) maharashtra amravati 1 =index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c3,2) =index($a:$b,match(vlookup(e$1,$a:$b,2,false),$b:$b,0)+$c3,2) maharashtra panvel 2 =index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c4,2) =index($a:$b,match(vlookup(e$1,$a:$b,2,false),$b:$b,0)+$c4,2) goa panjim 3 =index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c5,2) =index($a:$b,match(vlookup(e$1,$a:$b,2,false),$b:$b,0)+$c5,2) goa magaon 4 =index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c6,2) =index($a:$b,match(vlookup(e$1,$a:$b,2,false),$b:$b,0)+$c6,2) 5 =index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c7,2) =index($a:$b,match(vlookup(e$1,$a:$b,2,false),$b:$b,0)+$c7,2) 6 =index($a:$b,match(vlookup(d$1,$a:$b,2,false),$b:$b,0)+$c8,2) =index($a:$b,match(vlookup(e$1,$a:$b,2,false),$b:$b,0)+$c8,2)

copy , paste above , use text columns delimited tab them aligned cells.