formula to sum up values of non-sequential columns

[ruliansyah]

I have a raw data in excel that contains more than a hundred columns and around 35K rows transaction details, I need a solution to summarize the data, some columns contain a series of title like ineligible item 1 to 30, ineligible amount 1 to 30 (USD), ineligible amount 1 to 30 (IDR). the column placement in the file is like:
ineligible item 1, ineligible amount 1 (USD), ineligible amount 1 (IDR), ineligible item 2, ineligible amount 2 (USD), ineligible amount 2 (IDR) and continue to 30. most of the cells filled with "null" and the rest is filled with text and currency.
I have no idea how to sum the amount (USD) and (IDR) based on trx id, ineligible item total and ineligible item by group.
here I attached a sample file to be more clear with the problem. I only put 3 rows of raw data and 10 series of non-sequential columns to make file smaller. raw data will be in RAW sheet, and summary sheet that need to fill. I wonder what formula combination to use. I need to summarize it ASAP, it will take days to do it manually.
please forgive my English.

[José Augusto]

Hi

Have you tried pivot tables? I suppose you need that or even PowerPivot/PowerQuery

[jason.b75]

Assuming that Trx ID will be unique in the raw data, try this in summary D3, fill right and down as needed.

Please Login or Register  to view this content.

[ruliansyah]

Thank you for your reply, i will try the formula as soon as i turn on my laptop again, it's already 3.00 in the morning here. But for sure trx id not really unique, in some cases it may appear 2 or 3 times in raw data with different data as additional of previous ineligible items.
I will update the result here.

[ruliansyah]

I will give a try but i think i prefer to use formula because i can give the formula to other team members easily instead of teaching them to use pivot tables

[José Augusto]

I will give a try but i think i prefer to use formula because ....

Yes, yes, try first and think later, in the order you have in your sentence, because summarizing with pivot tables is better.

[jason.b75]

The formula that I suggested will not be of any use with repeated trx id's. Looking at your sample file again, I don't think that a formula or pivottable will be up to the task.

Is your user profile correct with your version of excel? If not, please update it. It might be possible with power query, but I'm not sure if your listed version of excel supports this.

[ruliansyah]

Thank you mr. jason.b75, I've checked your attachment, it works well for the ineligible amount totals but not working for the details on column L to Y. I'm trying to modify the formula but not yet succeed.
some of my team use excel 2013 and the rest excel 2016.

[kvsrinivasamurthy]

Pl upload file with more data in Raw sheet and show expected results in in all columns of Summary sheet.

[XLent]

I suspect you could achieve with SUMPRODUCT however, given size of real dataset the performance impacts will be significant.

For ex., using your sample file:

Please Login or Register  to view this content.

would generate a number of results, which would be along right lines however, I wouldn't say this is a pragmatic / sustainable approach -- especially when extending scope to include all the calcs to generate the entirety of the table (SUMPRODUCT not appropriate for the simpler ones).

given the layout of the (raw) data you would be best served using either VBA to summarise (should be fast if coded well) or, if possible, PowerQuery -- there a few folk very adept here at PQ, alas I am not one of them!

[jason.b75]

it works well for the ineligible amount totals but not working for the details on column L to Y. I'm trying to modify the formula but not yet succeed.

This is why I said that a formula will not be suitable, I overlooked the difference in the layout of the raw data relating to those columns.

I've managed to produce the correct output (I think I have anyway) using power query, however it is not a straightforward process. I had to manipulate the data in PQ, then export it back to excel, add another column with a formula, then manipulate a second time in PQ.

It should be possible with a single query, if I can't work it out soon then I'll post the file with the double query method to see if anyone else can finish it off.

edit:-

PQ not going to work either, not enough rows in a worksheet to unpivot the data if you really do have 35k rows and 100 columns. (will need 3.5M rows, limit is 1048576).

[XLent]

hi, minor update to my prior post as I realised (belatedly) that I wasn't handling the IDR values - so below revision - for illustration purposes:

Please Login or Register  to view this content.

note: again, to reiterate, I am not championing the above, or any other formula based approach
i.e. if you can use PQ do so, else VBA, as a calc based approach will be slow given size & layout of your dataset...

[ruliansyah]

Thank you all, i'm so sorry for late response, i currently having a little trouble with my little boys so i cannot use my laptop nor go to work but i will try all the suggestions here ASAP. For sure i believe VBA can solve this but it will take time for me to create the VBA.
I reply this on my mobile phone so i cannot try all suggestion directly. I will update again as soon as i have a change to use a laptop.

[jason.b75]

If you're going to insist on formulas, then you will need to keep them as simple as possible, some assumptions and sacrificing consolidation of duplicated trx id's will help, but not much.

In terms of the most practical method,

First choice:- Use VBA
Second choice:- Still VBA
Third choice:- Yep, VBA again.
....Nine hunded and ninety ninth choice:- maybe now think about these formulas, but I would still prefer VBA.
In Summary sheet
A3, fill right to E3 and down as needed.

Please Login or Register  to view this content.

H3, fill down

Please Login or Register  to view this content.

I3, fill down

Please Login or Register  to view this content.

L3, Don't fill yet!

Please Login or Register  to view this content.

M3, Don't fill yet!

Please Login or Register  to view this content.

Select both L3 and M3 together, then drag right to Y3 (you need to drag as a pair for them to alternate when you fill), then fill down.
Wait a week or so for it to calculate

I've updated your sample file with the above formulas in place.

[ruliansyah]

Hello, Sorry for this late reply, I tried some of suggestions here and I found out the combination of solutions given by mr. jason.b75 quite helpful, I think I will use those formulas for now since we are running out of time, I will try to create VBA after this one done.
Thank you so much for everyone here. I really appreciate it.