Search results

Wow ETAF, that worked perfectly and such a simple logic. I hit myself hard for not thinking that. I just made a minor change to adjust the month automatically as a parameter. =IF(AND(COUNTIFS($C:$C,C2,$D:$D,D2)<>COUNTIF($C:$C,C2), [@YearMonth]=202208),"Query","") Can I bother you for another...

Hi Gurus, I need some quick help with a formula. I am trying to compare Product prices between few months, where I need to highlight if the price changed from previous month(s). I pasted few scenarios below: Current month is 202308 Product A - It is exactly the same for both months - so no...

Thanks Hui. I am able achieve this slightly differently. My issue is when I try to do the following: Concatenate "and" before the last section number . e.g. Sections 1, 2 and 3 or Sections 2, 4 and 3 Also add "commas" where relevant. e.g. Sections 1, 2, 3 and 4 Output of this formula feeds...

Hi Gurus, I need a clever formula to concatenate few rows. Example below. Depending on the active sections the output in the highlighted cell changes. In the following example it is expected to be Sections 1, 2 and 4. However if 2 3 and 4 are active, the output will be Sections 2, 3 and 4. Also...

Hi All, Took longer. Been in struggle with IT get a power pivot to be installed on my machine. Didn't get the 2016 version yet. Any chances this can be automated with VBA? As there are other tasks I need to execute once the data is transposed. Thanks to all.

Thanks vletm and bluesky. The pivot seems to do the job. I will try and apply it to the main data. @bluesky63 - This seems to be a very good option especially for big data. I need to check if I can get power query working on my office machine. I only looked at macro as there is more...

Hi Excel Gurus, Need help to create a macro to do the following transformation. I though using a Dictionary will help to do the job. But I am at loss on how to do it. Any help is appreciated. I have a larger worksheet over 15K rows to be transformed. Regards, Ravi.

Hi Marc, Apologies for my delay. Been busy with a workshop and didn't get to post back. Yes the code did work for me except for a minor issue. Not sure why, but when I adapted the code to work with my data the column that is synchronous to "Countries from" data is being duplicated. The final...

Thanks Marc. Yes I am comfortable with VBA, though I cannot say the same about Arrays. Unfortunately, it has never been my strong point. And your solution is very innovative. Once I got hang of it, I adjusted the code to work with my original table layout. I am able to build the summary using...

Hi Hany Ali, What Marc sent is sheet code, which you placed within a module. Try opening the Sheet1 code, paste and run. Cheers, Ravi.

Thanks Marc. This is Awesome. I would love to understand what's happening internally. I am trying to adapt it to the original data, which has some intervening columns within the tables. Will get back. Cheers, Ravi.

Hi Excel gurus, I need some help with a specific requirement to merge 3 tables into 1 summary table. Unfortunately, I cannot install or use PowerPivot for this purpose. I attached sample data file and also added screenshot of tables + expected output. Expected output: I am finding it...

Hi Luke, Thanks, I tried this option but this doesn't work for me. I was able to get the following VBA code to do the job as required (though with another issue). Sub FindReplace() ' ' Dim cel As Range, celQ As Range Dim Find As String, Replace As String Dim tblName As String Dim lrow As...

Hi Gurus, I need some help with Find & replace functionality. Just to confirm, I tried many options without any luck. I am able to automate the whole process except for this functionality, forcing me to do the job manually. Requirement below: 1. Mapping worksheet contains a mapping table...

Hi Excel Gurus, I have a weird problem (I never faced it until recent) with one of my excel workbook. Actually occurred multiple times when I worked on copies from history. I am working on some updates to an Excel Workbook with lots of Worksheets, tables and VBA code. The changes involved are...

Yes Yasser, as I did not receive help overnight, thought it would be better to request on Ozgrid as well. Unfortunately, in the rush of things, I forgot to update the cross post in both the forums. Thanks for correcting. I still appreciate any suggestion from gurus, on how to tackle this code...

Hi gurus, I need a quick help. I am looking at comparing three lists (NamedRanges from different workbooks), which have few common values. I am trying to update all 3 NamedRanges by adding the distinct values from others. Typically all 3 lists look alike (without any duplicates) once updated...

Hi Gurus, I need to display a very small info in an descriptive viz for a powerpoint presentation. Data as follows: I need to display both the Company growth and Revenue growth at the same time. Please suggest me the best way to do this. (Sample file uploaded) Thank you, Cheers, Ravi.

Excellent Narayan. Got that working. You are a star... Cheers, Ravi.

Hi Narayan, In this case total should be: 75. Please note that in the data, none of the criteria are blank. It is just the way how the data is being consolidated by all possible combinations possible for further analysis. It is more like: A.....Apple.....Market1.....Buy.....17...

Hi Narayan, Sorry for delay. I checked the formula. It doesn't satisfy the requirement. If Criteria 2, 3 & 4 are blank, the formula should do a sumif based on "Criteria 1". Please check this attached file. Please help me crack this formula :) I am more inclined to find out how this is...

Wow! That's what I like about Chandoo... forums. Get your problem solved within minutes :) @chirayu: Concatenation that is cheeky. Why didn't I remember this. @Narayan: Exactly what I am expecting. I tried the same but applied IF condition for both Criteria and Criteria Range as well :( Thanks...

Hi Gurus, I have this formula requirement, I am unable to solve. I have a table with raw data and another table (below) with possible combinations of criteria to sum the total. I need ONE formula to calculate the sum of "Amount" field for all these combination. I am aware that I can run a...

Hi Hui, Thanks for looking into this. Your formatting is much better. However, isn't there a way to stack the Forecast Vs Actual for every Quarter? That way there will be only 4 Bars for each Quarterly forecast, which should be more usable and readable. Let me know your thoughts? Thank you, Ravi.

Hi All, I have a custom charting requirement. Glad if anyone of can assist me in this. Requirements below: I have yearly data pivoted into Quarterly calculations. Quarterly Forecast and Actual totals. I need to generate a chart which can compare re-forecasts between Forecast/Actual for all...