Calculate Discounts By Product

[Cordav]

Good evening,

I hope you are in good health.

My name is David and I use this forum because I am unable to find a solution to my problem.

I have two Excel files.

One in which I have the database of product references by date and by manufacturer (TodasPropostasPortugal), separated by TAB's for easy change when there are new price lists.

In that same file, it is divided by columns of:

****A | B | C | D | E | G | H
Brand | Reference | Description | Price | Price Group | Discount Group | Discount

Another file where I have is "FolhaPropostas_TIPO_23_03_2020” that will read information in the "TodasPropostasPortugal".

I've already managed to do some of the things I wanted to do, but right now I can't move forward

What is intended I will try to exemplify here.

In column C of the file “FolhaPropostas_TIPO_23_03_2020” references are placed:

For example references (I put already the 3 references):
MTN649212
52900
TXA624C

What will happen (it is already done) is that the formula will automatically search and put the description, brand, list price and Price Group, example:

Refª || DESCRIPTION || BRAND || TABLE PRICE || PRICE GROUP
MTN649212 || ACT.BIN.REG-K-12x230-10 C-OP.MANUAL || SCHNEIDER || 596.72€ || GP4
052900 || Gira HomeServer 4 Gira Server || GIRA || 2128,65€ || 5
TXA624C || Actuator blinds / pers. 4 channels 230V KNX || HAGER || 238.56€ || 42

What I want and do, and don't know how to do it, is that when I type a reference, make me an allocation of the corresponding discount for each product listing.

For example, in the Excel "TodasPropostasPortugal" file under the "SCHNEIDER" tab, there is a table of price groups (columns H and I) where it shows for example:

GROUP || Discount

GP1 || 52.00%
etc

For the GIRA brand, the price group is different:

GROUP || DISCOUNT

1 || 45%
2 || 35%
etc

For the HAGER brand, the price group is different:

GROUP || DISCOUNT

22 || 45%
23 || 40%
etc

And each manufacturer has its own discount groups.

What was intended is that on the "Excel "FolhaPropostasTIPO_23_03_2020" column J and Q should be filled with the discount according to each price group of each manufacturer.

Example I gave at SCHNEIDER would appear in the discount >> 45% (corresponding to the GP4 group)
In GIRA, the discount would appear >> 20% (corresponding to group 5)
At HAGER, the discount would appear >> 50% (corresponding to group 42)


The problem is that each manufacturer has its price groups and I am not able to make each discount appear and associate it with the correct discount group.

I humbly ask if anyone around here can help

Thanking you in advance for any help you can give me.

I leave the two files attached.

David

[alansidman]

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

Use concise, accurate thread titles.
Your post title should describe your problem, not your anticipated solution.
Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

Since you are new to the forum, I have changed the title for you. Please comply in the future and read the rules you agreed to abide by.

[MarvinP]

Hi Cordav,

I have an answer for you. Start with my example attached: VLookup Other Workbook using Named Ranges.xlsx and TodasPropostasPortugal.xlsx
I've created two Named Ranges on each of your second files sheets. This makes the formulas a bit easier. On each sheet there is a ??Table and ??Grupo ; Named Ranges. Look at Formulas > Named Ranges to see all those ranges.

I've done a formula for you that uses two VLookup() formulas. See the Yellow area of the first file above. You will need to have both above files open to have it work correctly.
Let me know if you need more explanation or my files don't work on your system.

[Cordav]

HI MarvinP

First of all.....thank you...thank you....thank you for helping me, many thanks

I could see that no one could help me!

I saw what you did and I think it solved 50% of my problem

But (I don't know if I didn't realize it or if I did something wrong), when I use the 3 lines in yellow, if each supplier puts a reference to that same supplier, it works

But if I change, it no longer works.

If in line 44, column C, I put the reference TYA670D (corresponds to the manufacturer HAGER) it will give an error because it is not the manufacturer Schneider.

The objective in column C, in rows 9,10,11,12, and continuing... is to put any reference (from any manufacturer) and it will return all the correct columns for that reference, that is, description, brand, price, group of price and discount.

Once again, I thank you from the bottom of my heart for helping me!

THANKS!

Best Regards
David

[MarvinP]

Hi David,

I couldn't find the manufacturer on your Propostas sheet for that product. I have a solution for that too. I used the first two letters of the manufacturer in the Defined names of my ranges. Show me a product that is in sold by both manufacturers and I'll toss that into my formula.

