aggregate

  1. willow1985

    Index Match Aggregate - find largest or 2nd largest value

    I have a formula that compares 2 columns and returns the largest value: =INDEX(AH2:AH500,MATCH(MAX(AI2:AI500),AI2:AI500,0)) I would like this formula modified to return the 2nd or even 3rd largest value instead of just the MAX but am not sure how to modify it/apply Aggregate. I am hoping...
  2. M

    Find cell based on multiple identical criteria

    Hello, I'm trying to find names based on multiple identical criteria. For clarity I'm looking to find specific NFL players from an entire league. For instance, there are 3 columns: Players/Position/Team. I want to pull each wide receiver (WR) from the Arizona Cardinals (ARI), subsequently...
  3. M

    read from .txt and extract data

    Hi All, I need to read a txt file and dump the data into a spreadsheet. each txt file with contain two different data sets always starting with DXE and ending with DXS Id like to extract the first set in to column A and second set into column B the data needs to be inserted row by row...
  4. J

    Using Dynamic Arrays and The XlLOOKUP function

    Hello Board: I have created a dynamic crostabulated table using the new XLOOKUP function with SORT and UNIQUE so that I can get dynamic toggle between rows and columns. It worked pretty good. The problem I am having is when I am trying to use SUMIFS to dynamically aggregate both row and column...
  5. N

    How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    I have a table as below. The goal is to extract only first two shop i.d.s from each country having the status either "Active" or "Completed" or "Discontinued". If there are no shops in the country with that status do not extract. If only one than extract only one. Finally the answer should be in...
  6. Y

    Automating pulling data from several sheets into a master sheet

    I helping a friend design a model that consolidate data from several sheets (+6) into a master sheet. His client asked him to design the model so that this process will be automated i.e. the user will fill the data in the input sheets and all the data will be pulled into the master sheet. The...
  7. S

    Converting AGGREGATE formula to VBA code

    I have this formula that works but I can seem to convert it to a VBA code. When I run the code, there is no error, but no value appears. Appreciate your help. My formula: INDEX('[Balanced Portfolio AA_280219.xlsx]Summary Table'!D78:CR298,AGGREGATE(14,6,(ROW('[Balanced Portfolio...
  8. J

    Design question: aggregate data from multiple users with output reporting

    Hi, Working on a project to aggregate monthly reporting revenues for a company using Excel. Outline: User inputs data and revenue for 1 team (4 additional teams need to be able to do this) An aggregate output needs to show all user inputs across all teams Before month-end, user can...
  9. T

    Rows to columns

    Good morning everybody I have a large table which I need to reorganise. It has two key fields and one of data, thus: <tbody> Contract Line Charge ID 10914 1 10010016 10914 1 10010932 10914 2 10020624 10915 1 10000883 10915 2 10010703 10915 2 10010244 10915 2 10010243 </tbody>...
  10. W

    Ignore Zeros in Aggregate Average Function

    I get a feeling this might not be possible, but I have a table that uses the Aggregate function to average a column. It needs to be Aggregate, because that allows me to use slicers to see averages for different items in my table. The problem is, the Aggregate average is averaging the zeros and...
  11. M

    Text function issue

    I'm making a grocery list in Excel. One sheet has my list then next sheet has a listing of my local grocery stores with items, prices and weight. I want to search thru the items against what's in my grocery list for the lowest price. I only have Microsoft Excel 2010. I'm trying to use MIN and...
  12. S

    get cell address from formula result

    Hi I'm making a dashboard where I import raw data reports from an old fashioned ERP system into different sheets. The dashboard sheet then retrieves and calculates different results and combines these into 1 view on 1 sheet.To find the top performing customers YTD, the dashboard retrieves it's...
  13. L

    How do I compute a weighted average for the following?

    <colgroup><col><col><col><col></colgroup><tbody> Store Average Daily Sales Inventory Days On Hand (Inv/Sales) New York, NY 20 40 2 Seattle, WA 25 200 8 Detroit, MI 75 225 3 Dallas, TX 5 50 10 Total 125 515 4.12 </tbody> How to calculate what each stores weight is in the aggregate...
  14. M

    Create dynamic monthly quantities from weekly quantities for multiple data items?

    Hello, I am trying to aggregate weekly quantities into monthly ones. THIS LINK shows the dataset in question, the formula I created to aggregate these subsets, and the organization tactic I used. Is there any way I can make this formula dynamic? I feel like there is an easier way to do this...
  15. F

    Using Aggregate and Index Functions

    I have an office inventory spreadsheet and the following is what I would like to do: Get a running total for quantity even if I sort any column the running total should not break Get Running total sorted by item and should reset when the item name changes Get running total (for visible rows...
  16. S

    Make a list with formula based on criteria

    I have a list of employees in a spreadsheet where I record if they paid their dues or not. I am looking for a formula solution to generate a list of employees that have paid. I understand this can easily be achieved by filtering, but in this particular case, I want to use a formula. <tbody>...
  17. T

    Aggregate Function

    From a previous post I have been using the function at the bottom of the post. I now wish to use the aggregate function again to find the Max value from a different range of cells but still based on only those records that are in a particular year. I have tried modifying the function as below to...
  18. P

    Faster? Subtotal or Aggregate

    Hi Has anyone noticed if AGGREGATE() processes data faster than SUBTOTAL() if you have a lot of data you are calculating? Thanks. Patriciaanne
  19. A

    Power Query: Best way to duplicate data, flip signage, change business unit

    Hi, I have some table data where I would like to generate an aggregate reciprocal position. This relates to modelling allocation of costs within an organisation. E.g. 5 departments a, b, c, d, e receive costs of -1, -2, -3, -4, -5 respectively. I would like to generate in the cleanest and...
  20. M

    suppress dialog boxes vba NOT applicatoin.displayalert = false

    Here's my code: aggregate = "'" & ThisWorkbook.Path & "\" & counter & "\[aggregator.xlsm]" & ws.Name & "'!percent" prior = "[" & counter & ".xlsm]" & ws.Name & "!" & counter & "MBO" If counter = qt Then rng.Formula = "=iferror(" & aggregate & ",""oddly...

Some videos you may like

This Week's Hot Topics

Top