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??

 

[GDRIII]

Before we start iv4n305, this is the Power BI forum which is primarily for an Excel Add-in called PowerPivot which are superpower enhanced pivot tables but, not regular pivot tables.

Are you using PowerPivot?

 

[iv4n305]

my apologies GDRIII, i do also have power pivot tables and mainly use them for the distinctive count feature

 

[jafa1970]

Add a new column with ABS(Amount filled) - this removes the sign
Add a second column that concatenates [Prescription number]+[Drug name]+[Date filled]+[new ABS Amount filled] to give a key of sorts
Add a third column and use COUNTIF to see if there are any duplicates (use absolute references for the range), or if you are Excel 2013, use Formatting-Duplicate Value from the Quick Analysis tool



Cheers

 

[iv4n305]

So Jaffa1970. .. I do have 2013 exce, could you help me understand the process , step by step? I m trying to keep this job that benefits my family... I'm sorry but the simplest terms would be helpful..

 

[scottsen]

iv4n305, you are getting questions/answers related to both traditional excel, and powerpivot, because it is not super clear which you want/need. Can you clarify that?

 

[jafa1970]

Hi iv4n305,

Perhaps you can explain which aspect you do not understand, as the points noted were the steps that should get you close to what you need.

Col E : =ABS(D2)
Col F : =A2&B2&C2&E2
Col G : =COUNTIF($F$2:$F$??,F2)

Change ?? to be the number of rows in your data.

Where Col G is greater than 1, you have duplicates based on all the fields, and the Amount filled, irrespective of whether the amount is positive or negative. You can then sort or filter or whatever you need to do with this data

The following link shows how to use conditional formatting to display duplicates in case that is useful. https://www.udemy.com/blog/excel-find-duplicates/

Cheers

 

[iv4n305]

Jafa1970.. I really appreciate you my friend you extending your help to me. I for one am rekindling my experience with excel 2013. What I need to do is reveiw the massive data files of transactions from pharmacies processing prescriptions. Like I mentioned before, I ususally have about 60-82 thousand rows and about 8- 10 columns, what my goal is is to "clean up" the data before i present it to my director by power pivot tables..
How would you sift all that data when you need to find this example:
rx#223341 acetaminophen 250mg 10/1/14 30 qty
rx#223341 acetaminophen 250mg 10/1/14 -30 qty
So.. I manually assort the prescription #columns by numeric order, next i highlight the values that are negative in the qty column, and lastly, I eternally go through the entire data file pairing the same rows by the criteria of having the same: RX # , same date filled, and same quantity ( with positive and negative value).. It takes me 3 days just to do this..
jafa1970 can you suggest to me a faster and more effective way in doing this?

 

[jafa1970]

Hi,

Have you tried the steps pointed out above?

Did they help you get to what you need? If not, please explain what you need in addition to those steps.


Cheers

 

[iv4n305]

scottsen, what my main object is to seek rows that have the same dte, presciption number , quantity, and with a negative and positive value..
example:
rx#223341 acetaminophen 250mg 10/1/14 30 qty
rx#223341 acetaminophen 250mg 10/1/14 -30 qty
i usually have about 60-75 thousand rows of data to sift through
and it takes me about 3 full days to find, high light and delete this transaction since they void each other out.