Macro to find and sort stale price assets

%3CLINGO-SUB%20id%3D%22lingo-sub-1682305%22%20slang%3D%22en-US%22%3EMacro%20to%20find%20and%20sort%20stale%20price%20assets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682305%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20spreadsheet%20which%20contains%20monitoring%20of%20a%20few%20events.%20One%20of%20these%20events%20is%20%22Stale%20market%20price%20date%20check%22.%20This%20are%20all%20in%20column%20AE%20of%20%22exception%20data%20input%22%20tab%20of%20the%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20the%20spreadsheet%20contains%20many%20funds%2C%20I%20am%20hoping%20to%20find%20the%20stale%20priced%20assets%20for%20each%20fund%20when%20selected.%20I%20wish%20to%20show%20in%20columns%20T%20%26amp%3B%20U%20of%20the%20%22summary%22%20tab%2C%20the%20assets%20which%20match%20this%20exception%20for%20the%20fund.%20I%20am%20hoping%20to%20use%20macro%20or%20similar%20to%20find%20a%20way%20to%20find%20the%20assets%20in%26nbsp%3BAE%20of%20%22exception%20data%20input%22%20that%20match%20the%20fund%20in%20cell%20E4%20of%20the%20summary%20tab.%20Then%20sort%20by%20the%20most%20common%20or%20number%20of%20times%20this%20has%20occurred.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%20me%20find%20a%20way%20to%20address%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1682305%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Frequent Contributor

Hi All,

 

I am working on a spreadsheet which contains monitoring of a few events. One of these events is "Stale market price date check". This are all in column AE of "exception data input" tab of the attached. 

 

As the spreadsheet contains many funds, I am hoping to find the stale priced assets for each fund when selected. I wish to show in columns T & U of the "summary" tab, the assets which match this exception for the fund. I am hoping to use macro or similar to find a way to find the assets in AE of "exception data input" that match the fund in cell E4 of the summary tab. Then sort by the most common or number of times this has occurred.

 

Can someone please help me find a way to address this issue.

 

Many thanks,

1 Reply
Highlighted

@calof1 

 

Your sheet is convoluted enough (not that mine aren't) for someone outside of your field, outside of your specialty, to find navigating difficult. So I'm going to point you in a direction that seems to me might well work. If you wish more help, I suggest you give an example not just of the high-level concept, but of an actual instance or two:

  • which fund
  • what stale dates (and where they're to be found)
  • what the results will be, where they'll be displayed,....
  • anything else that would help any one here, people who know Excel but not your particulars, to say "Ah, this function/that macro...that's what is needed."

As it is, you're kind of expecting us to be mind-readers as well as Excel geeks.

 

All that said, as it does look as if you're comfortable with Excel in the main, I'd suggest that some of the newest functions, available only in the newest version of Excel, might work. FILTER in particular, will take multiple criteria, which you have, and apply them to a database, such as you have, and return the results that meet the criteria. Those results can be summed, sorted, listed......

 

Here's a YouTube video that serves as an excellent introduction to that function and some associated ones. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

 

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...