How to group small variations on data

Liviu C

New Member
Joined
Apr 10, 2024
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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 ;)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
@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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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 tillProductQuantityEcmaPrice
Another this:
ECMA (M.A.N.)NAMEQuantityPrice
Or this:
CIMDEN PRODFORMSTRENGTHAPPDCIAMBPRICantitati PO Martie 2024Conditie

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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,144
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top