i'm wondering if someone could offer a solution to the following issue.

an excel application extracts data from a report using index/match. the report contains a finite list of products summarized into groups. the relationship between products and groups is set. one product may be split between two groups, such that one product may appear twice on the report yet be part of two separate group totals. group totals are to the right of products. column headings are only product name or group total, not a combination of both.

the report is dynamic, it will only show a product column if there are numbers for the period. therefore, the same number of products will not be present in the report period to period, nor in the same place.

a calculation worksheet needs to extract elements for each product. is it "better" to have each product "hard coded" on the worksheet and extract the appropriate items (eg first occurrence of product and then second occurrence of product, ie index/match uses a count) and then run a macro to eliminate "zero" columns? or is it "better" to build a macro to extract the products as they appear on the report for the period, copy the formula into the appropriate number of columns and then use a table to allocate "group" relationships? i'm not that good at vba, yet i'm sure someone here can help me ;o)

this report characteristic has come out of left field and has thrown me. i'm open to other suggested solutions.

thanks in advance.