Excel columns

Resolved · Low Priority · Version 2016

Thu 11 Apr 2019, 17:01 Delegate Steve said...

Steve has attended:
Excel Advanced course

Excel columns

Hi Marius,

I have this report that I spoke to you about on the course last week. What we are trying to do is convert all of the information from multiple rows, into 1 row, but also doing a running total on the report. We have stock with multiple best before dates and multiple quantities. What we need to know is if excel can convert the multiple dates and put them on one row, but also check if each specific date stock will sell out before they run out of shelf life.

Am I able send you the part of the report and you can help me with this?

Kind Regards

Steven

Fri 12 Apr 2019, 12:28 Trainer Sarah said...

RE: Excel columns

Hi Steven

Thanks for your question!

Unfortunately Marius is actually on holiday or training until almost the end of April, however I am more than happy to take a look at your issue.

Please could you send over your file and any extra detail/context that may be useful to info@stl-training.co.uk.

We can then take a look and give you some advice.

Kind Regards,
Sarah
Excel Trainer

Thu 18 Apr 2019, 14:55 Trainer Sarah said...

RE: Excel columns

Hi Steve

Thank you for sending your file over.

I took a look at your request and consulted other colleagues, unfortunately we think it's very difficult to achieve what you outlined.

You currently have your data set out in the optimal way for working in Excel - listed in columns. Rearranging the data to sit in horizontally along one line will take a long time and will actually limit the analytical capability of your data.

I suggest using the multi-level sort tool (found in the Data Ribbon) to organise the data by product and best before date, or using the filter tools. You may also like to use conditional formatting to highlight products nearing best before. Creating a Pivot Table is unlikely to help you achieve what you want, as PT's condense and summarise your data, allowing you to analyse and create reports rather than rearrange data.

Sorry to not be able to help further, but do let us know if you have any other questions in the future.

Kind Regards,
Sarah
Excel Trainer