Good Afternoon All,
This might seem easy to some, I just cant figure it out and I am not sure what keywords to enter if this was answered already.
I am the manager of an appliance retail store and I have a lot of new employees who are making simple errors. I was hoping there was a function I could enter into excel that would catch mistakes before the customer takes delivery. If we sell a washing machine, we generally speaking have two options of dryers we can sell that would match (gas or electric). So for example, if model # W1234 was sold, our dryer options would be DE123 or DG123.
Unfortunately, as of now I dont have a list of every corresponding dryer that matches each washer, so I was hoping that I could view all recent invoices that were sold, and find the most common dryers sold with each washer so that I could in essence create a list of the dryer options for each washer.
I have hundreds of lines of unique invoice numbers in column A and in Column B, it will show all of the model#'s that were sold on that invoice (please see below)
So the idea of the function I am looking for was to look up "A", find the washer model, and then find the dryer sold.
Then ultimately, in a different cell I want to show how many times each dryer was sold along with a washer so I can make a list of the most common dryers for each washer (something like this directly below in red)
D E F
WASHER DRYER OPTION 1 DRYER OPTION 2
and then create a different function to say something like "if in column A, washer model W1234 was sold AND dryer model DE123 OR DG123 was sold, then there is no error. However if washer W1234 was old and dryer DE500 was sold on that invoice, then create an error code.
If anyone has any suggestions on what function I could use, it would save me so many headaches haha. Thank you all, I really hope I explained this clearly.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]INVOICE #[/TD]
[TD]MODEL#[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A100[/TD]
[TD]W1234[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A100[/TD]
[TD]DE123[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B100[/TD]
[TD]W1234[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B100[/TD]
[TD]DG123[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C100[/TD]
[TD]W5000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]C100[/TD]
[TD]DE500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]D100[/TD]
[TD]W4000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]D100[/TD]
[TD]DEG400[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]E100[/TD]
[TD]W4000[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]E100[/TD]
[TD]DG400[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]F100[/TD]
[TD]W5000[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]F100[/TD]
[TD]DE500[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]F100[/TD]
[/TR]
</tbody>[/TABLE]
This might seem easy to some, I just cant figure it out and I am not sure what keywords to enter if this was answered already.
I am the manager of an appliance retail store and I have a lot of new employees who are making simple errors. I was hoping there was a function I could enter into excel that would catch mistakes before the customer takes delivery. If we sell a washing machine, we generally speaking have two options of dryers we can sell that would match (gas or electric). So for example, if model # W1234 was sold, our dryer options would be DE123 or DG123.
Unfortunately, as of now I dont have a list of every corresponding dryer that matches each washer, so I was hoping that I could view all recent invoices that were sold, and find the most common dryers sold with each washer so that I could in essence create a list of the dryer options for each washer.
I have hundreds of lines of unique invoice numbers in column A and in Column B, it will show all of the model#'s that were sold on that invoice (please see below)
So the idea of the function I am looking for was to look up "A", find the washer model, and then find the dryer sold.
Then ultimately, in a different cell I want to show how many times each dryer was sold along with a washer so I can make a list of the most common dryers for each washer (something like this directly below in red)
D E F
WASHER DRYER OPTION 1 DRYER OPTION 2
and then create a different function to say something like "if in column A, washer model W1234 was sold AND dryer model DE123 OR DG123 was sold, then there is no error. However if washer W1234 was old and dryer DE500 was sold on that invoice, then create an error code.
If anyone has any suggestions on what function I could use, it would save me so many headaches haha. Thank you all, I really hope I explained this clearly.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]INVOICE #[/TD]
[TD]MODEL#[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A100[/TD]
[TD]W1234[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A100[/TD]
[TD]DE123[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B100[/TD]
[TD]W1234[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B100[/TD]
[TD]DG123[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C100[/TD]
[TD]W5000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]C100[/TD]
[TD]DE500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]D100[/TD]
[TD]W4000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]D100[/TD]
[TD]DEG400[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]E100[/TD]
[TD]W4000[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]E100[/TD]
[TD]DG400[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]F100[/TD]
[TD]W5000[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]F100[/TD]
[TD]DE500[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]F100[/TD]
[/TR]
</tbody>[/TABLE]