Remove rows with dublicate field values

A

Almeding

New Member
Joined
Aug 1, 2017
Messages
2
Hi,

I have two tables: one with all items from a ERP-system (2), and one with manual correction to the items lists product hierachy (1). The manual corrections (1) can hold items which also will be found in the item list table (2).

The two tables are joined in an UNION query. My problem is that in case the item is found in both tables I only want to keep records from table (1).

So this table:

[TABLE="width: 947"]
<colgroup><col width="67" style="width:50pt"> <col width="157" style="width:118pt" span="4"> <col width="44" style="width:33pt"> <col width="74" style="width:56pt"> <col width="134" style="width:101pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 67"]MinOfSYS[/TD]
[TD="class: xl67, width: 157"]FirstOfHierarchy 1[/TD]
[TD="class: xl67, width: 157"]FirstOfHierarchy 2[/TD]
[TD="class: xl67, width: 157"]FirstOfHierarchy 3[/TD]
[TD="class: xl67, width: 157"]FirstOfCOMPANY[/TD]
[TD="class: xl67, width: 44"]UNITS[/TD]
[TD="class: xl67, width: 74"]COST[/TD]
[TD="class: xl67, width: 134"]EAN[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]46,04553[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.272 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]49,163[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.289 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]50,7706[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.296 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]53,9078[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.302 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]46,04553[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.272 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]49,16308[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.289 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]50,77057[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.296 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]53,90778[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.302 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]96[/TD]
[TD="class: xl69, width: 74"]1002,82563[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.256.013 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]96[/TD]
[TD="class: xl69, width: 74"]968,89833[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.256.006 [/TD]
[/TR]
</tbody>[/TABLE]


Should end up like this:

[TABLE="width: 947"]
<colgroup><col width="67" style="width:50pt"> <col width="157" style="width:118pt" span="4"> <col width="44" style="width:33pt"> <col width="74" style="width:56pt"> <col width="134" style="width:101pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 67"]MinOfSYS[/TD]
[TD="class: xl67, width: 157"]FirstOfHierarchy 1[/TD]
[TD="class: xl67, width: 157"]FirstOfHierarchy 2[/TD]
[TD="class: xl67, width: 157"]FirstOfHierarchy 3[/TD]
[TD="class: xl67, width: 157"]FirstOfCOMPANY[/TD]
[TD="class: xl67, width: 44"]UNITS[/TD]
[TD="class: xl67, width: 74"]COST[/TD]
[TD="class: xl67, width: 134"]EAN[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]46,04553[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.272 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]49,163[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.289 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]50,7706[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.296 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]1[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]AAA[/TD]
[TD="class: xl69, width: 44"]6[/TD]
[TD="class: xl69, width: 74"]53,9078[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.055.302 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]96[/TD]
[TD="class: xl69, width: 74"]1002,82563[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.256.013 [/TD]
[/TR]
[TR]
[TD="class: xl68, width: 67, align: right"]2[/TD]
[TD="class: xl68, width: 157"]ABC[/TD]
[TD="class: xl68, width: 157"]DDD[/TD]
[TD="class: xl68, width: 157"]KKK[/TD]
[TD="class: xl68, width: 157"]BBB[/TD]
[TD="class: xl69, width: 44"]96[/TD]
[TD="class: xl69, width: 74"]968,89833[/TD]
[TD="class: xl75, width: 134, align: right"] 6.310.183.256.006 [/TD]
[/TR]
</tbody>[/TABLE]


I have tried MIN, FIRST etc, but then I get the wrong hierachy

Any help will be much appreciated - thanks

brgds
a novice
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Forgot - the EAN-code must be unique, so it should return this (table (1)):

[TABLE="class: cms_table, width: 947"]
<tbody>[TR]
[TD="class: cms_table_xl68, width: 67, align: right"]1[/TD]
[TD="class: cms_table_xl68, width: 157"]ABC[/TD]
[TD="class: cms_table_xl68, width: 157"]DDD[/TD]
[TD="class: cms_table_xl68, width: 157"]KKK[/TD]
[TD="class: cms_table_xl68, width: 157"]AAA[/TD]
[TD="class: cms_table_xl69, width: 44"]6[/TD]
[TD="class: cms_table_xl69, width: 74"]46,04553[/TD]
[TD="class: cms_table_xl75, width: 134, align: right"]6.310.183.055.272[/TD]
[/TR]
</tbody>[/TABLE]


and remove this (from table (2))
[TABLE="class: cms_table, width: 947"]
<tbody>[TR]
[TD="class: cms_table_xl68, width: 67, align: right"]2[/TD]
[TD="class: cms_table_xl68, width: 157"]ABC[/TD]
[TD="class: cms_table_xl68, width: 157"]DDD[/TD]
[TD="class: cms_table_xl68, width: 157"]KKK[/TD]
[TD="class: cms_table_xl68, width: 157"]BBB[/TD]
[TD="class: cms_table_xl69, width: 44"]6[/TD]
[TD="class: cms_table_xl69, width: 74"]46,04553[/TD]
[TD="class: cms_table_xl75, width: 134, align: right"]6.310.183.055.272[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If you do a group query and use the union query as the source... maybe something like:

Code: 
SELECT First(MinOfSys) AS 1stSys, First([FirstOfHierarchy 1]) AS [1stHierarchy 1], First([FirstOfHierarchy 2]) AS [1stOfHierarchy 2], First([FirstOfHierarchy 3]) AS [1stOfHierarchy 3], First(FirstOfCOMPANY) AS 1stCOMPANY, First(UNITS) AS 1stUNITS, First(COST) AS 1stCOST, EAN

FROM (Select * from [COLOR=#FF0000]table1[/COLOR] UNION ALL select * from[COLOR=#FF0000] table2[/COLOR])

GROUP BY EAN;
 
Upvote 0
Wondering if a subquery would work along the lines of
Code: 
DELETE Table1.[MinOfSys] FROM Table1 WHERE 
(((Table1.[MinOfSys] = (SELECT [MinOfSys] AS Tmp FROM [Table1] As Dupe 
WHERE (Dupe.[EAN] = Table1.[EAN]))));
Don't try these on anything but copies of your data!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
ACCES count common friends / nodes (compare within 1 table)
Replies
2
Views
735
LittleBoeddha
L
C
  • Question Question
Filtering Unique records
Replies
6
Views
642
DanteAmor
DanteAmor
M
  • Question Question
3 lists into 1, sorted and then split into 2 lists again
2
Replies
10
Views
2K
Eric W
Eric W
T
  • Question Question
Consolidating rows
Replies
4
Views
655
sandy666
sandy666
R
  • Question Question
Count and Group by Date Range
Replies
6
Views
849
RMS123
R

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top