Same Formulas for Multiple Repeating Columns

I have a raw data set that has 277 columns (5 columns per Month).

If you look at the raw excel data, it's actually split in the middle (row 44: Removed Devices). Rows above that are Active Devices and rows below that are Removed Devices.

For each month in this dataset (Jan 2020 to July 2024) I need to calculate:

1. Total cost ex gst

2. Total Cost: Rental

3. Total Cost: Transaction

Total cost ex gst is a sum of column "total cost ex gst" (both active and removed devices)

Total Cost: Rental is a sum of column "rental" (both active and removed devices)

Total Cost :Transaction is a sum of columns Withdrawals + Deposits + Balance Enq (both active and removed devices)

I have been trying to apply Transpose, Cross Tab and Multi Row formula tools for hours now with no luck ">😫!! Could you please guide me how to manipulate this particular data to get desired sums for each month and to display it on the same sheet? Thank you!

raw data 277 columns.xlsx

Workflow

Help

Accepted answers

Qiu

@aiste_griffiths
I forgot that Transaction is a sum of columns Withdrawals + Deposits + Balance Enq (both active and removed devices)

This is the revised on.

1030-aiste_griffiths-r1.yxzp

CoG

Here is another alternative that should be just a tad simpler:

Hope this helps and Happy Solving!

Sample Workflow.yxzp

All comments

flying008

Hi, @aiste_griffiths

You need upload sample data table as your want output!

录制_2024_10_30_11_35_34_651.gif

Input
F1	F2	2020-1-1	2020-01-01_2	2020-01-01_3	2020-01-01_4	2020-01-01_5	2020-2-1	2020-02-01_2	2020-02-01_3	2020-02-01_4	2020-02-01_5
Device	Location	Withdrawals	Deposits	Balance Enq	Rental	Total cost ex gst	Withdrawals	Deposits	Balance Enq	Rental	Total cost ex gst
10	Odense	585.52	27.36	9.2	1745	2367.08	742.04	31.92	9.68	1745	2528.64
11	Aalborg [c]
12	Esbjerg	159.12	25.46	5.44	1745	1935.02	379.6	30.02	8.88	1745	2163.5
13	Randers	28.6	7.6	2.48	1670	1708.68	39	2.28	1.76	1670	1713.04
14	Horsens	348.92	19.76	5.76	1745	2119.44	383.24	17.1	3.36	1745	2148.7
15	Kolding	725.4	42.94	17.2	1745	2530.54	457.6	39.9	15.68	1745	2258.18
16	Vejle
17	Roskilde	915.2	77.14	31.68	1745	2769.02	715.52	73.72	28.16	1745	2562.4
18	Herning	484.12	68.02	27.84	1745	2324.98	414.44	76.76	23.04	1745	2259.24
19	Silkeborg
20	Hørsholm	359.84	66.5	17.44	1745	2188.78	95.68	20.14	8.64	1745	1869.46
21	Helsingør	103.48	16.72	10.72	1745	1875.92	354.12	59.28	17.68	1745	2176.08
22	Næstved	333.84	90.06	15.12	1745	2184.02	330.72	99.94	14.56	1745	2190.22

