CODEWITHSUNDEEP
excelexcel-formulasplitgrouping
Oalvinegro
Oalvinegro

Reputation: 470

Split columns by groups in Excel

I have a list of groups, subgroups and items in one single column that I would to split in three columns. as the example below:

Class, order and family | Quant.
1. Mammals              | 10
1.1 Primates            | 6
1.1.1 Lemuridae         | 4
1.1.2 Lorisidae         | 2
1.2 Carnivora           | 4
1.2.1 Felidae           | 3
1.2.2 Hyaenidae         | 1

I would like to split in columns following the number order, like that:

Class              | Order        | Family           | Quant.
1. Mammals         | 1.1 Primates | 1.1.1 Lemuridae  | 4
1. Mammals         | 1.1 Primates | 1.1.2 Lorisidae  | 2
1. Mammals         | 1.2 Carnivora| 1.2.1 Felidae    | 3
1. Mammals         | 1.2 Carnivora| 1.2.2 Hyaenidae  | 1

I already separeted numbers from text using RIGHT function, but I do not know what to do next.

Upvotes: 0

Views: 97

Answers (2)

basic
basic

Reputation: 11968

Edit for multi digit indexes.

Assuming the source table is in A1:B15 and the result table in E1:H15, the following formulas work:

  1. for Class (E2)

    =IFERROR(INDEX($A$1:$A$15,AGGREGATE(15,6,1/(((LEFT(F2,FIND(".",F2)) & " ") = LEFT($A$1:$A$15,FIND(".",F2)+1)))*ROW($A$1:$A$15),1)),"")

  2. for Order (F2)

    =IFERROR(INDEX($A$1:$A$15,AGGREGATE(15,6,1/(((LEFT(G2,FIND(CHAR(1),SUBSTITUTE(G2,".", CHAR(1),2))-1) & " ")=LEFT($A$1:$A$15,FIND(CHAR(1), SUBSTITUTE(G2,".",CHAR(1),2)))))*ROW($A$1:$A$15),1)),"")

  3. for Family (G2)

    =IFERROR(INDEX($A$1:$A$15,AGGREGATE(15,6,1/((LEN($A$1:$A$15)-LEN(SUBSTITUTE($A$1:$A$15,".",""))=2)*(COUNTIF($G$1:G1,$A$1:$A$15)=0))*ROW($A$1:$A$15),1)),"")

  4. for Quantity (H2)

    =IFERROR(VLOOKUP(G2,$A$1:$B$15,2),"")

enter image description here

Upvotes: 1

Steven Byrne
Steven Byrne

Reputation: 134

simplest way i can think off is, if the data above is in cells A:B then the following to be entered in Cells D:G (Headers in first row): -

D: - =IF(SEARCH(" ",A2,1)=3,A2,D1) E: - =IF(SEARCH(" ",A4,1)=4,A4,E3) F: =IF(SEARCH(" ",A4,1)=6,A4,F3) You will then have to delete the first couple of rows for each change in Class/Order.
G: to have a = to the Quantity. Hope this helps.

Note this way would only work if no number exceeds single digits, if it does let me know and i will make it a bit more intelligent :)

Upvotes: 0

Related Questions