Remove Duplicates with multiple criteria --Very Urgent, Need help

[rinaldoskvr]

Number Group
26673 VDP-VDW
26673 VDP-VROR
26673 VDP-VROR
26520 TTMS
26520 VDP-VDW
26520 VDP-VDW
26520 VDP-VROR
26520 VDP-VROR
26544 CPS
26544 Customer Central
26544 CPS
26490 CAD AMS
26493 TFAD
26493 TFAD




Above is my raw data, I want to remove the duplicate Numbers at "Group" level.


Criteria: If the "Numbers" are duplicated, then it should check whether that particular number is repeated more than once with same "Group", i.e.,Each "Group" should have only one unique "Number".


After removing duplicates at group level, I need the result like below,

Number Group
26673 VDP-VDW
26673 VDP-VROR
26520 TTMS
26520 VDP-VDW
26520 VDP-VROR
26544 CPS
26544 Customer Central
26490 CAD AMS
26493 TFAD




Please help me with any formula to get the result. Appreciate your help very much.

 

[Trevor G]

Welcome to the forum, look at using Advanced Filter or Remove Duplicates.

 

[Weazel]

maybe something like....

C1=SUM(IF(FREQUENCY(IF(A3:A16<>"",MATCH(A3:A16&B3:B16,A3:A16&B3:B16,0)),ROW(A3:A16)-ROW(A3)+1),1)) Control Shift Enter

D2=IF(ROWS($D$3:D3)>$C$1,"",INDEX($A$3:$A$16,SMALL(IF(FREQUENCY(IF($A$3:$A$16<>"",MATCH($A$3:$A$16&$B$3:$B$16,$A$3:$A$16&$B$3:$B$16,0)),ROW($A$3:$A$16)-ROW($A$3)+1),ROW($A$3:$A$16)-ROW($A$3)+1),ROWS($D$3:D3)))) Control Shift Enter

E2=IF(ROWS($E$3:E3)>$C$1,"",INDEX($B$3:$B$16,SMALL(IF(FREQUENCY(IF($A$3:$A$16<>"",MATCH($A$3:$A$16&$B$3:$B$16,$A$3:$A$16&$B$3:$B$16,0)),ROW($A$3:$A$16)-ROW($A$3)+1),ROW($A$3:$A$16)-ROW($A$3)+1),ROWS($D$3:E3)))) Control Shift Enter

