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

silasworx

New Member
Joined
May 4, 2021
Messages
3
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Code:
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

ice_screenshot_20210504-142047.png


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"

VBA Code:
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:

VBA Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

silasworx

New Member
Joined
May 4, 2021
Messages
3
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Products.xlsm
ABCDEFGHIJKL
1UpdateAdd New
2All TasksBRCAT001BRCAT003Do Later
3UpdateAdd to Products
4SKUTaskDo LaterDo LaterSKUReasonTask
5BRCAT001UpdateBRCAT003No pricing information yetAdd New
6BRCAT002UpdateSKUSKUBRCAT004Not available from supplier at the momentUpdate
7BRCAT003Add NewBRCAT001BRCAT003
8BRCAT004UpdateBRCAT002BRCAT006
9BRCAT005UpdateBRCAT004BRCAT007
10BRCAT006Add NewBRCAT005
11BRCAT007Add New
12
Tasks
Cell Formulas
RangeFormula
E2E2=IF($E$7="","",$E$7)
E3E3=HYPERLINK("#","Update")
E4,G4E4=HYPERLINK("#","Do Later")
G2G2=IF($G$7="","",$G$7)
G3G3=HYPERLINK("#","Add to Products")
K5:K6K5=VLOOKUP([@SKU],tasks_all,2,FALSE)
C5:C11C5=IF(COUNTIF(products_data_sku,[SKU])=1,"Update","Add New")
Named Ranges
NameRefers ToCells
products_data_sku=ProductsData[SKU]C5:C11
products_database=ProductsDataC5:C11
tasks_all_sku=tasks_all[SKU]C5, K5:K6
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K:KCell Valuecontains "Add New"textNO
K:KCell Valuecontains "Update"textNO
C:CCell Valuecontains "Add New"textNO
C:CCell Valuecontains "Update"textNO
 

silasworx

New Member
Joined
May 4, 2021
Messages
3
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,576
Members
418,401
Latest member
B_A_M155

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
Top