Cleaning up data before applying to a pivot table

[iv4n305]

Hello to all, I would like to know is there a formula or a function in which i could pair a set of rows with a pair of columns ?
Mys scenario is this.
i have about 70k of rows of data with about 20 colums
column A is a list of prescription numbers
column B is drug name
column C is the date filled
column D is the Amount filled

My objective is to find the Same: prescription number, drug name, date filled and Amount filled but with a negative and positve value?
I manually go in the this entire data file Manually and highlight the rows. can some one please tell me theres is a simpler way in executing this task??

 

[scottsen]

Well, if you want to user PowerPivot it can certainly do this. Seems like you could just have a measure: Total := SUM(MyTable[Quantity]) then just drop RX and Date on rows, drop [Total] in values... and filter your table to only show Total=0 ?