Split and group values in excel

[nagasree]

Hi I have a column of values which has different suffix after a dot, i need it to group it based on the value after dot. Example i need to split all values that end with .pdf into one column, values with.xls as another column, etc,,,

how to do this is my doubt.

 

[James006]

Hi,

With your data located in range A1:A10, you could test following Array Formula in cell D1:

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(RIGHT($A$1:$A$10,3)="pdf",MATCH(ROW($A$1:$A$10),ROW($A$1:$A$10)),""),ROWS(C1:$C$1))),"")

Hope this will help

 

[awoohaw]

THere is probably a much cleaner way, but this is two formulas.

WorkBook1.xlsx
	A	B	C	D	E
1
2	document		pdf	dws	xls
3	abc.pdf		abc.pdf	adc.dws	def.xls
4	adc.dws		def.pdf	ghi.dws
5	def.pdf		ghi.pdf
6	def.xls
7	ghi.pdf
8	ghi.dws
Sheet3

Cell Formulas
Range	Formula
C2:E2	C2	=TRANSPOSE(UNIQUE(SUBSTITUTE($A$3:$A$8,LEFT($A$3:$A$8,FIND(".",$A$3:$A$8)),"")))
C3:C5,E3,D3:D4	C3	=FILTER($A$3:$A$8,ISNUMBER(FIND(C$2,$A$3:$A$8)))
Dynamic array formulas.

 

[Fluff]

@awoohaw The OP's profile shows they are using 2019 & so do not have those functions.

 

[nagasree]

@awoohaw The OP's profile shows they are using 2019 & so do not have those functions.

exactly

 

[James006]

@nagasree

Have you tested the formula posted in message #2 in cell C1 ? ... then copy down and right ... and adjust for extensions ...

 

[James006]

@nagasree

Out of curiosity, have you tested the array formula ...

 

[alansidman]

an alternative is power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "document", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"document.1", "document.2"}),
    #"Duplicated Column" = Table.AddColumn(#"Split Column by Delimiter", "document.2 - Copy", each [document.2], type text),
    #"Inserted Merged Column" = Table.AddColumn(#"Duplicated Column", "Merged", each Text.Combine({[document.1], [document.2]}, "."), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"document.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"document.2 - Copy"]), "document.2 - Copy", "Merged"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"document.1"})
in
    #"Removed Columns1"

 

[nagasree]

@nagasree

Out of curiosity, have you tested the array formula ...

Hi, sorry for the late reply, yes all the solutions listed above works, Thank a lot for your help

 

[nagasree]

Can u help me this query VBA to remove all kinds of duplicates in excel