Excel VB Code to create totals columns based on lookup

[tommill52]

Hi All


Something I cannot get my head around.


I have a worksheet (Analysis1) that contains numeric data in columns 'D' to 'lastCol'. Each of these data columns has a header label in row 2. There can be anything up to 30 different column headers, but these fall into only three 'types'. The 'type' reference for each column label is found in a separate worksheet (ITEM01) that shows the column label in Col A, and the 'type' in Col B. I need to create a 'totals' column for each of the 'types' (TOTAL1, TOTAL2, TOTAL3: stored in 'lastCol' +2 to +4 inclusive of worksheet 'Analysis1').


I hope this makes sense.

 

[Jeffrey Mahoney]

Tom,

Questions to help clarify:

The table you have in ITEM01 is a list of just the columns headers you want to total, or a complete list of column headers? If it's a complete list, can you create a list of just the headers you want to total, or add a 3rd column in that table that identifies the "Total" headers?

Are you asking for a macro that can create a formula in the column just to the right of the last column in your dataset that sums all the values in the same row that match your "Total" column headers?

Jeff

 

[tommill52]

Hi Jeff, apologies but I found this one hard to put into words. Its a bit harder now as I have a feeling that the user would benefit more from some form of 'SUBTOTAL' rather than 'SUMIF' as they need to be able to filter out columns as required but still see the totals.

Below is a link to an example file. The columns that need to hold the totals are on the extreme right. The second table array is the 'lookup' that will be on a different sheet.

The data rows have headers that indicate a warehouse code as a column header. The examples given have codes that all start with '10', eg '10CC'. However, there are a load that start with '20' and '30'. I think the user would like to be able to 'hide' the data columns they don't want to include in the report so that the 'total' for that 'type' is restricted to those that are not hidden (SUBTOTAL(109,xx).

I would be grateful for any help on this.

Tom

https://www.dropbox.com/s/ovxjnss1t58aqxh/sample.xlsx?dl=0

 

[Jeffrey Mahoney]

Sorry Tom,

I'm recognizing that your request is bigger than I originally thought. I won't be able to put in that kind of time right now. You may need to post your question again and break it down into smaller chunks of time requirements.

Jeff

 

[tommill52]

Hi Jeff, thanks for your reply. No problem. I think I have resolved it for now in a non-pretty way. I am using VBA to feed the 'group' ('I', 'O' or 'N' to a new row at the top of the worksheet. I then inserted another new row that contains the width of the column. I am then using 'SUMPRODUCT' to evaluate a) whether the column is hidden and b) which group the column belongs in. It works 'manually', so I am now looking at working out the code.

Thanks again, Tom