cac1219

New Member
Joined
Feb 27, 2014
Messages
14
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:

50039APPLES GALA 12 CT (DI) 218.4
50041APPLES HONEYCRISP (DI) 12752.2
50001APPLES PEELED SL HARALSON IQFCS=30 LB
50001APPLES PEELED SL HARALSON IQF 923938.4
59010APPLESAUCE UNSWEETENED #10CS=6/#10
59010APPLESAUCE UNSWEETENED #10 38.66671224.54
59001APRICOT CANNED #10CS=6/#10
59001APRICOT CANNED #10 351237.55
50028APRICOTS DRIED (DI) 8142.12
67030ARTICHOKE QTRD #10CS=6/#10
67030ARTICHOKE QTRD #10 29.831680.74
51534ARUGULA BABY (DI) 790.85

<tbody>
</tbody><colgroup><col><col><col><col span="2"></colgroup>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
Hi,
Do not merge the 2 tables. Use the same ID in the 2 tables to create a whole 1 table with VLOOKUP function.
 
Upvote 0
Try this:-
Code:
[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
 
Upvote 0
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))
 
Upvote 0
Worksheet 1 (First 5 rows)
20148JUICE TOMATO ASEPTIC 46 OZCS=12/46 OZ
20319MIX BITTERSCS=12/4 OZ
21584CHEESE CHEDDAR MILD SLICECS=12/1 LB
21705CHIPS DORITO COOL RANCHCS=64/1.75 OZ
21789CHEESE PEPPER JACK SLICED .75 OZCS=9/21 OZ

<tbody>
</tbody><colgroup><col><col><col></colgroup>


Workseet 2 (First 5 rows)
11018PEN - BANQUET STICK - 500/CASE Total302700
14145PUMP CONDIMENT (DI) Total210.72
20001GINGER ALE BIB Total34720.12
20003SODA BOTTLES 10 OZ(DI) Total345.81
20007DIET TONIC 1 L (DI) Total115.13

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>



Do not match
 
Upvote 0
Sheet 1

50001APPLES PEELED SL HARALSON IQFCS=30 LB

<tbody>
</tbody>

Sheet 2

50001APPLES PEELED SL HARALSON IQFENTER FORMULA923938.4

<tbody>
</tbody>

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

Does this work?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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