CODEWITHSUNDEEP
excel
madhur
madhur

Reputation: 377

Excel: Group rows based on other 2 column parameters

I have 2 columns like this

maharashtra     mumbai
maharashtra     vashi
maharashtra     amravati
maharashtra     Panvel
Goa             panjim
Goa             magaon

I want to sort this like this in excel how should I proceed

maharashtra as one column                  Goa as another column
mumbai as row in maharashtra column        panjimas as row in goa column
vashias row in maharashtra column          magaon as row in goa column
amravati as row in maharashtra column 
panvel as row in maharashtra column

Upvotes: 0

Views: 114

Answers (1)

Scheballs
Scheballs

Reputation: 532

Lets say what you have there is A1 to B6

  • First thing would be to paste your desired headers in a new Row. Lets do D1:E1.
  • In column C2 and down type in a counting index list of 0 to 6 just for example purposes here
  • In D1 type in the following formula

=INDEX($A:$B,MATCH(VLOOKUP(D$1,$A:$B,2,FALSE),$B:$B,0)+$C2,2)

  • Now you should be able to drag this formula down and to the right and it will read the index count list and the header properly.

This will return a "0" if there is no value for the header after a certain row. You can add an if statement to make it blank instead of zero if you like.

-EXPLAINED:

Look at formulas from the inside out. We use Vlookup to tell us the first matching vaule for our header. NOTE* This will only work if your data is Sorted by column A and there are Unique values in column B for Column A. Now that we have the first value for our header we use MATCH to find out what row that is in, that will give us a number, then we add our index count list to Change the row number incrementally. Now we just INDEX the data for the row that the MATCH gave us. So as you drag down this formula it will look further down the list for matching values.

Hope this 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 and paste the above and use Text to Columns delimited by TAB to get them aligned into cells.

Upvotes: 1

Related Questions