mercredi 1 mai 2019

Two Sets of data(both contain 2 data points, Name & Value), Match 1a & 2a, Calculate Delta(2a - 1a) if matching values exist

  • I have two large datasets that contain the same Headers but different values
  • We are trying to calculate Rate of Sales per hour
  • The main datapoints in question are Name and Value
  • Dataset B was just formed while Dataset A was generated 1 hour ago
  • Data is from Sales Reports. Names are product names and Values are Quantity sold

The need for this comparison stems from the inability to run reports over time with the data provider

I want to compare Dataset A to B by:

  1. Matching Set B: Name to Set A: Name
  2. If there is a match, calculate delta between Set B: Value and Set A: Value to get Sales in last hour for each of Set B: Name
  3. Print the delta in a new column

I've tried to utilize If, index and match in my formulas, however the closest I can get is to break the desired formula into steps but I have yet to be successful.

The resulting formula would allow me to paste into a 5th cell and copy the formula down the sheet to quickly calculate deltas based on Set B: Name

sample result here

Aucun commentaire:

Enregistrer un commentaire