Unique UPCs/EAN/Barcodes from across multiple spreadsheets without Macro

progwhiz

New Member
Joined
Jan 22, 2016
Messages
3
Hi All, could really use some help here.

I have an excel workbook where I keep inventory of different stores, and a TOTALS spreadsheet as the first spreadsheet tab..

(Spreadsheet tabs such as Totals, Store1, Store2, Store3, Store4 etc).

All spreadsheets have UPCs/EANs/Barcodes in Column A.

I want the TOTALS spreadsheet to have all unique Values from across all the stores/spreadsheets in column A, where I then will calculate totals by lookups/sumIFs/sumproducts with spreadsheet references (the latter I don't need help with, it's already created, and totals let's say QTY from Store1!C3. Store2!C3 and the like)

I already created a Macro which gets all the EAN/UPCs and takes out Duplicates, which works well, as follows:

Code:
Sub Combine_and_Remove_Duplicates()


    Output_Row = 6
     
For Sheet_Index = 2 To 8
    Input_Row = 2
    
    While (Not (Sheets(Sheet_Index).Cells(Input_Row, 1) = "" And Sheets(Sheet_Index).Cells(Input_Row, 2) = ""))
        
        If ((Trim(Sheets(Sheet_Index).Cells(Input_Row, 1)) <> "") And (Trim(Sheets(Sheet_Index).Cells(Input_Row, 1)) <> 0)) Then
    
            Sheets(Sheet_Index).Cells(Input_Row, 1).Value = Application.Trim(Sheets(Sheet_Index).Cells(Input_Row, 1).Value)
            Sheets(1).Cells(Output_Row, 1) = Sheets(Sheet_Index).Cells(Input_Row, 1) 'Ean/UPC
            Sheets(1).Cells(Output_Row, 2) = Sheets(Sheet_Index).Cells(Input_Row, 2) 'Product Name
            Sheets(1).Cells(Output_Row, 3) = Sheets(Sheet_Index).Cells(Input_Row, 3) 'Category
            Output_Row = Output_Row + 1
            
        End If
        
        'Output_Row = Output_Row + 1
        Input_Row = Input_Row + 1
        
    Wend
Next Sheet_Index


     Range("Table8[EAN]").Select
    Selection.NumberFormat = "0"
       
 ActiveSheet.Range("Table8[#All]").RemoveDuplicates Columns:=1, Header:= _
        xlYes
        


End Sub

but the issue is I always have to run it manually with my macro shortcut, after I was helping update the stores' stock manually.

Now I've changed it so each store does an external lookup to a separate .xls for each store (containing 1 spreadsheet with the identical info as their tab in my workbook) that they will update daily themselves.

I want this workbook, after taking the data from the external worksheets to take all the unique EAN/UPCs into the TOTALS spreadsheet Column A (overriding any previous data), then it needs to carry over the corresponding col B (Product Name) and col C (Category) -see code above - and then automatically do my other totals lookups/calculations on its own (already in col D & on)

Can anyone recommend either in-cell formulas in Column A(-C) on the TOTALS page or a UDF that can do this?

Please let me know if anything is not clear.

Thanks in advance!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,223,060
Messages
6,169,861
Members
452,286
Latest member
noclue2000

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