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

[progwhiz]

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:

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!

 

[progwhiz]

bump

 

[progwhiz]

anyone? please?