aggregate

  1. O

    Need AGGREGATE to list when category is mixed up with others under one cell

    Hello, I have a Data-Validation Drop Down on Cell A1 that contains Board Game Categories and whenever I click them, I need excel to pull all the Board Games associated with that category and list them down in COLUMN A. So far, my code is only pulling Board Games that has that specific category...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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>...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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>...
  18. 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...
  19. 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
  20. 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...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top