How to group small variations on data

[Liviu C]

Hi,

Please help!
I have a file with multiple sheets. The same products, but from different countries so they have 70% the same names. They all have different prices but in the same currency. I need to be able to have the first row with my name of the product (the one that I set), my price in the second row, and from there (3rd row, 4th, and so on) the price that every customer offers. Starting with the best (calculated in percentage from my price).

So basically I have my price with my name of the product, and I need to see which one of my clients has the best price on offer.

It would be great if I can just input the clients list somewhere (on a different sheet) and it will give me the updates result in the first sheet. This is because every month (sometimes twice a month) prices may change.


I know it is pretty darn complicated but I even tried paying for this and didn't manage to find the answer...

Thank you in advance for any possible solution

 

[awoohaw]

welcome to the forum. you will be able to get help here if you can help the forum help you.
First, what version of excel are you using? (please update your profile so that your version is displayed on your button in the chats).
Secondly, post some sample data. It is best to use the xl2bb add in, but if you cannot use that then post a table (identifying the cell reference of the top left cell) using a cut and paste (paste into the message post).
Thanks in advance.

 

[Liviu C]

welcome to the forum. you will be able to get help here if you can help the forum help you.
First, what version of excel are you using? (please update your profile so that your version is displayed on your button in the chats).
Secondly, post some sample data. It is best to use the xl2bb add in, but if you cannot use that then post a table (identifying the cell reference of the top left cell) using a cut and paste (paste into the message post).
Thanks in advance.

Hi Awoohaw,

Nice to E-meet you, and i like that you moo at cows

I am using Microsoft Office Mondo 2016. At least that`s what i can tell. I updated it.

It seems i can`t install the add-in at the moment so it will be easier to upload a cut and paste sample.

So for example:
This is the name i have for the product (also called line). This name is imported from my stock so i want it to be the base for the file:
NEUPRO 6 MG/24 H * 28 PLASTURI UCB
And these are some of the names my clients have for the same line. Each of them sends me a whole file with anywhere from 30 to 300 different lines:

NEUPRO 6 mg/24h

NEUPRO 6 mg/24 h system plasture transdermix x28

Neupro patches 6mg 28's

NEUPRO 6MG

NEUPRO PLASTURE TRANSDERMIC 6MG/24H CT*28 RT

NEUPRO 2mg/24 h x 28 plast

I only need my line name, but i need the formula to recognize the other names from different sheets and bring the price next to my name (each client has a sheet). Come to think of it it`s not a requirement for each of them to be in a different sheet, i just do it this way at the moment. There would be no problem to change that.

My price is 363.78 Lei (Romanian currency). I calculate the conversion rate from Lei to Euro at 4.95 (but i need to be able to change it if there are big differences). So based on this, 1 pack of Neupro 6mg in euro will be: 363.78/4.95=73.5 Euro. And some of my clients can buy it with 75E, some with 85E, and even 95E. At the moment all my selling strategy is calculated from my price in euro versus my clients price. So the 75E client is calculated: 75E-73.5=1.5E. This is my margin in euro, and I always do the % as well (1.5E/73.5E=2%). For 85E it`s 15.5%, and for 95E 29% (I used the same formula and rounded them up to be easier to follow). I would need to have the one with the best margin in percentage (29% in this case) to be in front (first after my price as i previously said), the 15.5% after, and then 2%. Like this until the last client. Where the client doesn`t have that line in his buying file, it would be great if there would be no data (an empty slot) if possible.
Each of them sends me they`re updated prices on a monthly basis. They`re prices could change even in the middle of the month, and i need to update my sells team about it in short notice, but mine only changes once a year (at which point i can insert it manually).

I hope I managed to make it a bit more clear for anybody that could help. I know it seems complicated, trust me it is even more for somebody that doesn`t know excel past pivot table and Vlookup, but for me it would mean tenths or maybe even thousands of hours saved.

Again, thank you in advance!

 

[awoohaw]

Thanks.
Do all of the products line names start with the first six characters in the cell? And are there multiple product names that begin with the same six characters?(If so, how can you distinguish further?) Does your grouping criteria change with data that is after the 6th character? Can you provide more sample data of actually different groups?

 

[Liviu C]

Thanks.
Do all of the products line names start with the first six characters in the cell? And are there multiple product names that begin with the same six characters?(If so, how can you distinguish further?) Does your grouping criteria change with data that is after the 6th character? Can you provide more sample data of actually different groups?

Thank you also!

Yes there are more Product names in which the difference is a few characters. For example: i Have Neupro 2mg, 4mg, 6mg, 8mg and IV. This goes for other products as well. Another example i can give you is:

HUMALOG 100U/ML SOL.INJ 5 CART X 3ML

HUMALOG MIX25 100U/ML 5 CARTUSE X 3ML

HUMALOG MIX25 KWIKPEN 100UI/ML 5 PEN X 3ML

HUMALOG MIX25 X 5 SUSP. INJ. 100U/ml ELI LILLY NEDERLAND

HUMALOG MIX50 100U/ML 5 CARTUSE X 3ML

HUMALOG MIX50 KWIKPEN 100UI/ML 5 PEN X 3ML

HUMALOG MIX50 X 5 SUSP. INJ. 100U/ml ELI LILLY NEDERLAND

Different products and different concentrations and all of them have different names from my clients but the same base. Like for Humalog MIX 25 KWIKPEN in everybody`s name there are at least these characters but the words are not necessary in the same order. Some clients have it Humalog Kwikpen mix 25, some Humalog Kwikpen 25 mix, and so on.

Does this help?
Please feel free to ask as many questions you need


Thank you!

 

[Cubist]

Do you have the NDC? It would be easy if you do.

 

[awoohaw]

Do you receive these files separately from each vendor?

 

[awoohaw]

Do you have the NDC? It would be easy if you do.

@Cubist, i'm curious, what is NDC? Is that drug manufacturer code or something?

 

[Liviu C]

Do you have the NDC? It would be easy if you do.

Hi there! i`m sorry i don`t know what that is

Thank you!

 

[Cubist]

@awoohaw
This is only applicable in the US. I don't know if that's where the OP is from. It's short for the National Drug Code. It's a 10-digit code that tells you the manufacturer, form, and dosage. All drugs sold in the US are required to have this, including over-the-counter. You can find them on the bottle.