AnsweredAssumed Answered

Filtering data and getting quantities from an Excel spreadsheet

Question asked by JeffBroderick on Apr 6, 2015
Latest reply on Apr 7, 2015 by philmodjunk


Filtering data and getting quantities from an Excel spreadsheet





I use a FileMaker file to import information from two different Excel worksheets and compare a report with actual barcode scanned counts.

It is very simple with only two tables.  

Table one = HardwareMatching

Table two = QuantityScanned

HardwareMatching is used to import a POS system report (Excel) with qty and item number which is an inventory report.

QuantityScanned imports from a spreadsheet where individual scanned items have been aggregated and computed in the spreadsheet to create a  qty scanned of each item number.

With a layout related to HardwareMatching, I have the fields:

HardwareMatching::Qty and HardwareMatching::Item and on that layout, I have also put QuantityScanned::Qty and QuantityScanned::Item.  

A calculation field shows which quantities match, which are missing and which are over in terms of qty and items matching each other.

All this leads to my actual question: in my Excel sheet, I use Advanced Filter to separate unique values and then have to use a Countif formula to count the actual individual item numbers to actually apply quantities to the scanned items.

I'm wondering what the best method in FileMaker will be to bring in a single column of items in this spreadsheet of scanned individual items and have FileMaker count them up so I can have qty matched with items.

I can then let my FileMaker file do what it already does very well - match the two tables and give me the results.