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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
736
LittleBoeddha
L
C
  • Question Question
Filtering Unique records
Replies
6
Views
647
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
656
sandy666
sandy666
R
  • Question Question
Count and Group by Date Range
Replies
6
Views
855
RMS123
R

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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