Remove duplicates

[cac1219]

I had 2 spreadsheets- both with lists of the same item but containing different information on each item. I have copied and pasted the information together and need to combine information on to one line and delete duplicates. My document is 5 columns wide and about 4,000 rows long.

Column headers are:
Item ID, Item Description, Pack, Qty, Total
Spreadsheet 1 had Item ID, Item Description and Pack.
Spreadsheet 2 had Item ID, Item Description, Qty and Total.
Not every line will have complete information (mostly Pack is missing).

As a simple solution, I can insert a cell in the Pack column and shift everything down 1 space. Would work for most but cannot be guaranteed it is the same for every line (4,000 lines).

Delete duplicate information was not working for me because rows are not completely the same and excel does not define as a “duplicate”. When I narrow the “duplicate” criteria (Item ID only) deletes rows with info and leaves me with blank ones.


Sample:

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD]50039[/TD]
[TD]APPLES GALA 12 CT (DI) [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18.4[/TD]
[/TR]
[TR]
[TD]50041[/TD]
[TD]APPLES HONEYCRISP (DI) [/TD]
[TD] [/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]752.2[/TD]
[/TR]
[TR]
[TD]50001[/TD]
[TD]APPLES PEELED SL HARALSON IQF[/TD]
[TD]CS=30 LB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50001[/TD]
[TD]APPLES PEELED SL HARALSON IQF [/TD]
[TD] [/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]3938.4[/TD]
[/TR]
[TR]
[TD]59010[/TD]
[TD]APPLESAUCE UNSWEETENED #10[/TD]
[TD]CS=6/#10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]59010[/TD]
[TD]APPLESAUCE UNSWEETENED #10 [/TD]
[TD] [/TD]
[TD="align: right"]38.6667[/TD]
[TD="align: right"]1224.54[/TD]
[/TR]
[TR]
[TD]59001[/TD]
[TD]APRICOT CANNED #10[/TD]
[TD]CS=6/#10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]59001[/TD]
[TD]APRICOT CANNED #10 [/TD]
[TD] [/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1237.55[/TD]
[/TR]
[TR]
[TD]50028[/TD]
[TD]APRICOTS DRIED (DI) [/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]142.12[/TD]
[/TR]
[TR]
[TD]67030[/TD]
[TD]ARTICHOKE QTRD #10[/TD]
[TD]CS=6/#10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]67030[/TD]
[TD]ARTICHOKE QTRD #10 [/TD]
[TD] [/TD]
[TD="align: right"]29.83[/TD]
[TD="align: right"]1680.74[/TD]
[/TR]
[TR]
[TD]51534[/TD]
[TD]ARUGULA BABY (DI) [/TD]
[TD] [/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]90.85[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"></colgroup>[/TABLE]

 

[Daveydoodles]

Do you still have the original two spreadsheets? I would have thought it would be easier to fill in the blanks via a lookup rather than combining the two tables.

So for example in sheet two, add a column for pack and use the Item ID to Vlookup/Index(Match the pack info.

 

[eliW]

Hi,
Do not merge the 2 tables. Use the same ID in the 2 tables to create a whole 1 table with VLOOKUP function.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Mar32
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]For[/COLOR] Ac = 2 To 4
            [COLOR="Navy"]If[/COLOR] .Item(Dn.Value).Offset(, Ac) = "" [COLOR="Navy"]Then[/COLOR]
                .Item(Dn.Value).Offset(, Ac) = Dn.Offset(, Ac)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
Rng.Resize(, 5).RemoveDuplicates Columns:=1
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[cac1219]

I do have the originals. I do not know Vlookup/Index. Anyway to give brief overview?

 

[eliW]

I do have the originals. I do not know Vlookup/Index. Anyway to give brief overview?

It is basic and worth to learn. Can you send few rows of the originals ?

 

[Daveydoodles]

Say your item ID is in column A on both sheets and pack info is in column C sheet 1.

In your new column on sheet 2 enter;

INDEX(Sheet1!C:C,MATCH(A1,Sheet1!A:A,0)) and drag down

essentially; INDEX(what you want to return,MATCH(what you are looking for, where you are looking for it,0))

 

[Daveydoodles]

or,

VLOOKUP(A1,Sheet1!A:C,3,FALSE)

 

[cac1219]

Worksheet 1 (First 5 rows)
[TABLE="width: 413"]
<tbody>[TR]
[TD]20148[/TD]
[TD]JUICE TOMATO ASEPTIC 46 OZ[/TD]
[TD]CS=12/46 OZ[/TD]
[/TR]
[TR]
[TD]20319[/TD]
[TD]MIX BITTERS[/TD]
[TD]CS=12/4 OZ[/TD]
[/TR]
[TR]
[TD]21584[/TD]
[TD]CHEESE CHEDDAR MILD SLICE[/TD]
[TD]CS=12/1 LB[/TD]
[/TR]
[TR]
[TD]21705[/TD]
[TD]CHIPS DORITO COOL RANCH[/TD]
[TD]CS=64/1.75 OZ[/TD]
[/TR]
[TR]
[TD]21789[/TD]
[TD]CHEESE PEPPER JACK SLICED .75 OZ[/TD]
[TD]CS=9/21 OZ[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


Workseet 2 (First 5 rows)
[TABLE="width: 581"]
<tbody>[TR]
[TD="align: right"]11018[/TD]
[TD]PEN - BANQUET STICK - 500/CASE Total[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2700[/TD]
[/TR]
[TR]
[TD="align: right"]14145[/TD]
[TD]PUMP CONDIMENT (DI) Total[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10.72[/TD]
[/TR]
[TR]
[TD="align: right"]20001[/TD]
[TD]GINGER ALE BIB Total[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]720.12[/TD]
[/TR]
[TR]
[TD="align: right"]20003[/TD]
[TD]SODA BOTTLES 10 OZ(DI) Total[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]45.81[/TD]
[/TR]
[TR]
[TD="align: right"]20007[/TD]
[TD]DIET TONIC 1 L (DI) Total[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15.13[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]



Do not match

 

[Daveydoodles]

Sheet 1

[TABLE="class: cms_table_grid, width: 550"]
<tbody>[TR]
[TD]50001[/TD]
[TD]APPLES PEELED SL HARALSON IQF[/TD]
[TD]CS=30 LB[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="class: cms_table_grid, width: 550"]
<tbody>[TR]
[TD]50001[/TD]
[TD]APPLES PEELED SL HARALSON IQF[/TD]
[TD]ENTER FORMULA[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]3938.4[/TD]
[/TR]
</tbody>[/TABLE]

=INDEX(Sheet1!C:C,MATCH(A1,Sheet1!A:A,0))

Does this work?