Output
F1_Device	F2_Location	2020-01-01_Withdrawals	2020-01-01_Deposits	2020-01-01_Balance Enq	2020-01-01_Rental	2020-01-01_Total cost ex gst	2020-01-01_Total cost_Ex Gst	2020-01-01_Total Cost_Rental	2020-01-01_Total Cost_Transaction	2020-02-01_Withdrawals	2020-02-01_Deposits	2020-02-01_Balance Enq	2020-02-01_Rental	2020-02-01_Total cost ex gst	2020-02-01_Total cost_Ex Gst	2020-02-01_Total Cost_Rental	2020-02-01_Total Cost_Transaction
10	Odense	585.52	27.36	9.2	1745	2367.08	2367.08	1745	585.5227	742.04	31.92	9.68	1745	2528.64	2528.64	1745	742.0431
11	Aalborg [c]
12	Esbjerg	159.12	25.46	5.44	1745	1935.02	1935.02	1745	159.1225	379.6	30.02	8.88	1745	2163.5	2163.5	1745	379.63
13	Randers	28.6	7.6	2.48	1670	1708.68	1708.68	1670	28.67	39	2.28	1.76	1670	1713.04	1713.04	1670	392.281
14	Horsens	348.92	19.76	5.76	1745	2119.44	2119.44	1745	348.9219	383.24	17.1	3.36	1745	2148.7	2148.7	1745	383.2417
15	Kolding	725.4	42.94	17.2	1745	2530.54	2530.54	1745	725.442	457.6	39.9	15.68	1745	2258.18	2258.18	1745	457.639
16	Vejle
17	Roskilde	915.2	77.14	31.68	1745	2769.02	2769.02	1745	915.277	715.52	73.72	28.16	1745	2562.4	2562.4	1745	715.5273
18	Herning	484.12	68.02	27.84	1745	2324.98	2324.98	1745	484.1268	414.44	76.76	23.04	1745	2259.24	2259.24	1745	414.4476
19	Silkeborg
20	Hørsholm	359.84	66.5	17.44	1745	2188.78	2188.78	1745	359.8466	95.68	20.14	8.64	1745	1869.46	1869.46	1745	95.682
21	Helsingør	103.48	16.72	10.72	1745	1875.92	1875.92	1745	103.4816	354.12	59.28	17.68	1745	2176.08	2176.08	1745	354.1259
22	Næstved	333.84	90.06	15.12	1745	2184.02	2184.02	1745	333.849	330.72	99.94	14.56	1745	2190.22	2190.22	1745	330.7299

Sum Output
Sum_2020-01-01_Total cost_Ex Gst	Sum_2020-01-01_Total Cost_Rental	Sum_2020-01-01_Total Cost_Transaction	Sum_2020-02-01_Total cost_Ex Gst	Sum_2020-02-01_Total Cost_Rental	Sum_2020-02-01_Total Cost_Transaction
22003.48	17375	4044.2601	21869.46	17375	4265.3475

Running Total Output
F1_Device	F2_Location	2020-01-01_Withdrawals	2020-01-01_Deposits	2020-01-01_Balance Enq	2020-01-01_Rental	2020-01-01_Total cost ex gst	2020-01-01_Total cost_Ex Gst	2020-01-01_Total Cost_Rental	2020-01-01_Total Cost_Transaction	2020-02-01_Withdrawals	2020-02-01_Deposits	2020-02-01_Balance Enq	2020-02-01_Rental	2020-02-01_Total cost ex gst	2020-02-01_Total cost_Ex Gst	2020-02-01_Total Cost_Rental	2020-02-01_Total Cost_Transaction	RunTot_2020-01-01_Total cost_Ex Gst	RunTot_2020-01-01_Total Cost_Rental	RunTot_2020-01-01_Total Cost_Transaction	RunTot_2020-02-01_Total cost_Ex Gst	RunTot_2020-02-01_Total Cost_Rental	RunTot_2020-02-01_Total Cost_Transaction
10	Odense	585.52	27.36	9.2	1745	2367.08	2367.08	1745	585.5227	742.04	31.92	9.68	1745	2528.64	2528.64	1745	742.0431	2367.08	1745	585.5227	2528.64	1745	742.0431
11	Aalborg [c]																	2367.08	1745	585.5227	2528.64	1745	742.0431
12	Esbjerg	159.12	25.46	5.44	1745	1935.02	1935.02	1745	159.1225	379.6	30.02	8.88	1745	2163.5	2163.5	1745	379.63	4302.1	3490	744.6452	4692.14	3490	1121.6731
13	Randers	28.6	7.6	2.48	1670	1708.68	1708.68	1670	28.67	39	2.28	1.76	1670	1713.04	1713.04	1670	392.281	6010.78	5160	773.3152	6405.18	5160	1513.9541
14	Horsens	348.92	19.76	5.76	1745	2119.44	2119.44	1745	348.9219	383.24	17.1	3.36	1745	2148.7	2148.7	1745	383.2417	8130.22	6905	1122.2371	8553.88	6905	1897.1958
15	Kolding	725.4	42.94	17.2	1745	2530.54	2530.54	1745	725.442	457.6	39.9	15.68	1745	2258.18	2258.18	1745	457.639	10660.76	8650	1847.6791	10812.06	8650	2354.8348
16	Vejle																	10660.76	8650	1847.6791	10812.06	8650	2354.8348
17	Roskilde	915.2	77.14	31.68	1745	2769.02	2769.02	1745	915.277	715.52	73.72	28.16	1745	2562.4	2562.4	1745	715.5273	13429.78	10395	2762.9561	13374.46	10395	3070.3621
18	Herning	484.12	68.02	27.84	1745	2324.98	2324.98	1745	484.1268	414.44	76.76	23.04	1745	2259.24	2259.24	1745	414.4476	15754.76	12140	3247.0829	15633.7	12140	3484.8097
19	Silkeborg																	15754.76	12140	3247.0829	15633.7	12140	3484.8097
20	Hørsholm	359.84	66.5	17.44	1745	2188.78	2188.78	1745	359.8466	95.68	20.14	8.64	1745	1869.46	1869.46	1745	95.682	17943.54	13885	3606.9295	17503.16	13885	3580.4917
21	Helsingør	103.48	16.72	10.72	1745	1875.92	1875.92	1745	103.4816	354.12	59.28	17.68	1745	2176.08	2176.08	1745	354.1259	19819.46	15630	3710.4111	19679.24	15630	3934.6176
22	Næstved	333.84	90.06	15.12	1745	2184.02	2184.02	1745	333.849	330.72	99.94	14.56	1745	2190.22	2190.22	1745	330.7299	22003.48	17375	4044.2601	21869.46	17375	4265.3475

