Filter result from two columns to one - excel formula

[Vishaal]

HI, we want to filter data from two column to one as per result sheet

(Excel 2007 and Excel 2010)

Book1
	A	B	C
1	S.No.	Barc	Second
2	1
3	2	SQ351
4	3	SQ352
5	4	SQ353
6	5	SQ354
7	6		SQ354 - 1
8	7	SQ358
9	8	SQ354
10	9		SQ354 - 2
11	10		SQ354 - 3
12	11	SQ357
Sheet1

Result Sheet

Book1
	A
20	Final
21	SQ351
22	SQ352
23	SQ353
24	SQ354
25	SQ354 - 1
26	SQ354 - 2
27	SQ354 - 3
28	SQ357
29	SQ358
Sheet1

 

[alansidman]

Try using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MergeColumns = Table.CombineColumns(Source,{"Barc", "Second"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    SorteRows = Table.Sort(MergeColumns,{{"Merged", Order.Ascending}}),
    RemoveBlanks = Table.SelectRows(SorteRows, each ([Merged] <> "")),
    RemoveColumn = Table.SelectColumns(RemoveBlanks,{"Merged"}),
    RenameColumn = Table.RenameColumns(RemoveColumn,{{"Merged", "Final"}})
in
    RenameColumn

Book1
	A	B	C	D	E
1	S.No.	Barc	Second		Final
2	1				SQ351
3	2	SQ351			SQ352
4	3	SQ352			SQ353
5	4	SQ353			SQ354
6	5	SQ354			SQ354
7	6		SQ354 - 1		SQ354 - 1
8	7	SQ358			SQ354 - 2
9	8	SQ354			SQ354 - 3
10	9		SQ354 - 2		SQ357
11	10		SQ354 - 3		SQ358
12	11	SQ357
Sheet1

 

[Vishaal]

Thanks alansidman,

 

[alansidman]

You are welcome.