Moving big amounts of sales statistics to one row per product

[Bitter Blue]

Hello. Can someone please help me.

I have been struggling with this for days! In the screenshot you will see sales statistics of several products (A:D) from different branches (E) in specific years (F). In the end the goal is to get the sales statistics per month (G:AD) in one row (see the red squares). Then duplicated rows of a products will be deleted so that i have one unique row per product per store with sale statistics for May 2018 to April 2019.

The problem is shifting sales to one row takes a lot of time (14500 rows!) by hand.

I tried conditional formatting and formulas but I am stuck.

Some products have sold in every year (2017,2018,2019 blue highlight), some in two years (2017,2018 or 2018,2019, etc.green highlight) and some only in one of the years (yellow highlight).

Any ideas on solving this problem? It would be really appreciated!

Regards

Bitter

Problem-01.jpg

[Glenn Kennedy]

No-one is going to want to recreate that...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Bitter Blue]

Hi Glen!

Thank you vermy much for the reply. I am new at forums, so please be patient

I have hopefully followed you're instructions correctly.

On the attatched file there are a few more lines than 20, but I thought this will give a real perspective on the whole file of 14158 lines of data.

The data are truly representative of the real file. Merged cells deleted. I have put the Before data in that sheet and the desired goal in the After sheet.

Hope this helps. Thank you again. Much appreciated.

[alansidman]

I created a Pivot Table using your data to replicate your needs. See attached.

[Bitter Blue]

Glenn

You are my newest hero! Everything worked out. 16000 lines sorted and done!

I totally did not think Pivot Table would be able to cope with the way the data were set up.

When you visit SA we should meet for a beer and biltong!

Thank you very much

[Bitter Blue]

Thank you Alan

It worked! You guys are really helpful. 16 000 lines of data sorted. Please visit SA with Glenn for my treat for a beer and biltong!

[Bitter Blue]

Thank you everybody. Thread SOLVED

[AliGW]

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