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

 

[Liviu C]

Do you receive these files separately from each vendor?

Yes they send it separately

 

[Liviu C]

@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.

Yes we also have this, but the code is different in Romania versus other countries. They are the same at the EU level (which would help), but i didn`t manage to find anywhere to correlate them between Romanian and European Union. We have them imported from the national registry and the EU has them sent by each manufactured/importer. And for outside the EU it gets even worse. The only common thing is the name. I spent almost a day searching for them a few months ago.

 

[awoohaw]

How many different file sources do you have? It seems to me you need to create an intermediate conversion file for each file.
Is there any consistency in how each individual file is created? For instance, if the first file always has the drug name in before a space, and the dose amount is between a space and the letters "mg" you may be able to build a conversion file and keep it in a data source reference file and use that with Power Pivot or Power Query to consolidate and convert the data.

 

[Liviu C]

Yes we also have this, but the code is different in Romania versus other countries. They are the same at the EU level (which would help), but i didn`t manage to find anywhere to correlate them between Romanian and European Union. We have them imported from the national registry and the EU has them sent by each manufactured/importer. And for outside the EU it gets even worse. The only common thing is the name. I spent almost a day searching for them a few months ago.

i just thought of this: maybe i can start from the Romanian name, correlate it with the clients name, then from that correlate the ECMA (EU code from my clients) with the product name from my stoc, and finally with the CIM (Romanian and own stock code). Because i also have to notify the Romanian authorities when i export to the EU, and the notification is based on the CIM (national code). I will try this tomorrow.

Thank you again!

 

[awoohaw]

i just thought of this: maybe i can start from the Romanian name, correlate it with the clients name, then from that correlate the ECMA (EU code from my clients) with the product name from my stoc, and finally with the CIM (Romanian and own stock code). Because i also have to notify the Romanian authorities when i export to the EU, and the notification is based on the CIM (national code). I will try this tomorrow.

Thank you again!

I think that is what i was trying to write.

 

[Liviu C]

How many different file sources do you have? It seems to me you need to create an intermediate conversion file for each file.
Is there any consistency in how each individual file is created? For instance, if the first file always has the drug name in before a space, and the dose amount is between a space and the letters "mg" you may be able to build a conversion file and keep it in a data source reference file and use that with Power Pivot or Power Query to consolidate and convert the data.

i deeply appreciate your good intentions but what you said is over my knowledge...
I have 12 clients in total. 12 different files, none of them with a certain alignment. But i think i can cut and paste in each one so that i make them the same.
I can give you an example.
One client sends this:

Order valid till

Product

Quantity

Ecma

Price

Another this:

ECMA (M.A.N.)

NAME

Quantity

Price

Or this:

CIM

DEN PROD

FORM

STRENGTH

APP

DCI

AMB

PRI

Cantitati PO Martie 2024

Conditie

Did i understand correctly the question was if i can arrange the columns so that they match in every sheet? Yes i think i can do that.
"mg" stands for milligrams, and they should all have that in they`re name.

This is all i got from you after this i don`t know what you said

 

[awoohaw]

How about this: How about putting sample file on a share drive like Dropbox? Put about 10 records from each vendor file individual worksheets.
So: company one list on Sheet1, company two list on Sheet2, etc.? As long as the data is not proprietary or personal identifying information then you should be able to do that.
Hope that makes sense.

 

[Liviu C]

How about this: How about putting sample file on a share drive like Dropbox? Put about 10 records from each vendor file individual worksheets.
So: company one list on Sheet1, company two list on Sheet2, etc.? As long as the data is not proprietary or personal identifying information then you should be able to do that.
Hope that makes sense.

Yes it does make sense. And I think it would be easier for you to understand as well.
The only problem is that now I have to get my kid to sleep (as it's 11 at night local and hebhas school tomorrow)). I will get on this first thing in the morning and get back to you. Reply when you can I imagine we are not on the same timetable.

Again, I wish I could do more then say thank you!

 

[awoohaw]

Take you time. It is your project and work. If I'm not online when you post it someone else may get on it and provide a solution as well.

 

[Liviu C]

Take you time. It is your project and work. If I'm not online when you post it someone else may get on it and provide a solution as well.

Hi again,

i managed to get some more data on the file. as per your request please find the file here: Copy of ALL IN 1 martie 2024.xlsx

In the "main" sheet is all the info i`m trying to get a better use for.
In the rest of the sheets i inserted "EU" for european clients and "RO" for the local clients (after their name). I only put in a few of them.

I inserted the ECMA codes i already have but you will see there are about 100 lines that i didn`t find codes. Today i searched again and still can`t seem to find them. I also asked a client but he has yet to get back to me.
The product names are is the one i have in my stock program and i need that one to be the base.

Thank you again!