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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
931
Office Version
  1. 365
Platform
  1. 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
324
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
931
Office Version
  1. 365
Platform
  1. Windows
Is the format acceptable to you???
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324

ADVERTISEMENT

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
931
Office Version
  1. 365
Platform
  1. 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
931
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
931
Office Version
  1. 365
Platform
  1. 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
324
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
931
Office Version
  1. 365
Platform
  1. Windows
Can you send the data using dropbox or Google drive. ???
It should work
 

Watch MrExcel Video

Forum statistics

Threads
1,133,145
Messages
5,657,094
Members
418,355
Latest member
michaelirl

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
Top