Posts by London.London

Hi Carim,

Thanks for your reply. I have attached a model. If this is not clear, please let me know and I will explain further.

Thanks

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

YTD and MTD performance - Prior Month Data

Hi Everyone, I am a complete begginner using Power Pivot within Excel. I heard there were real geniuses here so that's the reason why I ask my question…

www.mrexcel.com

Hi Everyone,

I am a complete begginner using Power Pivot within Excel. I heard there were real geniuses here so that's the reason why I ask my question here.

I am extracting some data each beginning of month to analyse prior month information.

I have a simple data model with a period dimension (date, month, year, quarter) along with a fact table where we have all the data that I download on a monthly basis.

I would like to create a formula that gives me the YTD and MTD sales for the given period for the current year and also vs last year.

There is also a small issue: in January 2023, I will download the data to analyse December 2022.

If it not clear, let me know and I will do my best to explain better.

Thanks to everyone!

Hi There,

I have various massive files that I am downloading on a monthly basis (maybe 5 or 6). I started created data table in my consolidation workbook where I will create dashboards.

The issue is that the size of the file gets enormous. I heard on Youtube that it is better to create pivot tables using the "external sources" option. I tried that option but the size is still the same.

I wanted to have your opinion on how to create dashboards based on huge data tables? The idea is to have minimize as possible the size of my consolidation workbook.

P.S: In this workbook, I will just use sumifs, vlookups formulas based on several conditions.

Thanks

Hi all,

Thanks for your answers.

Let's take an example with the department called "AV".

I want to apply some filters to the "BO" tab in order to select only the desired rows for the AV department. Once done, I am copying this detail into the "AV Detail" tab. I will then create a new workbook called "AV Expenses" that will include those two sheets.

Trusten, I am using array formulas as this is the only way I found to show the top 5 biggest values that meet multiple condition. For example : 1st biggest of if column A = "Apple" and Column B = "200" and "Column C < 0". Would you have another way to do it?

Roy, how and where would you use pivot tables on my model?

Thanks again much appreciated

Hi Everyone,

I am a begginer with VBA. I have started created a simple code. However, at the end of the vba code, I can see some calculation performed by Excel.

Indeed, on the bottom right, I can see "Calculate : 1%". It takes my Excel maybe 3 minutes to move to 2%.

This is not a big file but I would be sooo helpful if you could help me identifying what is causing this slowness.

Thanks to everyone for your help

Hi Everyone,

I have just discovered slicers and this is great. However, I am facing issues using it in my report. I would like to show the revenue for this year (data_ty) and last year (data_ly) based on the user's selection.

The user will have the opportunity to select one or more items in the slicer (for instance, more than one project or account).

The issue is that I don't even know which formula to use in order to show the user's selection. It gets even more complicated to show the selection when the user selects more than one item in the slicer.

I have included a dummy file (no real info therefore no confidential info).

Thank you all

Hi Everyone,

I have just discovered slicers and this is great. However, I am facing issues using it. I would like to show the revenue for this year and last year based on the user's selection.

The user will have the opportunity to select one or more items (for example more than one project etc.).

The issue is that I don't even know which formula to use to show the user's selection. It gets even more complicated to show the selection when the user selects more than one item in the slicer...

I have included a file that I use in my company.

Best regards,

Hi Everyone,

I was wondering if you had a chance to look at my issue above.

Thanks everyone

Hi NBVC,

Sorry I have just tried your formula that worked perfectly! A big thanks to you!!

Hi NBVC

Thank you it works!!!

Hi Everyone,

I have attached a file with 2 tabs. The first one is the summary tab and the second one is the raw data.

The summary tab shows the revenue by store split in two sections : Solus (Directly Operated Stores) and Concessions. I would like to build a macro that will look at the raw data and add a row in the correct section if a store is missing from the summary report. For example, Windsor and Oxford are not in the summary report in the "Solus" section. Therefore, I would like to add two rows in the "Solus" section, copy down the formulas above and adjust the sum of the solus stores.

I hope my explanation is clear. Feel free to let me know if not.

Thanks

Hi There

Yes I know that I can get this info with a pivot but I need this formula as I am using a report and not a pivot. Not sure if what I need can be done though?

Hi Everyone

I am looking for a formula to get the largest revenue by project. The issue is that I cannot not use a “simple” large and if array formula as the project appears in multiple rows. I need to get the sum by project and show the largest one. I have provided below an exemple where I need to show the top 5 projects for 2018.

Project Name - Year - revenue
Project A - 2018 - 100€
Project A - 2017 - 100€
Project A - 2018 - 200€
Project B - 2018 - 500€
Project C - 2018 - 400€
Project C - 2017 - 400€

Top 3 result in 2018

Project B - 500€
Project C - 400€
Project A - 300€

Thanks guys

Hi Everyone,

I am starting using VBA. I have a question and people suggested to post my request here.

I want to message box 2 or 3 columns from multiple rows in Tab 2. I have given an example below. Based on the example below, for section 1, the message box should show :

" - Alex 10€
- Nathan 30€
- Julie 30€ "

Tab 1 (main)

Section
1
2
3

Tab 2

Section Name Price
1 Alex 10€
2 Dylan 20€
1 Nathan 30€
1 Julie 30€

Thanks everyone

Re: Macro Code to delete specific data in specific columns

Hi Kiran,

Try this and it should work

Option Explicit
Sub DeleteRow()
Dim WS As Worksheet
Dim LastRow As Long
Dim i As Long


LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
Range("A1").Activate
For i = LastRow To 1 Step -1
 
     If Cells(i, 3).Value = "Africa" Or Cells(i, 4).Value = "Information" Then Rows(i).EntireRow.Delete
Next
End Sub

Re: Macro Code to delete specific data in specific columns

OK. Do you have an Excel I can work on? I will build and macro and post it back

Re: New to VBA and want to learn by helping others

I am so glad to see the community active and helping each other in this forum. Thanks again guys. Hopefully, one day, my skills will be as good as yours.

I have found a thread I replied to yesterday so I will try and help the guy.

Actually, I could have a question if you don't mind? Maybe I could prepare an excel file and explain you the question I have to make it easier for you?

Thanks

Re: Macro Code to delete specific data in specific columns

Hi Kiran,

Do you want to delete the whole row or the cell?

Re: New to VBA and want to learn by helping others

Thanks for your prompt reply. What you say makes sense.

Let's say I want to dig through the threads and see if I can help, how can I filter on easy posts only to start with?

Hi Everyone,

I am starting learning VBA and I want to learn how to use this fabulous tool.

Most of people told me the best way to learn is to actually work on small projects. So I thought why not helping people on this forum who have easy questions.

Is it possible to find :

- Simple/Easy questions or problems from members? I could try to resolve it myself and then look at the answers from members
- Alternatively, exercices and solutions

Thank you all and thanks again for this great forum