Get all matching values from tableA["SKU"] and append them to tableB["SKU"]

[silasworx]

Hi... My first time posting here...

I'm a noob to VBA. I'm used to managing products with a .xls file but my co-workers are struggling using the file because they're regular computer users.
My plan is to create a Macro-Enabled Excel File that will be easy to use to everyone. I want to make it sort of like an app, to help automate tasks.


1. My issue today is within the Tasks sheet

Sheet Name: Tasks

tasks_all
Table to hold all SKUs for the task of the day / listing all products from a PDF catalogue

tasks_update
Table to hold all SKUs that needs to be updated

tasks_add
Table to hold all SKUs that needs to be added

tasks_do_later
Table to hold all SKUs that are discontinued or not yet available or with incomplete information

___________

2. Products Database sheet
Sheet that has 1 table(products_database) that holds all the products for our online store


------------------------------------

Plan of Function

How I want this code to to function
1. I paste allskus from the pricelist/catalogue in tasks_all["SKU"] column
2. then tasks_all["Task"] will match all skus in tasks_all["SKU"] with products_all["SKU"] which is located in the "Products Database" sheet
2.1 If SKUs matches, the value must be "Update"
2.2 Else, the value must be "Add New"

Sub AutomateTasksSample()

    Dim MatchSKU As Integer
    MatchSKU = Application.WorksheetFunction.CountIf(Sheet2.Range("F1:F99999"), Sheet7.Range("C1:C99999"))

    If MatchSKU = 1 Then
        ' APPEND ALL MATCHING SKUs TO [B]tasks_update[SKU][/B]
    Else
        ' APPEND ALL MATCHING SKUs TO [B]tasks_add_new[SKU][/B]
    End If

End Sub

So far this code works... This is what I currently have:

Sub AutomateTasksSample()
    Dim MatchSKU As Integer
    MatchSKU = Application.WorksheetFunction.CountIf(Sheet2.Range("F1:F99999"), Sheet7.Range("C1:C99999"))

    If MatchSKU = 1 Then
        MsgBox "Found - Update Product"
    Else
        MsgBox "Not Found - Add Product"
    End If
    

End Sub

Thanks in advance... Your respond will be much appreciated

 

[silasworx]

Products.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1					Update		Add New
2		All Tasks			BRCAT001		BRCAT003		Do Later
3					Update		Add to Products
4		SKU	Task		Do Later		Do Later		SKU	Reason	Task
5		BRCAT001	Update						BRCAT003	No pricing information yet	Add New
6		BRCAT002	Update		SKU		SKU		BRCAT004	Not available from supplier at the moment	Update
7		BRCAT003	Add New		BRCAT001		BRCAT003
8		BRCAT004	Update		BRCAT002		BRCAT006
9		BRCAT005	Update		BRCAT004		BRCAT007
10		BRCAT006	Add New		BRCAT005
11		BRCAT007	Add New
12
Tasks

Cell Formulas
Range	Formula
E2	E2	=IF($E$7="","",$E$7)
E3	E3	=HYPERLINK("#","Update")
E4,G4	E4	=HYPERLINK("#","Do Later")
G2	G2	=IF($G$7="","",$G$7)
G3	G3	=HYPERLINK("#","Add to Products")
K5:K6	K5	=VLOOKUP([@SKU],tasks_all,2,FALSE)
C5:C11	C5	=IF(COUNTIF(products_data_sku,[SKU])=1,"Update","Add New")

Named Ranges
Name	Refers To	Cells
products_data_sku	=ProductsData[SKU]	C5:C11
products_database	=ProductsData	C5:C11
tasks_all_sku	=tasks_all[SKU]	C5, K5:K6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K:K	Cell Value	contains "Add New"	text	NO
K:K	Cell Value	contains "Update"	text	NO
C:C	Cell Value	contains "Add New"	text	NO
C:C	Cell Value	contains "Update"	text	NO

 

[silasworx]

OR
(For my this can work too)

List of All Tasks, SKUs matching with ones in the products database

Update
Products that needs to be updated in the product database table

Add New
Products that needs to be added to the product database

if( tasks_all["Task"] = "Update" )

' Products that needs to be updated
append all tasks_all["SKU"] (Update) to tasks_update["SKU"]

Else

' Products that needs to be added to the Products Database (Sheet within the workbook)
append all tasks_all["SKU"] (Add New) to tasks_add_new["SKU"]

End If