[TABLE="class: grid, width: 446"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD="align: right"]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Number[/TD]
[TD]Group[/TD]
[TD][/TD]
[TD]Number[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VDW[/TD]
[TD][/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VDW[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VROR[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26520[/TD]
[TD]TTMS[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]26520[/TD]
[TD]TTMS[/TD]
[TD][/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VDW[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VDW[/TD]
[TD][/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VROR[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VDW[/TD]
[TD][/TD]
[TD="align: right"]26544[/TD]
[TD]CPS[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26544[/TD]
[TD]Customer Central[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26490[/TD]
[TD]CAD AMS[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]26544[/TD]
[TD]CPS[/TD]
[TD][/TD]
[TD="align: right"]26493[/TD]
[TD]TFAD[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]26544[/TD]
[TD]Customer Central[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]26544[/TD]
[TD]CPS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]26490[/TD]
[TD]CAD AMS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]26493[/TD]
[TD]TFAD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]26493[/TD]
[TD]TFAD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DocAElstein]

Hi,
. Welcome to the Board.
. Please get clued up on the Board, read the rules, check out “The tools” in my signature..
. Try to avoid things like “Urgent” in the Title.

. There are lots of Thread on what you want.. so search first

. Anyways while I’m ‘ere as I did this as part of a bigger Formula Yesterday….. applied to your data….

…Using Excel 2007
[TABLE="class: grid"]
<tbody>[TR]
[TD]-[/TD]
[TD]

A​

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

1​

[/TD]
[TD]Number Group[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]26673 VDP-VDW[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]26673 VDP-VROR[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]26673 VDP-VROR[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]26520 TTMS[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]26520 VDP-VDW[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]26520 VDP-VDW[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]26520 VDP-VROR[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]26520 VDP-VROR[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]26544 CPS[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]26544 Customer Central[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]26544 CPS[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]26490 CAD AMS[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]26493 TFAD[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]26493 TFAD[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]rinaldoskvr [/TD]
[/TR]
</tbody>[/TABLE]

This formula

Using Excel 2007
[TABLE="class: grid"]
<tbody>[TR]
[TD]-[/TD]
[TD]

B​

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

2​

[/TD]
[TD]=IFERROR(INDEX($A$2:$A$15, MATCH(0, COUNTIF($B$1:$B1,$A$2:$A$234), 0),1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]rinaldoskvr [/TD]
[/TR]
</tbody>[/TABLE]
Gives you this:

Using Excel 2007
[TABLE="class: grid"]
<tbody>[TR]
[TD]-[/TD]
[TD]

A​

[/TD]
[TD]

B​

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

1​

[/TD]
[TD]Number Group[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]26673 VDP-VDW[/TD]
[TD]26673 VDP-VDW[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]26673 VDP-VROR[/TD]
[TD]26673 VDP-VROR[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]26673 VDP-VROR[/TD]
[TD]26520 TTMS[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]26520 TTMS[/TD]
[TD]26520 VDP-VDW[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]26520 VDP-VDW[/TD]
[TD]26520 VDP-VROR[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]26520 VDP-VDW[/TD]
[TD]26544 CPS[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]26520 VDP-VROR[/TD]
[TD]26544 Customer Central[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]26520 VDP-VROR[/TD]
[TD]26490 CAD AMS[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]26544 CPS[/TD]
[TD]26493 TFAD[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]26544 Customer Central[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]26544 CPS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]26490 CAD AMS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]26493 TFAD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]26493 TFAD[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]rinaldoskvr [/TD]
[/TR]
</tbody>[/TABLE]

.
They are CSE Formula things. In this case you "CSE" it in cell B" then drag it down.. If you don’t know what that is… check out the detailed explanations here:
.. ( it is where I Lifted this formula from … )

http://www.mrexcel.com/forum/excel-...-column-b-populate-column-c.html?#post4151992

 

[DocAElstein]

maybe something like....

C1=SUM(IF(FREQUENCY(IF(A3:A16<>"",MATCH(A3:A16&B3:B16,A3:A16&B3:B16,0)),ROW(A3:A16)-ROW(A3)+1),1)) Control Shift Enter

D2=IF(ROWS($D$3:D3)>$C$1,"",INDEX($A$3:$A$16,SMALL(IF(FREQUENCY(IF($A$3:$A$16<>"",MATCH($A$3:$A$16&$B$3:$B$16,$A$3:$A$16&$B$3:$B$16,0)),ROW($A$3:$A$16)-ROW($A$3)+1),ROW($A$3:$A$16)-ROW($A$3)+1),ROWS($D$3:D3)))) Control Shift Enter

E2=IF(ROWS($E$3:E3)>$C$1,"",INDEX($B$3:$B$16,SMALL(IF(FREQUENCY(IF($A$3:$A$16<>"",MATCH($A$3:$A$16&$B$3:$B$16,$A$3:$A$16&$B$3:$B$16,0)),ROW($A$3:$A$16)-ROW($A$3)+1),ROW($A$3:$A$16)-ROW($A$3)+1),ROWS($D$3:E3)))) Control Shift Enter.........

Hey Weazel,
. That is a brilliant Alternative...
. I was searching like mad last week for those sorts of formulers...

http://www.mrexcel.com/forum/excel-...iate-some-help-form-board-formula-master.html

. Can you tell me where you got that and if or you have a good explanation for how it "works"

. Thanks,
. Alan

 

[Weazel]

Hi Doc,

I learned most of the concepts of how the formula works from the Excelisfun youtube channel as well as here in the forum.

If you search the channel for Extract Unique and Count Unique Mike has some good in depth explanations which are far better than I can do.

Also Mike Girvins' Control Shift Enter book is great too.

 

[DocAElstein]

.......If you search .....
Also Mike Girvins' Control Shift Enter book is great too.

Thanks
Alan

 

[rinaldoskvr]

maybe something like....

C1=SUM(IF(FREQUENCY(IF(A3:A16<>"",MATCH(A3:A16&B3:B16,A3:A16&B3:B16,0)),ROW(A3:A16)-ROW(A3)+1),1)) Control Shift Enter

D2=IF(ROWS($D$3:D3)>$C$1,"",INDEX($A$3:$A$16,SMALL(IF(FREQUENCY(IF($A$3:$A$16<>"",MATCH($A$3:$A$16&$B$3:$B$16,$A$3:$A$16&$B$3:$B$16,0)),ROW($A$3:$A$16)-ROW($A$3)+1),ROW($A$3:$A$16)-ROW($A$3)+1),ROWS($D$3:D3)))) Control Shift Enter

E2=IF(ROWS($E$3:E3)>$C$1,"",INDEX($B$3:$B$16,SMALL(IF(FREQUENCY(IF($A$3:$A$16<>"",MATCH($A$3:$A$16&$B$3:$B$16,$A$3:$A$16&$B$3:$B$16,0)),ROW($A$3:$A$16)-ROW($A$3)+1),ROW($A$3:$A$16)-ROW($A$3)+1),ROWS($D$3:E3)))) Control Shift Enter

[TABLE="class: grid, width: 446"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD="align: right"]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Number[/TD]
[TD]Group[/TD]
[TD][/TD]
[TD]Number[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VDW[/TD]
[TD][/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VDW[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VROR[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]26673[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26520[/TD]
[TD]TTMS[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]26520[/TD]
[TD]TTMS[/TD]
[TD][/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VDW[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VDW[/TD]
[TD][/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VROR[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VDW[/TD]
[TD][/TD]
[TD="align: right"]26544[/TD]
[TD]CPS[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26544[/TD]
[TD]Customer Central[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]26520[/TD]
[TD]VDP-VROR[/TD]
[TD][/TD]
[TD="align: right"]26490[/TD]
[TD]CAD AMS[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]26544[/TD]
[TD]CPS[/TD]
[TD][/TD]
[TD="align: right"]26493[/TD]
[TD]TFAD[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]26544[/TD]
[TD]Customer Central[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]26544[/TD]
[TD]CPS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]26490[/TD]
[TD]CAD AMS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]26493[/TD]
[TD]TFAD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]26493[/TD]
[TD]TFAD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi Weazel,

Works flawlessly, you are Genius !!.
I was lazy even to post this question in this forum, but you are really great to take courage and answer this..
Thank you so so much !! Now on am ur big fan !!!.

Am very much interested in Advanced macros, Please suggest me a good source(video tutorial).

Regards,
Rinaldo

 

[Weazel]

You're welcome, glad its working for you.

I'm not very well versed in VBA and Macros but I've gone through a few videos from the ExcelVBAISFUN youtube channel and also YourProgrammingNetwork site and youtube channel YourProgrammingNetwork

Hopefully that helps

 

[rinaldoskvr]

You're welcome, glad its working for you.

I'm not very well versed in VBA and Macros but I've gone through a few videos from the ExcelVBAISFUN youtube channel and also YourProgrammingNetwork site and youtube channel YourProgrammingNetwork

Hopefully that helps

Again Thank you !!