Formula to Remove Duplicates from List... IF

[tobermory]

Hello there,

I know you can remove duplicates with the 'Remove Duplicates' tab, however is there a way to do it formula wise, including an if statement?

For instance, in the dataset below, I need the unique values for each fruit. So, for instance, the unique values for Oranges would be 1, 2 and 3.

Apples - 1, 2, 3, 4, 5
Pears - 1, 2

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Col A[/TD]
[TD="class: xl65, width: 64"]Col B[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apples[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apples[/TD]
[TD="class: xl65, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apples[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apples[/TD]
[TD="class: xl65, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apples[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]Apples[/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]Oranges[/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pears[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to achieve this?

Many Thanks

 

[Joyner]

Hello, can you use a Pivot Table?

Just add both Column fields to the Rows Area and you have it.

 

[tobermory]

Hello, can you use a Pivot Table?

Just add both Column fields to the Rows Area and you have it.

Thanks for your reply.

Yes I'm aware of PivotTables and how to do this, however for purpose of what I'm trying to achieve a formula'ed version would be more beneficial as I would be using the formula fairly regularly and would like it to update automatically rather than having to refresh the PivotTable all the time.

 

[Joyner]

Ok, then I think you need a bit more information
For your output you show:
Apples - 1, 2, 3, 4, 5
Pears - 1, 2
Do you expect to have this is one cell, just like you show,or is each item in a separate cell across a row or can it be in two columns etc.?
What version of Office / Excel do you have?
If 2016, which version?
I believe you will be looking at a fairly complex array formula. If I had a lot of data I would go with the Pivot Table / Power Query solution and use a macro to automatically update itif that was important.
For the array formula, Excelisfun has a lot of videos that show some complicated formulas to extract unique lists, like here for several examples:
https://www.youtube.com/watch?v=J6yeTUFkLzQ

 

[Peter_SSs]

Since your column B values are numeric, you could try this. Formula in E2 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G
1	Col A	Col B			Oranges	Apples	Pears
2	Oranges	2			1	1	1
3	Pears	1			2	2	2
4	Pears	1			3	3
5	Oranges	1				4
6	Oranges	1				5
7	Apples	2
8	Apples	4
9	Apples	1
10	Pears	1
11	Pears	2
12	Oranges	1
13	Oranges	2
14	Apples	4
15	Apples	5
16	Apples	3
17	Oranges	3
18	Pears	1
19	Pears	1
Unique Values