Filtering for unique values across multiple rows

[mabndit49]

I need to be able to filter massive amounts of data across multiple rows (of varied column length) to remove all repetition in the data set.

Example is attached that has a set of data becoming a row of unique data.

Anyone have a smart and simple way to do this? By hand would we incredibly time intensive and innacurate.

Data Set
[TABLE="width: 830"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]ACD: Mike - AP5[/TD]
[TD]OJ: Ranger - PP1[/TD]
[TD]NB: Rome - 425[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NPS: Avery - I3[/TD]
[TD]ACD: Mike - AP5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NPS: Avery - I3[/TD]
[TD]ACD: Mike - AP5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NPS: Avery - I3[/TD]
[TD]OJ: Ranger - PP1[/TD]
[TD]UJ: XKCD - 87[/TD]
[TD]ACD: Mike - AP5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NPS: Avery - I3[/TD]
[TD]OJ: Ranger - PP1[/TD]
[TD]UJ: XKCD - 87[/TD]
[TD]ACD: Mike - AP5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OJ: Ranger - PP1[/TD]
[TD]UJ: XKCD - 87[/TD]
[TD]NB: Rome - 425[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OJ: Ranger - PP1[/TD]
[TD]UJ: XKCD - 87[/TD]
[TD]ACD: Mike - AP5[/TD]
[TD]NB: Rome - 425[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ideally becomes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACD: Mike - AP5[/TD]
[TD]OJ: Ranger - PP1[/TD]
[TD]NB: Rome - 425[/TD]
[TD]NPS: Avery - I3[/TD]
[TD]UJ: XKCD - 87
[/TD]
[/TR]
</tbody>[/TABLE]


Any good thoughts?
Thanks!

 

[Akashwani]

Hi and welcome to MrExcel.

A quick search on G**gle came up with this VBA method....
Atlas: Excel Training | Testing | Consulting

And a formula solution here....
Extract a unique distinct list from three columns in excel | Get Digital Help - Microsoft Excel resource

This is the result of the formula, it's not perfect (I can't get rid of the 0) and the result is in a column not a row, but I hope it gives you some ideas.
If nothing else, this has bumped you up and hopefully someone will provide the prefect solution for you.

Excel Workbook
	A	B	C	D	E	F
1	Data1	Data2	Data3	Data4		Result
2	ACD: Mike - AP5	OJ: Ranger - PP1	NB: Rome - 425			ACD: Mike - AP5
3	NPS: Avery - I3	ACD: Mike - AP5				NPS: Avery - I3
4	NPS: Avery - I3	ACD: Mike - AP5				OJ: Ranger - PP1
5	NPS: Avery - I3	OJ: Ranger - PP1	UJ: XKCD - 87	ACD: Mike - AP5		UJ: XKCD - 87
6	NPS: Avery - I3	OJ: Ranger - PP1	UJ: XKCD - 87	ACD: Mike - AP5		NB: Rome - 425
7	OJ: Ranger - PP1	UJ: XKCD - 87	NB: Rome - 425			0
8	OJ: Ranger - PP1	UJ: XKCD - 87	ACD: Mike - AP5	NB: Rome - 425
Sheet1

The formula in F2 needs to be entered with ctrl shift enter and NOT just enter, you can then copy it down until you get the 0 or a blank cell.

Good luck and I'm sorry this isn't the ideal solution for you.

Ak