Solved How to Match data, create formula&calculate if data matches

July 16, 2015 at 13:12:51
Specs: Windows 10
I need a spreadsheet that will match data in column A. Any time a specific work order number is entered/repeted in that column, and the numbers are entered under each shift, the totals will automatically subtract from the "orders to complete" . It will be a running total subtracting from the Orders to Complete column.
wk order # Orders to Complete shift 1 shift 2 shift 3 shift 4 tot orders completed
1234 200 10 10 20 30 70
456 50 2 2 2 2 8
987 500 10 0 0 0 10 1234 0 0 10 50 60
987 20 20 20 20 80
456 5 5 5 5 20


See More: How to Match data, create formula&calculate if data matches

Report •


✔ Best Answer
July 17, 2015 at 16:09:41
You didn't include any Column Letters or Row Numbers, so I am going to assume that your data looks like this:

        A          B          C      D      E         F
1  wk order # # of wk ordrs shift1 shift2 shift3 tot ordr cmplt 
2     1234	  200	      10     10     20        40
3      456	  100	       2      2      2         6
4      987	  550	      20     50     41       111
5      456	              20     10     40        70
6      987		      10     10     10        30
7     1234		      20      5     40        65

Based on that table, enter this formula in G2 and drag it down:

=B2-SUMIF($A$2:$A$7,A2,$F$2:$F$7)

which should give you this:

        A          B          C      D      E         F              G
1  wk order # # of wk ordrs shift1 shift2 shift3 tot ordr cmplt  orders rem
2     1234	  200	      10     10     20        40             95
3      456	  100	       2      2      2         6             24
4      987	  550	      20     50     41       111            409
5      456	              20     10     40        70
6      987		      10     10     10        30
7     1234		      20      5     40        65

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
July 16, 2015 at 16:54:53
Thanks for letting us know what you need.

If you want some help with that, feel free to ask.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 17, 2015 at 12:30:12
wk order # # of wk ordrs shift1 shift2 shift3 tot ordr cmplt 
1234	      200	  10	  10	20	    40
456	      100	  2	   2	 2	     6
987	      550	  20	  50     41         111
456	                  20	  10	 40	     70
987		          10	  10     10          30
1234		          20	  5	 40	     65

I need a spreadsheet that will match data in column A. Any time a specific work order number is entered/repeated in that column, and the numbers are entered under each shift, the totals will automatically subtract from the "orders to complete" . It will be a running total subtracting from the #of work orders.


Report •

#3
July 17, 2015 at 12:51:04
Here are my changes, please let me know if additional changes need to be made.

Report •

Related Solutions

#4
July 17, 2015 at 13:16:12
Well, as I hinted at in my first response, it would nice if you actually asked for some help.

We are all volunteers here, giving freely of of our own time. When someone just blasts out a post starting with "I need..." and not followed with "Please help", it comes off as a little harsh.

That said, I'll work on your "needs" and see if I can come up with a solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 17, 2015 at 16:09:41
✔ Best Answer
You didn't include any Column Letters or Row Numbers, so I am going to assume that your data looks like this:

        A          B          C      D      E         F
1  wk order # # of wk ordrs shift1 shift2 shift3 tot ordr cmplt 
2     1234	  200	      10     10     20        40
3      456	  100	       2      2      2         6
4      987	  550	      20     50     41       111
5      456	              20     10     40        70
6      987		      10     10     10        30
7     1234		      20      5     40        65

Based on that table, enter this formula in G2 and drag it down:

=B2-SUMIF($A$2:$A$7,A2,$F$2:$F$7)

which should give you this:

        A          B          C      D      E         F              G
1  wk order # # of wk ordrs shift1 shift2 shift3 tot ordr cmplt  orders rem
2     1234	  200	      10     10     20        40             95
3      456	  100	       2      2      2         6             24
4      987	  550	      20     50     41       111            409
5      456	              20     10     40        70
6      987		      10     10     10        30
7     1234		      20      5     40        65

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#6
July 21, 2015 at 11:57:37
Derbydad, you don't know me. You don't know that I am a very polite and appreciative person. I always say, "please and thank you". I am very appreciative of any help that anyone gives me with these spreadsheets and formulas. I was having a difficult time getting this formatted correctly and obviously forgot my manners. However, I would never, ever speak to anyone the way you spoke to me. If you were trying to be insulting and condescending, you succeeded, but you ended up looking like a very rude person. As you said, 'you' volunteer to do this and I, as well as many others, appreciate the help. No need to ever insult anyone the way you attempted to insult me. Very unprofessional!

Report •

#7
July 22, 2015 at 10:45:04
re: I always say, "please and thank you".

Please refer to your first 2 posts in this thread. No where do I see a "please" or a "thank you".

In both your OP and in Response #2, you started the description of your issue with "I need a spreadsheet that will match data in column A." You then went on to explain what you wanted the spreadsheet to do, but you never actually asked for any help.

You are right, I don't know you, therefore I can only react to what you post. Since you posted nothing more than your "needs" with no request for help (i.e. no "please" or "thank you") I felt it proper to remind you of the manners that you admit you "forgot".

As a matter of fact, I just went back and looked at some of your other threads.

In this one I see an "I want" and an "I need", but nary a "please" nor a "thank you".

http://www.computing.net/answers/of...

In this thread, all I see is "I would like to see". Once again, nary a "please" nor a "thank you".

http://www.computing.net/answers/of...

Now in this final thread, things get a little better. Even though you started the thread with nothing more than an "I want" (as usual) at least you (finally) offered a "thank you" at the end.

http://www.computing.net/answers/of...

So, "please" don't tell us that you always say, "please and thank you" because your track record in this forum doesn't back up that claim. "Thank you."

