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

	A	B	C	D	E
1			9
2	Number	Group		Number	Group
3	26673	VDP-VDW		26673	VDP-VDW
4	26673	VDP-VROR		26673	VDP-VROR
5	26673	VDP-VROR		26520	TTMS
6	26520	TTMS		26520	VDP-VDW
7	26520	VDP-VDW		26520	VDP-VROR
8	26520	VDP-VDW		26544	CPS
9	26520	VDP-VROR		26544	Customer Central
10	26520	VDP-VROR		26490	CAD AMS
11	26544	CPS		26493	TFAD
12	26544	Customer Central
13	26544	CPS
14	26490	CAD AMS
15	26493	TFAD
16	26493	TFAD

<tbody>
</tbody>

 

[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

-	A​
1​	Number Group
2​	26673 VDP-VDW
3​	26673 VDP-VROR
4​	26673 VDP-VROR
5​	26520 TTMS
6​	26520 VDP-VDW
7​	26520 VDP-VDW
8​	26520 VDP-VROR
9​	26520 VDP-VROR
10​	26544 CPS
11​	26544 Customer Central
12​	26544 CPS
13​	26490 CAD AMS
14​	26493 TFAD
15​	26493 TFAD

<tbody>
</tbody>

rinaldoskvr

<tbody>
</tbody>

This formula

Using Excel 2007

-	B​
2​	=IFERROR(INDEX($A$2:$A$15, MATCH(0, COUNTIF($B$1:$B1,$A$2:$A$234), 0),1),"")

<tbody>
</tbody>

rinaldoskvr

<tbody>
</tbody>

Gives you this:

Using Excel 2007

-	A​	B​
1​	Number Group
2​	26673 VDP-VDW	26673 VDP-VDW
3​	26673 VDP-VROR	26673 VDP-VROR
4​	26673 VDP-VROR	26520 TTMS
5​	26520 TTMS	26520 VDP-VDW
6​	26520 VDP-VDW	26520 VDP-VROR
7​	26520 VDP-VDW	26544 CPS
8​	26520 VDP-VROR	26544 Customer Central
9​	26520 VDP-VROR	26490 CAD AMS
10​	26544 CPS	26493 TFAD
11​	26544 Customer Central
12​	26544 CPS
13​	26490 CAD AMS
14​	26493 TFAD
15​	26493 TFAD

<tbody>
</tbody>

rinaldoskvr

<tbody>
</tbody>

.
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

	A	B	C	D	E
1			9
2	Number	Group		Number	Group
3	26673	VDP-VDW		26673	VDP-VDW
4	26673	VDP-VROR		26673	VDP-VROR
5	26673	VDP-VROR		26520	TTMS
6	26520	TTMS		26520	VDP-VDW
7	26520	VDP-VDW		26520	VDP-VROR
8	26520	VDP-VDW		26544	CPS
9	26520	VDP-VROR		26544	Customer Central
10	26520	VDP-VROR		26490	CAD AMS
11	26544	CPS		26493	TFAD
12	26544	Customer Central
13	26544	CPS
14	26490	CAD AMS
15	26493	TFAD
16	26493	TFAD

<tbody>
</tbody>

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 !!