dezibluenose
New Member
- Joined
- Oct 6, 2021
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
Hopefully someone can help Me with this - I'm trying to sort out a file for my job and I can't get any further than I've gotten already. The way to fix this is obviously way beyond my excel knowledge.
I have a report with different batch numbers and claims/notifications. Each batch can have either a valid or invalid claim against it. And to save time we've created a formula that looks at the batch numbers and the claim status and says if all claims are valid or if one is invalid.
As you can see below, this works perfectly if there are only two batch numbers - 3 or more and the results mess up.
If you look at the picture below, cells A11-A13 are one batch number with two valid claims and one invalid claim. The results I expected to see in cells C11-C13 is "One claim valid, other rejected". Instead, the formula checks the first two batch numbers of the three then treats the third one as a new batch. I know the formula I've used only checks 2 rows, but when I include the same checks a third or fourth time, I again get the wrong results as it can be checking one batch number against a different batch number
Now I'm assuming to fix this, I'll need some sort of unique identifier in my formula - something that tells excel that if 3 or 4 batch numbers match, we need to do the same checks on all and not just stop at 2 rows but it's something I don't know how to do.
Any help or advice would be much appreciated.
And honestly, this could all be avoided if my boss just let me separate the batch numbers into different tabs so 2 batches numbers were in one tab, 3 in the other etc etc, but she doesn't want that.
I've included details in cells A2 - A9 to show the formula works perfect if there's only two batch numbers. Also, in the actual file, there are no gaps between the batch numbers, I just separated them here so you could see the results clearer.
Formula continues down the page as below.
I have a report with different batch numbers and claims/notifications. Each batch can have either a valid or invalid claim against it. And to save time we've created a formula that looks at the batch numbers and the claim status and says if all claims are valid or if one is invalid.
As you can see below, this works perfectly if there are only two batch numbers - 3 or more and the results mess up.
If you look at the picture below, cells A11-A13 are one batch number with two valid claims and one invalid claim. The results I expected to see in cells C11-C13 is "One claim valid, other rejected". Instead, the formula checks the first two batch numbers of the three then treats the third one as a new batch. I know the formula I've used only checks 2 rows, but when I include the same checks a third or fourth time, I again get the wrong results as it can be checking one batch number against a different batch number
Now I'm assuming to fix this, I'll need some sort of unique identifier in my formula - something that tells excel that if 3 or 4 batch numbers match, we need to do the same checks on all and not just stop at 2 rows but it's something I don't know how to do.
Any help or advice would be much appreciated.
And honestly, this could all be avoided if my boss just let me separate the batch numbers into different tabs so 2 batches numbers were in one tab, 3 in the other etc etc, but she doesn't want that.
I've included details in cells A2 - A9 to show the formula works perfect if there's only two batch numbers. Also, in the actual file, there are no gaps between the batch numbers, I just separated them here so you could see the results clearer.
Formula continues down the page as below.