message edited by DerbyDad03


Report •

#8
July 22, 2015 at 13:00:02
FYI, here are the instructions on how to ask a question.
How to Ask a Question?
» Make your title both brief and precise.
» Give full details about your problem.
» Provide all your system specifications.
» Enter relevant tags and specifications.
» Select the most appropriate category..
You can see that most people are trying to be precise and short. I read 9 submissions requesting help, from other people, and their posts were the same as mine. Nary a "please" nor a "thank you". I don't know what set you off, but you need to rethink your volunteering position. It's really not that big of a deal as you are making it to be. I would assume you are an adult that knows how to be more professional than you are acting. Do you go around 'scolding' the others that submit questions that omit 'please' and 'thank you'? This really is a little too much.

Report •

#9
July 22, 2015 at 18:57:11
Search through my posts in this forum and you will indeed find other times when I have brought up the same issue with other posters. In just about all cases, they came back and apologized for the tone of their posts. None of them meant to sound rude, but they understood that their post could be taken that way. So far, you are the only one that took offense at my comments.

As long as you are quoting "how to ask a question" criteria, may I suggest that you read this How To.

http://www.computing.net/howtos/sho...

You will find at least 2 interesting points:

1 - There is a suggestion (in bold) that you actually ask a question because it is considered good etiquette. As I mentioned earlier, you have a tendency to tell us your "needs" and "wants" but the "asking" part seems to be missing.

2 - There are instructions on how to report posts that you find offensive. If you do not like anything that I have posted, feel free to pass it on up the chain of command and let the moderators decide if my comments are out of line.

In the meantime, I hope that the formula I posted in Response #5 has solved your initial problem.

message edited by DerbyDad03


Report •

#10
July 23, 2015 at 06:54:31
Sir, your comments are out of line. I am finished with this. I have much more important things to do that banter back and forth with you regarding a 'please or thank you'. I have nothing to apologize for. I was not rude to you or anyone else. You, on the other hand, were very rude. I am finished with this. If I don't tell you what I 'need or what I want, how would you know? Really, you are way too sensitive. This is way over the top. Done.

Report •

#11
July 23, 2015 at 07:46:14
CSD - I think you are too sensitive, Derby was not rude nor out of line with you at all. He was merely suggesting that rather than demand help, why not just ask? To be honest when i read your post i wasnt too inclined to help due to how your 'request' was structured. Derby is a well respected and extremely knowledgable menber of this forum and despite the lack of manners in your OP (which as you say was not intentional) he still went ahead and offered you a solution. We get it a lot on forums where people expect us to help and rather than ask politely they often tell us what they need and when they need it by so it does kind of sometimes get to us.

Have you tried Derbys suggestion?


Report •

#12
July 23, 2015 at 08:13:41
No one is intentionally being rude to any of you. Why would we? We are needing help and/or solutions to a problem. Most of us, including me, are in the middle of a situation that we are in a bind and yes, we 'need' help, or we wouldn't be on your web site. I am not being too sensitive. He was attacking, very rude, condescending and a real smart mouth. I responded to him and told him that I forgot my manners, and he just continued, would not let it go. If I had intentionally been rude, Ok, all this would be justified. I wasn't, he was. I understand things that are posted can be taken with the wrong tone. It was never intended to be rude, but his comments were just that, very intentionally rude. Frankly, I am shocked by all of this. I have never seen a web site attack the people that are needing their help. I thought that is why this web site was there, to help with the needs of the requesors. I read several posts and there was not one 'thanks' or 'please' in any of them, and they were all stating what they 'needed' or 'wanted'. I would like to know if they got attacked as well. This is totally over the top. I have used this web site maybe 3 times in 4 years. I was trying to make my point, and explain in detail what I "needed" so that you could understand. It was difficult for me to try to explain my 'needs'. My point is, he was extremely rude, even though neither of you think that, but after all it is how the reader interprets the tone of the posts, right?

Report •

#13
July 23, 2015 at 10:15:13
re: "If I don't tell you what I 'need or what I want, how would you know?"

The mere fact that you even asked that question indicates to me that you are missing the entire point of this discussion. No one is saying that you shouldn't tell us what you need or want. Of course you need to tell us that. That is always a required part of any request for help.

Let's make this simple. Which one of these sounds "nicer" to you. Which one do you think is more likely to receive immediate help?

Your style...

"I need a spreadsheet that does such and such."

...or the preferred style:

"I need a spreadsheet that does such and such. Can anyone offer some suggestions? Thanks in advance!"

If someone showed up at your office and needed some help, which of those 2 styles would elicit the more positive response from you?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
July 23, 2015 at 11:09:40
Oh my, this is harassment. I get your point, I got it from the first message you sent me. I don't feel like I was rude, you took it as I was rude because of the way the post reads. I feel like you are extremely rude because of the way YOUR posts read. You don't get that. I would not be offended, like you are, if someone from my work stated to me they needed something like the posts reads. I get that every day, all day long so I don't consider it rude, I consider it, someone needs my help. That's my job, that's what I do. The end. Look I don't have time for this. We disagree so I will remove my name from this site as well as all my posts. I have had enough of your 'trying to get a point across". I got the 'point', I disagree with your point. I feel like this has gone on long enough. Please don't send me any more messages. Did you get that, "Please"?
Thanks (??)

Report •

#15
July 23, 2015 at 19:43:52
AWTL:

Thank you for your support.

I guess we'll never know if my suggested formula solved CSD's original issue. Oh well.

message edited by DerbyDad03


Report •

#16
July 27, 2015 at 00:37:51
Derby,

No worries mate anytime! Im sure your suggestion works and CSD is probably using it :)


Report •


Ask Question