SOLVED

To Lookup For Top Name of Column And Row In A Chart For A Specific Value And Create Another Chart

Brass Contributor

Hello There,

 

I need big help.

Attached images is self explanatory.

 

Let me know if you need any other information

 

Let's say, I need to create alert system for a specific date

 

Thanks.

10 Replies

Hi, @mohsin91santono-_-1220

Power Query would be one way to solve this. It comes with Excel in the most recent versions. Which  version of Excel do you use? Can you please attach the file? I will help you with this.

Hi
I am using office 2013
Thanks for the response :)
Could you please help?

@mohsin91santono-_-1220 

Please see here how to install Power Query in your Excel: https://www.myexcelonline.com/blog/install-power-query-with-excel-2013/

 

This is a powerful tool that you don't want to miss in Excel.

Let me know when you have it and please send a sample of the file. I will create an automation with PQ for you on your file. All you will have to do after that is to add new data and click a button to update your results.

 

@Celia_Alves 
Please find the sample file.
Please follow original post to provide formula. (you shall find my original query explained in an image)

Many thanks.

best response confirmed by mohsin91santono-_-1220 (Brass Contributor)
Solution

@mohsin91santono-_-1220 

Here you go! :) I developed a solution only based in formulas and not requiring Power Query.

 

Data Table:

I transformed the data table into an Excel Table. That will allow you to add more data at the bottom without having to update the ranges in the formulas. The formulas will always know that they need to look at the entire data table, regardless of the number of rows.

I added three extra columns to the table that will be responsible to check if the columns B, C and D have dates that coincide with the date in cell I2. You can hide these three helper columns if you'd like.

Cell I2 was given the name ChosenDate.

I added conditional formatting rules to the data table. This way, any date that matches the ChosenDate will be automatically highlighted. If you want this feature to keep working, you cannot paint the cells of the data table.

 

Results Table:

I added two helper columns to this table that are responsible to pick all the matching entries in the data table. You can hide these two columns if you'd like.

I added the formulas to the report columns.

The report table can show up to 25 results. You can copy the formulas down if you need more result rows.

 

The final file is attached.

 

I hope this helps.

Celia Alves 

 

@Celia_Alves 

Hi Celia,

 

Thanks A Lot :D
It works :D
You saved my **bleep** :D

@mohsin91santono-_-1220 

Here is the answer to your message asking how to adapt the formulas for when you want to get the results for dates in between two given date values.

Let me know if you have any questions. 

@Celia_Alves 

WOW.

Thanks again. You are Awesome, dear.
Even I also tried yesterday in separate way , it worked (Thanks to insights already shared by you before)

But you did it more smartly.

Advance Excel is beautiful.

Yes, Excel is the best!

The great thing is that there is no unique solution. I am glad that you were able to find an answer to the challenge on your own. Well done! ;)

1 best response

Accepted Solutions
best response confirmed by mohsin91santono-_-1220 (Brass Contributor)
Solution

@mohsin91santono-_-1220 

Here you go! :) I developed a solution only based in formulas and not requiring Power Query.

 

Data Table:

I transformed the data table into an Excel Table. That will allow you to add more data at the bottom without having to update the ranges in the formulas. The formulas will always know that they need to look at the entire data table, regardless of the number of rows.

I added three extra columns to the table that will be responsible to check if the columns B, C and D have dates that coincide with the date in cell I2. You can hide these three helper columns if you'd like.

Cell I2 was given the name ChosenDate.

I added conditional formatting rules to the data table. This way, any date that matches the ChosenDate will be automatically highlighted. If you want this feature to keep working, you cannot paint the cells of the data table.

 

Results Table:

I added two helper columns to this table that are responsible to pick all the matching entries in the data table. You can hide these two columns if you'd like.

I added the formulas to the report columns.

The report table can show up to 25 results. You can copy the formulas down if you need more result rows.

 

The final file is attached.

 

I hope this helps.

Celia Alves 

 

View solution in original post