MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have 4 columns
Parent(A), Child(B),Stock(C)Archive(D)
example
PAR1, EX56, 0, N
PAR1, EX57, 1, N
PAR1, EX58, 0, N
PAR1, EX59, 0, N
PAR2, EX60, 0, N
PAR2, EX61, 0, N
PAR2, EX62, 0, N
PAR2, EX63, 0, N
I need a formula that checks the child against the product so if any of the child product in the parent have stock the product can not be archived but if all the child products have 0 stock then Y can populate in the Archive column
I can only populate with a Y if all the child products to that parent product have no stock
If any of the child products have stock all the parent products have to show N
I’m at a loss can you help? I have 20,000 codes to do so really could do with a formula if possible.
I think the second column is pretty redundant for the formula.
=if(sumifs(c:c, a:a, a2) =0,"Y","N") and copy it down
=if(sumif(a:a, a1, c:C) =0,"Y","N")
Put that in d1 then copy down
Caveat - from memory, not tested
Edit - I've assumed no header row so first entry is row 1, above alternative assumes first data is in row 2. Both basically do the same thing.
I'm not an expert and I don't know the answer. But I'm interested hence the reply
My very limited knowledge suggest that writing a visual basic script might be easier than an EXCEL function
You'd need a IF loop so that it goes through the stock for all instances of a particular parent adding up stock as into a variable. If that is variable is equal to zero then another loop runs setting archive to y
I probably couldn't do that
brill thanks guys that's just saved me hours of work
Assuming your data starts in row 2, enter this formula in cell D2 and copy it down:
Try either: =IF(SUMIF($A:$A, A2, $C:$C) > 0, "N", "Y")
or: =IF(COUNTIFS($A:$A, A2, $C:$C, ">0") > 0, "N", "Y")