Excel help - condense columns

Question

Hi all,

I'm wondering if anyone can help me. I've been asked to put a spreadsheet together for a colleague and I'm struggling with one of his requests.

On a section of the spreadsheet, there are currently 11 columns with different headers for employees to fill in with a number depending on what was used on a certain brief. So for some will have a 1 entered, some nothing at all and occasionally some will have 2 or 3 (see attachment 1).

This information then needs to pull through to another sheet on the document as a summary, showing the total for each per month.

I've got the above set up, however, I've been asked to make it more condensed, as he doesn't want 11 columns just for that one section. He's asked if there is anyway of having just one column with a drop down, but I can't work out how to do this as some items will be selected more than once, and also don't know how to then pull that through to the summary.

Does anyone have any suggestions of how i could improve this? I've attached a few screen shots. Any help at all would be greatly appreciated as my excel knowledge has basically been learnt from Google!

Thanks.

Jo

Answer 1

Hi,

Please check whether the following solution is helpful:

To condense the columns, I did the following:

Step 1) I created a helper grid - assigned unique codes.

This helper grid could be anywhere in the same worksheet or another worksheet. After the helper grid is created, it could even be hidden.

Step 2) With limited understanding of Your situation, I created a fictional dataset.

Step 3) Finally, I found the number of occurrences of each type > in a particular month.

i. Value in cell AC2 is 1-1-2021 (dd-mm-yyyy) > using Custom Formatting, I changed the appearance to only show January.

Likewise for cells AF2 & AI2.

ii. Formula in cell AD3 is: =SUMPRODUCT(--(MONTH($W$2:$W$10)=MONTH(AC$2)),--ISNUMBER(SEARCH($AA3,$X$2:$X$10)))

- In the above suggested formula, please change cell reference/ranges to suit Your requirement.

- Please drag the formula down to more rows.

- Please paste the formula in cell AG3 and drag it down to more rows.

Please paste the formula in cell AJ3 and drag it down to more rows.

Whole screenshot

Unanswered question: in the screenshots You shared > in columns X to AH > what is the difference between 1 and 2 and 3? Because I do not know the difference, I did not consider it while creating the solution.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

Hi joanneboggon,

I'm writing to follow up on this thread.

May I know if you need further help? If so, please feel free to contact us. And we will further investigate this issue.

Best Regards,

May