Qiu

@aiste_griffiths
This seems to be a real business case.

I am take a bit different way as below, working with the header and the use Regex to seperate Accounting category and Month.

I cross check with Excel manual sum an it seems to be correct.

1030-aiste_griffiths.yxzp

aiste_griffiths

Hi, @flying008

Thanks a million for a quick reply! Wow that looks advanced??!

To be honest with you, sum output on it's own ( as per below) would work as long as it sums up each month (i'm not required to sum up by location).


Sum_2020-01-01_Total cost_Ex Gst	Sum_2020-01-01_Total Cost_Rental	Sum_2020-01-01_Total Cost_Transaction	Sum_2020-02-01_Total cost_Ex Gst	Sum_2020-02-01_Total Cost_Rental	Sum_2020-02-01_Total Cost_Transaction
22003.48	17375	4044.2601	21869.46	17375	4265.3475

I did a little manual testing, looks like Columns : Total cost_Ex Gst and Total Cost_Rental sum up to the correct figure, however Total_Cost_Transaction does not.

If we work with data - Devices 10-22- : Sum_2020-01-01_Total Cost_Transaction should be 4628.48, Sum_2020-02-01_Total Cost_Transaction should be 4494.46.

Qiu

@aiste_griffiths
If I simply sum up the F column in your Excel I can get the 146680 shown in above snapshot.
How did you come up with the 17375?

Qiu

@aiste_griffiths
I forgot that Transaction is a sum of columns Withdrawals + Deposits + Balance Enq (both active and removed devices)

This is the revised on.

1030-aiste_griffiths-r1.yxzp

flying008

Hi, @aiste_griffiths

Yes, you are right. this is my mistake with loss some symbol in formula.

Sum_2020-01-01_Total cost_Ex Gst	Sum_2020-01-01_Total Cost_Rental	Sum_2020-01-01_Total Cost_Transaction	Sum_2020-02-01_Total cost_Ex Gst	Sum_2020-02-01_Total Cost_Rental	Sum_2020-02-01_Total Cost_Transaction
22003.48	17375	4628.48	21869.46	17375	4494.46

aiste_griffiths

Hi @Qiu

Thank you so much for your input! I'll review updated workflow tonight and if all good will Accept As Solution!

p.s. might look like a real business case, but it's fake data.

aiste_griffiths

Hi @flying008

thank you so much for your assistance with above case, would you mind sharing the workflow? I will then review it all in the evening and if all good will accept as solution Thank you!!!!

flying008

Hi, @aiste_griffiths

Due to security policy restrictions, I am unable to upload .yxmd file, so please create your own based on the workflow tool animation.

BTW, @Qiu 's workflow seems much simpler than my process, so please accept his work as solution. wish you both have a good day!

Qiu

@aiste_griffiths
Thank you for your follow-up. Take your time.
I really like this one, maybe we can submit it as weekly challenge idea after your confirmation.

CoG

Here is another alternative that should be just a tad simpler:

Hope this helps and Happy Solving!

Sample Workflow.yxzp

Qiu

@CoG
Happy to see that we seems to have the same numbers😁

aiste_griffiths

Thank you so much @flying008 @Qiu @CoG

what an amazing community! ❤️

Qiu

@aiste_griffiths
Glad we helped.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703