Consolidate columns date values into a report as per period considering category variable

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
Hello guys
I need to consolidate values distributed underneath columns represented by Days.

I just need the consolidation of these values by Category as per period (Week, Month and Year).
Due the quantity of information in my file, using a formula is not feasible. The file has +2k columns and +27k rows.

Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn H(...)
IdCategory01/0101/02/202001/03/202001/04/202001/05/202001/06/2020(...)
1A$124$230$31$43$21$190$
2B$250$18$35$178$420$221$
3C$30$75$470$12$340$110$

I tried using pivot tables, but it recognize the column dates as a field instead as a date.
The final report will be something like below. Where the first column either is Week or Month as per year.

YearCategory ACategory BCategory C
Wk#1$$$
Wk#2$$$
Wk#3$$$
(...)$$$
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Loading Query Table into the sheet is not necessary
file size with QT loaded - 3.97 mb
file size without QT - 1.67 mb
 
Upvote 0
Hii, Through formula it might be less handy,
However using powerquery it is quite simple

Please find the attached link file

Thaks again for your time and replies.

Did you notice that the report is not correct?
When the data is converted to table the dates are assuming the following format JAN/YEARx... where "x" is representing the first column of date. What I meant is that the data converted to table is wrong. We print an error on excel converting the data.
I have different dates meaning different months with data along a period of 5 years. (Jan to Dec)/YEAR.
Check in my spreadsheet the column CQ. Its not January month. Now check in your file converted to table. it calling the date as Jan/202093
 
Upvote 0
Oh it seems that your date format is mm/dd/yyyy.
I am using dd/mm/yyyy so that is the issue. Now try.

convert the mm/dd/yyyy into mmm/yyyy format and try
 
Upvote 0
Oh it seems that your date format is mm/dd/yyyy.
I am using dd/mm/yyyy so that is the issue. Now try.

convert the mm/dd/yyyy into mmm/yyyy format and try
In the spreadsheet I'm using the same format dd/mm/yyyy. I guess the issue can be something else.
 
Upvote 0
or even like this
Still with the same problem with the date format.

Guys thank you so much.
Your insights were perfect.
What I will do based on your feedbacks:

Without converting to table or using additional formulas on my data set (excel)
01- Use the Excel File as data source
02 - Consolidate the values using Power Query
03- Unpivot the columns so the dates will be "transposed to rows".
04 - Transform the dates into years, MonthName and Week#
05- In my excel file Create a Pivot using the Power Query as data.

Check below the idea based on your feedbacks.

>>>Excel-File-Report<<<
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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