• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. R

    Excel Formula to compare 2 month's price and highlight if there is a change

    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...
  2. R

    Excel Formula to compare 2 month's price and highlight if there is a change

    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...
  3. R

    Clever formula to concatenate

    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...
  4. R

    Clever formula to concatenate

    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...
  5. R

    Merge rows and Transpose data

    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.
  6. R

    Merge rows and Transpose data

    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...
  7. R

    Merge rows and Transpose data

    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.
  8. R

    Summary table from 3 different tables

    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...
  9. R

    Summary table from 3 different tables

    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...
  10. R

    Summary table from 3 different tables

    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.
  11. R

    Summary table from 3 different tables

    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.
  12. R

    Summary table from 3 different tables

    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...
  13. R

    Custom Find & Replace

    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...
  14. R

    Custom Find & Replace

    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...
  15. R

    Duplicated worksheet objects causing Automation error

    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...
  16. R

    Compare 3 lists and update both

    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...
  17. R

    Compare 3 lists and update both

    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...
  18. R

    Best way to visualize this data in Powerpoint

    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.
  19. R

    Sumifs multiple criteria (only if criteria cell is available)

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

    Sumifs multiple criteria (only if criteria cell is available)

    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...
  21. R

    Sumifs multiple criteria (only if criteria cell is available)

    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...
  22. R

    Sumifs multiple criteria (only if criteria cell is available)

    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...
  23. R

    Sumifs multiple criteria (only if criteria cell is available)

    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...
  24. R

    Custom chart to compare quarterly forecasts

    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.
  25. R

    Custom chart to compare quarterly forecasts

    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...
Back
Top