Thinking about it again, you could try a product code against all sheets and only return the one that didn't error.

This problem could also be solved using VBA, which I didn't do for you. The secret is that in the Find Dialog, you can search for product codes in the whole workbook instead of just the worksheet. Press Ctrl+F and change "Worksheet" to "Workbook" to see what I mean.

Hope this helps....
Wash your hands is my new phrase for Covid-19!

[Cordav]

Hi Marvin

Pease don't kill me !! : eek :: eek:

I try to understand what you explain to me all this hours, but I can't understand ... sorry!

Could you help? (sorry for the incovinience and work )

Thanks in advance

David

[MarvinP]

Hey Cordav, Let me try in English.

You have two workbooks: Products and Proposals. You need to have both open at the same time (for now).
On the Products sheet I created (new) named Ranges (look at Formulas > Named Ranges). For each worksheet I named a range for all products named (first two letters of the company) followed by the word "Table". I also named a range for the Group table. I used the same first two letters of the company on both these Named Ranges.

Would it be OK to put all your Products on a single worksheet? Do you know you have about 30 different products with the same code? Would a VBA answer work for you?

I'll keep working on this after you get back to me.

[Cordav]

Hi MarvinP

Please, don't be upset with me!

I got to see everything you did, but didn't understand it

In other words, I know I have to have both sheets open, I changed the link that came because it came from your “Marvi” folder and I was able to understand how to select the discount group and the table where you will see it.

I just didn't realize when I said that changing from "workgroup" to "worksheet" he would start to see the references requested in the whole sheet.

Yes, I know that there are several identical references.

On the Proposals sheet, in column C line 9 if you put the reference, for example 100101, the indication in column N will appear to warn you that there are 2 marks with that reference.

If you change in column A the number of 3 or 5, it will change the manufacturer that if we want the reference to be considered.

As for products on a single sheet, or VBA, the only thing that is more complicated is that there are several manufacturers and they change prices several times a year.

What I did until now, was when a manufacturer changed prices, just go to the manufacturer's TAB, change the references and give a new name (for example, SCHNEIDER032020 = list of prices oft March 2020, the manufacter JUNG, the TAB JUNG062018 = that the price list in from June 2018. When the manufacturer JUNG changes the prices, they send me the excel with the ref, description, price and price group and I just go to the manufacturer TAB and copy paste, and it is update de prices, that is why I worked with the two sheets of excel).

I really don't want to be bothering you or giving you any trouble, if you can help in any way (VBA or other) I am entirely grateful

Thank you and I apologize for my English that I use the Google translator and may not be 100% what I want to transmit.

Once again my sense THANK YOU,

David

[MarvinP]

Hey David,

You should really have unique Product Codes. For example Referencia:
100101 Berker = 1930 - Quadro montagem superf. x1, preto AND
100101 Gira = Espelho de remate 1,5 tomadas Standard 55 creme
That throws off your whole system of unique numbers.
Another example that is bad is
213126 Gira - where you have 4 products with this number leading to two different GP's

My solution for you depends on Named Ranges from the Proposal sheet into the Products sheet. YES - you can define a Named Range into a second workbook!! These ranges first column should be the Referencia number and span 4 columns, over to the GP column. They you can take an ID and VLookup it into the Products table using the Named Range you've created in the Proposal sheet (that is looking into the Products workbook)

Read more about this at:
https://support.office.com/en-us/art...a-d7cca2a9b95f

[Cordav]

Good morning Marvin P

I read carefully what you wrote to me, thought and made a decision.

I will give up improving the sheet

I don't want to bother you anymore

I really appreciate your help and time (he was the only person who tried to help me), but unfortunately I can't go on.

I am a simple and basic user of excel. I don't know how to make simple formulas....VBA...that is "lots of sand for my truck"!

I will continue to place the discounts line by line as I used to do, because the file of suppliers is much larger (I had to eliminate almost all of them and I only left 5 manufacturers, due to the size of the excel sheet that did not allow me to upload the attachments here in the forum, because they are 19 manufacturers and thousands of references), so it is complicated for me to be seeing all the references and everything, because I don't have the knowledge to do so.

But I don't know you, but my feeling of gratitude is big for trying to help me

I wish you all the best and a lot of health, good weekend.

THANKS!
David