[Solved] Excel Macro Coding Needed – conditional formatting, duplicates, dashboard data [closed]

by


First up, bring your CF conditions into a new column, so that you can directly access the resulting TRUE/FALSE results by either Formulas or VBA.

Depending on what version of Excel you have, you have a number of options to accomplish what you’re trying to do here, including:

  • Pull all the data into VBA using a Variant Array and use Array
    manipulation
  • Pull all the data into VBA using a Variant Array and use Scripting
    Dictionaries
  • If you’ve got Excel 2016 or later, use PowerQuery to pull the data
    into Excel, and shape it how you need it right in the PowerQuery
    interface.
  • If you’ve got Excel 2013 or later, import the separate tables into
    the DataModel and join them on the common field, so that you can
    use a PivotTable to do the reporting you describe. (You can do this
    even if you don’t have the PowerPivot add-in in the SKU of Excel you
    have installed)

Note that both PowerQuery and PowerPivot are available as a free addin if you’re running a version between 2010 and the dates I mention above.

PowerQuery and PowerPivot are by far the easiest way to tackle your issue if you’re new to the above concepts, and there are plenty of resources on the net and videos on YouTube to get you started.

2

solved Excel Macro Coding Needed – conditional formatting, duplicates, dashboard data [closed]