Conditional Removal of Duplicates from Excel

[agatina11]

Hey,

I'd appreciate help with 1 excel spreadsheet and duplicate values that I would like to remove.

I've got information about some collections at specific suppliers for each week of the year.

Example

Week Supplier Missed collection
Week 1 SU1 YES
Week 1 SU2 YES
Week 1 SU3 YES
Week 1 SU1 YES

So if I use the countifs function I would get 4 missed collection, however, SU1 is repeated 2 times. I am not sure if I can remove duplicates as that would remove that SU from other weeks on that report ( from a query). Unless there is an option to remove the duplicated suppliers form a specific week

Thanks in advance

Agata

 

[Marcelo Branco]

Welcome to Mr Excel forum

Do you really want to remove the duplicates or just count unique rows depending on conditions? If the former try Data > Remove duplicates; if the later you can use a formula to count unique without removing the duplicates.
Please, clarify.


M.

 

[agatina11]

Welcome to Mr Excel forum

Do you really want to remove the duplicates or just count unique rows depending on conditions? If the former try Data > Remove duplicates; if the later you can use a formula to count unique without removing the duplicates.
Please, clarify.


M.

Hi,

thanks for a swift reply. As it's a table ran from a query, I'm not able to remove duplicates.

Is there a formula to count unique values ?

Thanks
Agata

 

[Marcelo Branco]

You need an array formula

Something like this

	A​	B​	C​	D​	E​	F​	G​
1​	Week​	Supplier​	Missed colection​		Week​	Missed colection​	Count Unique​
2​	Week 1​	SU1​	YES​		Week 1​	YES​	3​
3​	Week 1​	SU2​	YES​
4​	Week 1​	SU3​	YES​
5​	Week 1​	SU1​	YES​
6​	Week 1​	SU4​	NO​
7​

Criteria in E2:F2

Array formula in G2
=SUM(IF(FREQUENCY(IF(A2:A10=E2,IF(C2:C10=F2,MATCH(B2:B10,B2:B10,0))),ROW(B2:B10)-ROW(B2)+1),1))
confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

Hope this helps

M.

 

[agatina11]

You need an array formula

Something like this

	A​	B​	C​	D​	E​	F​	G​
1​	Week​	Supplier​	Missed colection​		Week​	Missed colection​	Count Unique​
2​	Week 1​	SU1​	YES​		Week 1​	YES​	3​
3​	Week 1​	SU2​	YES​
4​	Week 1​	SU3​	YES​
5​	Week 1​	SU1​	YES​
6​	Week 1​	SU4​	NO​
7​

<tbody>
</tbody>

Criteria in E2:F2

Array formula in G2
=SUM(IF(FREQUENCY(IF(A2:A10=E2,IF(C2:C10=F2,MATCH(B2:B10,B2:B10,0))),ROW(B2:B10)-ROW(B2)+1),1))
confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

Hope this helps

M.

Hi!

Thanks for help. However this didn't work for me. I get an error that there is a problem with this formula. Also I am taking that info from a few different sheets, and there are much more suppliers, so I want to have a unique count for any supplier that would appear. Not sure I explain myself good.

ANyhow! thanks for help

Best
Agata

 

[Marcelo Branco]

Agata,

The formula should have worked - it's a well known formula. What error have you gotten?
Try to show us a small data sample similar to your real scenario.

M.

 

[agatina11]

Agata,

The formula should have worked - it's a well known formula. What error have you gotten?
Try to show us a small data sample similar to your real scenario.

M.

HI M,

SUPPLIER	Week	Pick up day	Agreed Pickedup day	Delay	misscollection
SU1	22	2	2	0	X
SU2	22	2	2	0	2
SU3	22	2	2	0	2
SU4	22	2	2	0	2
SU5	22	2	2	0	2
SU6	22	2	2	0	2
SU7	22	2	2	0	2
SU8	22	2	2	0	2
SU9	22	3	3	0	3
SU10	22	3	3	0	3
SU11	22	3	3	0	3
SU12	22	3	3	0	3
SU13	22	3	3	0	3
SU14	22	3	3	0	3
SU15	22	3	3	0	3
SU16	22	3	3	0	3
SU17	22	3	3	0	3
SU18	22	3	3	0	3
SU1	22	3	3	0	X
SU20	22	3	3	0	3
SU21	22	3	3	0	3
SU22	22	3	3	0	3
SU23	22	3	3	0	3
SU24	22	3	3	0	3
SU1	22	6	3	0	X
SU26	22	3	3	0	3
SU1	22	3	3	0	X

<colgroup><col width="204" style="width:153pt"> <col width="64" style="width:48pt" span="5"> </colgroup><tbody>
</tbody>

So I'd like to count the missed collection, and total number should be 1, as the same supplier appears few times.

Cab you help? I can't get it working :?

Thanks!
Agata

 

[Marcelo Branco]

Assuming your data in A1:F28, headers in row 1 try this array formula
=SUM(IF(FREQUENCY(IF(F2:F28="x",MATCH(A2:A28,A2:A28,0)),ROW(A2:A28)-ROW(A2)+1),1))
Ctrl+Shift+Enter

M.

 

[agatina11]

HI Marcelo,
Thanks for help. How can I use the result ? I got as a result this:

SU	Week	Missed collection	RESULT
SU2	22	X	8
SU3	21	X	8
SU4	21	X	8
SU5	23	X	8
SU6	23	X	7
SU7	23	X	7
SU1	20	X	4
SU1	20	X	4
SU1	20	X	4
SU1	20	X	4
SU1	20	X	4
SU8	20	X	1

<colgroup><col width="211" style="width:158pt"> <col width="64" style="width:48pt"> <col width="168" style="width:126pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks!
Agata

 

[Marcelo Branco]

Do you want something like this?

	A​	B​	C​	D​	E​	F​	G​
1​	SU​	Week​	Missed collection​		Week​	Missed colection​	Result​
2​	SU2​	22​	X​		20​	x​	2​
3​	SU3​	21​	X​		21​	x​	2​
4​	SU4​	21​	X​		22​	x​	1​
5​	SU5​	23​	X​		23​	x​	3​
6​	SU6​	23​	X​
7​	SU7​	23​	X​
8​	SU1​	20​	X​
9​	SU1​	20​	X​
10​	SU1​	20​	X​
11​	SU1​	20​	X​
12​	SU1​	20​	X​
13​	SU8​	20​	X​
14​

Criteria in columns E:F

Array formula in G2 copied down
=SUM(IF(FREQUENCY(IF(B$2:B$13=E2,IF(C$2:C$13=F2,MATCH(A$2:A$13,A$2:A$13,0))),ROW(A$2:A$13)-ROW(A$2)+1),1))
Ctrl+Shift+Enter

M.