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

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
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$$$
(...)$$$
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
523
Office Version
365
Platform
Windows
You can use Transpose function to convert those column dates to row like this

Book1
BCDEFGH
1Category01-Jan01/02/202001/03/202001/04/202001/05/202001/06/2020
2A$124$230$31$43$21$190
3B$250$18$35$178$420$221
4C$30$75$470$12$340$110
5
6
7
8CategoryABC
901/01/2020$124$250$30
1001/02/2020$230$18$75
1101/03/2020$31$35$470
1201/04/2020$43$178$12
1301/05/2020$21$420$340
1401/06/2020$190$221$110
15
Sheet3
Cell Formulas
RangeFormula
B8:E14B8=+TRANSPOSE(B1:H4)
Dynamic array formulas.


Please tell me how would you like to represent the dates??? is it week wise or row wise
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
Thanks for your reply. The problem is the quantity of data that makes the use of this approach impossible.
As I mentioned there are more than 27 thousand rows so I cannot transpose rows to columns due excel limitation.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
523
Office Version
365
Platform
Windows
Is the format acceptable to you???
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
Actually instead of days would be Month Name/Year and Week#. See table below.
I put year 2025 as an example. The year as per dates on my interval.

Month/YearWeek#Category ACategory BCategory C(...) So on
Jan/20201$$$$
Jan/20202...$$$$
(...)/2020(...)$$$$
Dec/202049$$$$
Jan/20251$$$$
(...)/2025(...)$$$$
Dec/202552$$$$
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
523
Office Version
365
Platform
Windows
Is this acceptable

Book1
ABCDE
3Sum of ValueCategory
4Year/weekNumberABCGrand Total
5202022301875323
6202054317812233
72020712425030404
82020723135470536
92021221420340781
10202123190221110521
11Grand Total639112210372798
Sheet7


I have done using power Query.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
523
Office Version
365
Platform
Windows
Try This

Book1
ABCDEFGH
1
2Category01/01/202001/01/202001/04/202031/12/202001/05/202101/04/2022
3A$124$230$31$43$21$190
4B$250$18$35$178$420$221
5C$30$75$470$12$340$110
6
7Jan/2020Jan/2020Apr/2020Dec/2020May/2021Apr/2022
8CategoryJan/2020Jan/20202Apr/2020Dec/2020May/2021Apr/2022
9A$124$230$31$43$21$190
10B$250$18$35$178$420$221
11C$30$75$470$12$340$110
12
13Sum of ValueCategory
14AttributeABCGrand Total
15Apr/20203135470536
16Apr/2022190221110521
17Dec/20204317812233
18Jan/202012425030404
19Jan/202022301875323
20May/202121420340781
21Grand Total639112210372798
22
23
Sheet8
Cell Formulas
RangeFormula
B7:G7B7=TEXT(B2,"MMM")&"/"&YEAR(B2)
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
523
Office Version
365
Platform
Windows
You can also use formula based answer like

Book1
ABCDEFGHIJKLMNOPQR
1
201/01/202001/01/202001/04/202031/12/202001/05/202101/04/2022
3CategoryJan/2020Jan/2020Apr/2020Dec/2020May/2021Apr/2022You can convert the column dates to Row using Transpose and Select Pivot to remove duplicates
4A124230314321190
5B2501835178420221
6C307547012340110
7
8AttributeABCGrand Total
9Apr/20203135470
10Apr/2022190221110
11Dec/20204317812
12Jan/2020354268105
13May/202121420340
14Grand Total
15
16
17
18
19
Sheet8
Cell Formulas
RangeFormula
B9:D13B9=SUM(IF($A$4:$A$6=B$8,IF($B$3:$G$3=$A9,$B$4:$G$6)))
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
Thanks again for your replies.

I tried here your suggestions and I faced an interesting bug. The concatenation result in a wrong value. Jan/202225...Also the file became very slow due the processing of such amount of information.
Just to be aware of my range, see below.

My Column range for the dates is C2:BSV2 meaning a total of 1.866 columns.
My Column range for the category/Data is B2:B27500.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
523
Office Version
365
Platform
Windows
Can you send the data using dropbox or Google drive. ???
It should work
 

Watch MrExcel Video

Forum statistics

Threads
1,102,115
Messages
5,484,822
Members
407,467
Latest member
spurs50

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top