Data Formatted incorrectly with date/pricing - chatgpt didn't help! :(

Originally Posted by loutromans

chatgpt didn't help!

Because of course ChatGPT didn't help.

Do you mean that you want all data in column C to be for 2023-08-11, then all data in column E to be for 2023-08-12, and so forth?

Your data also shows time. This will be greatly simplified if you care only about date and not time. Can you clarify what you need?

I think your data layout could be improved by normalizing this so that each row had one product/supplier, and one date. From there you can use other sheets to view the data in different ways for the comparisons you need.

I will look at this again after I make sure I understand your requirements. I suspect that the best way to tackle this may be Power Query, which I do not have experience with.

How many product Name do you have?
I am thinking what if product Name will be running accross columns, and dates are increasing downward by rows

I note many products have more than one price for a given date so are all prices to be reported ?.

1000 UNIQUE products vs 1000 in total [with duplicates (multiple suppliers)] ?

months/years worth of data

Can you clarify as the row/column numbers are going to very large!

Perhaps you need to assess more carefully what type of analysis is required, rather than just simply transforming a large volume of data.

e.g Price comparison between suppliers based on a "month by month" analysis.

this will be a continuous database I'll be adding to daily. So, eventually, it will reach the year's worth of data surely.

I am going to reiterate what 6StringJazzer said, then. If this is going to grow to be a substantial database, start the project off on the right foot by designing the database well from the beginning. I'm not a database person, myself, but I can see a lot of people talking about good database design. I think you will find that these analyses and queries and such will be a lot easier if you design your database well.

I see four fields in your sample, 1) product, 2) supplier, 3) date, 4) price. If there are other fields, identify them now. Build your database like that. Then, when you want to chart the price for a given product over time, it will be a simple query/filter of the raw data.

Edit to add: Power Query is probably the tool of choice for rearranging the data in your sample text file. Unfortunately, I don't know PQ at all, so someone else will have to help with the commands or M code programming to shape the text data.

Please Login or Register  to view this content.

See attached: reformatted data

I don't have PQ and hence no knowledge of: but there are certainly very knowledgeable PQ contributors so hopefully someone will respond.

Hey John,

again I can't thank you enough for what you have done for me.

The VBA code is working great

Have a wonderful life mate

Lou

Here is the Power Query to get your data normalized into a database format that you can then analyze more easily. Personally, with the size you are talking about, I would set up the database in MS Access. You can then run queries on the data. You will have nearly unlimited amount of data that you can input. But enough of that. Here is the Mcode to Unpivot your data

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

I have changed up the code a bit after importing the csv file directly into PQ and not loading it first into Excel.

Please Login or Register  to view this content.

Using this file example you can now pivot your data or graph it or analyze it in any way you wish as it is now in a normalized condition.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.