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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Week​

[/td][td]

Supplier​

[/td][td]

Missed colection​

[/td][td][/td][td]

Week​

[/td][td]

Missed colection​

[/td][td]

Count Unique​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Week 1​

[/td][td]

SU1​

[/td][td]

YES​

[/td][td][/td][td]

Week 1​

[/td][td]

YES​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Week 1​

[/td][td]

SU2​

[/td][td]

YES​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Week 1​

[/td][td]

SU3​

[/td][td]

YES​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Week 1​

[/td][td]

SU1​

[/td][td]

YES​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Week 1​

[/td][td]

SU4​

[/td][td]

NO​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Week​

[/TD]
[TD]

Supplier​

[/TD]
[TD]

Missed colection​

[/TD]
[TD][/TD]
[TD]

Week​

[/TD]
[TD]

Missed colection​

[/TD]
[TD]

Count Unique​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Week 1​

[/TD]
[TD]

SU1​

[/TD]
[TD]

YES​

[/TD]
[TD][/TD]
[TD]

Week 1​

[/TD]
[TD]

YES​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Week 1​

[/TD]
[TD]

SU2​

[/TD]
[TD]

YES​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Week 1​

[/TD]
[TD]

SU3​

[/TD]
[TD]

YES​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Week 1​

[/TD]
[TD]

SU1​

[/TD]
[TD]

YES​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Week 1​

[/TD]
[TD]

SU4​

[/TD]
[TD]

NO​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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,

[TABLE="width: 524"]
<colgroup><col width="204" style="width:153pt"> <col width="64" style="width:48pt" span="5"> </colgroup><tbody>[TR]
[TD="width: 204"]SUPPLIER[/TD]
[TD="width: 64"]Week[/TD]
[TD="width: 64"]Pick up day[/TD]
[TD="width: 64"]Agreed Pickedup day[/TD]
[TD="width: 64"]Delay[/TD]
[TD="width: 64"]misscollection[/TD]
[/TR]
[TR]
[TD="class: xl65"]SU1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl65"]X[/TD]
[/TR]
[TR]
[TD]SU2[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SU3[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SU4[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SU5[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SU6[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SU7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SU8[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SU9[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU10[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU11[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU12[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU13[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU14[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU15[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU17[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU18[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]SU1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl65"]X[/TD]
[/TR]
[TR]
[TD]SU20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU22[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]SU24[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SU1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl65"]X[/TD]
[/TR]
[TR]
[TD]SU26[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]SU1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl65"]X[/TD]
[/TR]
</tbody>[/TABLE]


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:
[TABLE="width: 507"]
<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>[TR]
[TD="width: 211"]SU[/TD]
[TD="width: 64"]Week[/TD]
[TD="width: 168"]Missed collection[/TD]
[TD="width: 64"]RESULT[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU2[/TD]
[TD="class: xl100, align: right"]22[/TD]
[TD="class: xl100"]X[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU3[/TD]
[TD="class: xl21, align: right"]21[/TD]
[TD="class: xl21"]X[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU4[/TD]
[TD="class: xl100, align: right"]21[/TD]
[TD="class: xl100"]X[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU5[/TD]
[TD="class: xl21, align: right"]23[/TD]
[TD="class: xl21"]X[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU6[/TD]
[TD="class: xl100, align: right"]23[/TD]
[TD="class: xl100"]X[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU7[/TD]
[TD="class: xl21, align: right"]23[/TD]
[TD="class: xl21"]X[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU1[/TD]
[TD="class: xl100, align: right"]20[/TD]
[TD="class: xl100"]X[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU1[/TD]
[TD="class: xl21, align: right"]20[/TD]
[TD="class: xl21"]X[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU1[/TD]
[TD="class: xl100, align: right"]20[/TD]
[TD="class: xl100"]X[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU1[/TD]
[TD="class: xl21, align: right"]20[/TD]
[TD="class: xl21"]X[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl100"]SU1[/TD]
[TD="class: xl100, align: right"]20[/TD]
[TD="class: xl100"]X[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl21"]SU8[/TD]
[TD="class: xl21, align: right"]20[/TD]
[TD="class: xl21"]X[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
Agata

 

[Marcelo Branco]

Do you want something like this?


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

SU​

[/td][td]

Week​

[/td][td]

Missed collection​

[/td][td][/td][td]

Week​

[/td][td]

Missed colection​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

SU2​

[/td][td]

22​

[/td][td]

X​

[/td][td][/td][td]

20​

[/td][td]

x​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

SU3​

[/td][td]

21​

[/td][td]

X​

[/td][td][/td][td]

21​

[/td][td]

x​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

SU4​

[/td][td]

21​

[/td][td]

X​

[/td][td][/td][td]

22​

[/td][td]

x​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

SU5​

[/td][td]

23​

[/td][td]

X​

[/td][td][/td][td]

23​

[/td][td]

x​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

SU6​

[/td][td]

23​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

SU7​

[/td][td]

23​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

SU1​

[/td][td]

20​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

SU1​

[/td][td]

20​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

SU1​

[/td][td]

20​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

SU1​

[/td][td]

20​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

SU1​

[/td][td]

20​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

SU8​

[/td][td]

20​

[/td][td]

X​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.