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.