find, meet condition, sum range, deduce, find next

Originally Posted by pinstripe

first one has Q =100 second one Q=250.
soled Q=150

Hello pinstripe,

It's a little difficult to follow your explanation. What does the above mean? You show column C with some numbers, are these the results you are trying to get or existing data?

Originally Posted by daddylonglegs

Hello pinstripe,

It's a little difficult to follow your explanation. What does the above mean? You show column C with some numbers, are these the results you are trying to get or existing data?

HI, i am very gratefull for your reply! Yes, in column C are the results.
to explain better:
columnA is an article code, columnB is a Quantity on stock, in columnC are results. In separate sheet i have in columnA aricle codes and in column B soled Quantities. With SUMIF i can sum all sold quantites by aricle code but, then i don't know how to deduct the given number with all the articles - like explained abowe.
Is this clearer ?
thenks for your help

Hi

Can you attached a zipped copy of your example spreadsheet. In it can you explain what number you want to go where, and for 1 or 2, show the result you expect from the data given.


rylo

final result in column ..."this should be the resoult"

Hi

What happens if you make:
AC2: =IF(ISNUMBER(FIND("WW",B2)),1,0)
AH2: =IF(AND(ISNUMBER(FIND("WW",B2)),AB2=0),0,1)

If that doesn't solve it, then can you explain what result you would expect to find where, and explain why it should occur. Pick 1 or 2 rows to explain things.


rylo

thanks for replay.
Well, in columnZ is what comes in and in AA is what is sold.In AB is the differenc of that for each article. In AF is how much have to be upsided for each article to get 0 value - just for those which are in negative numbers in AB. In AH i get 1 for those which name of the article includes WW letter, beacuse these are inported in barels and then repacked to the smaller ones with same UPV (column C)code.
So, i need to sum up all values in AC with same code (columnC) and deduct from those which has same code but also WW in the name.

The problem is with my formulas it deducts from all WW articles with the same code insted of one. And after first one with positive value hits 0 it should deduct from second one it finds with same code and WW in the name. So, in column AH i am puting 1 so i know it has WW in name (from which will be deducted) and in AG should come out the final value for those...for others without WW - 0.
Please, bare with me

Hi,
i have gone through the internet with no luck to find a solution for my problem....and i have to say i am not to good with vba.

Problem:
ColumnA - article name
ColumnB - article code
ColumnC - quantity
ColumnD - month (e.g. 200701- january)
ColumnE - result (X)

i have 12 different ranges (for every month, about 400 article per month)
in columnA - articles have WW in name, sub articles are without --they are condected by the same code (columnB)
What i need to do is, in columnE - take first article code in (B1),
sum quantites of all aricles with the same code but wihout WW in name, go through columnA find article with the same artilce code and with WW in name, - then deduct the sum from first find of WW with same code (if not negative), else find next and deduct from that.

how can this be accomplished?
thx for help

Hi

Your description above doesn't seem to match the format of the sample file you have provided.

Can you either change the description, or put up a new sample file.

Then can you give a worked example from your data. Select the code, the sub code etc, show what they are, and from the numbers given, show what the result should be and how it was calculated.

rylo

i have decided not to do this in excell but in access insted.

I would like to thank you all, for help and will you have given me.
thx