match 3 column values in a row with offsetting qty values in 4th column

[ronmaltase]

This one is crazy. so If the value in column a, b, c, match then in column d, I want to find if there is a positive value with an offsetting negative value for those same a, b, c matches.

a = account number

b = item number

c = date

d = quantity (could be positive or negative.

I need to charge for the items that don't have a positive and a negative for the same account, item, date. Because if it has a positive and a negative, it's been removed and I want to ignore those.

any ideas?

Thanks, Ron M.

 

[NARAYANK991]

Hi Ron ,


Can you post some sample data ? Or even better , upload a workbook ?


Narayan

 

[Faseeh]

@ronmaltase


If you have same item with diff. prices on diff. dates then only then it should have been problematic, with same price you can use SUMIFS() or SUMPRODUCT(). Bcz positive and negative qtty will cancel each other and you can charge for the rest of the qtty.


Do you have diff. prices for same item??


Faseeh

 

[ronmaltase]

Thanks NARA, I don't know how to upload a workbook :-(


Thanks Faseeh, the price is not listed on the spreadsheet. One account number (column a) might be listed on 10 lines with the same or different item numbers (column b), the same or different dates of service (column c), and the corresponding quantity for each line in (column d). what i'm really after is a flag or indication (like "YES") listed for each line item in a new column (e) if that item (matching a, b, c) DOES NOT HAVE an offsetting item.


So if a, b, c match and it's a positive quantity 2, and there is another line where a, b, c match and it has a negative quantity 2, both of those lines would have a blank in column (e). But if a, b, c match and the quantity is 2 (for example), but there is not another line where a, b, c match with at negative qty 2, then column (e) would be marked with a "YES", because i need to charge it.


you guys are so smart, thanks for your help.


-ron m.

 

[NARAYANK991]

Hi Ron ,


This forum does not have any provision for either uploading files or attaching files to posts.


What you can do is upload your file to any file-sharing website of your choice ( DropBox , SkyDrive , RapidShare , SpeedyShare , Google Docs ,... ) , give others permission to access your file , and then post the access link in this same topic.


Narayan