Forum Discussion

ginochambers's avatar
ginochambers
Copper Contributor
Nov 12, 2023

Take only required data from spreadsheet

Hi,

 

I have a full list of product barcodes which we stock from supplier X (approx. 350)

Is there a way to take data from supplier X’s full product spreadsheet (1600 items) and only show the data related to the barcodes I supply?

 

Including all rows which feature the barcode 

excel

1 Reply

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Nov 12, 2023

    ginochambers There are a couple of formulas you could try involving the FILTER function, if it's available in your version of Excel. Use either the COUNTIF method or the ISNUMBER/XMATCH method shown below, to filter by a list of SKU values:

     

    =FILTER(table, COUNTIF(sku_list, sku_range))
=FILTER(table, ISNUMBER(XMATCH(sku_range, sku_list)))

     

    Also, if your master table includes multiple product lists by supplier in the same table, include additional criteria to filter by supplier:

     

    =FILTER(table, (supplier_range="Supplier Name")*COUNTIF(sku_list, sku_range))
=FILTER(table, (supplier_range="Supplier Name")*ISNUMBER(XMATCH(sku_range, sku_list)))

     

    For a demonstration of the ISNUMBER/XMATCH method, check out Mike Girvin's Excel FILTER Function to Extract Records Based on Items in a List video on his YouTube channel.

Resources