Find the minimum value of a range based on presence of a substring in another range
My dataset has series of columns with the pattern
a question column, tagged with one of many possible tags. For example:
What is your year in school (tag - A, B, C)
a response column with some standard series of responses.
I need to aggregate the results by tag for each row - for example, the lowest-number response for all questions in the row tagged with B.
Have been asked to do this in Excel on a mac - normally I would do it in R but that's not going to work this time.
Thanks for any guidance!
Edited to add:
Didn't realize I could drop an image here. So, for example, I would like to be able to see that for questions tagged C, the low response was "2".
If you're just looking for a quick and dirty, one-off solution, you could just drop in some helper formulas to the right of the actual data. You'd have one per target tag, ex.
=IF(ISERROR(SEARCH("A",A2)), "", 1)
Just adjust the target cells and search values and then you'll have a 1 in the row if it contains the target flag, and the sum of that column is the # of rows with the target flag. To enable getting the lowest response # with a flag swap the 1 with the response # (wherever that is) and then the MIN of the column will be what 're looking for (and the MAX would be largest response #, COUNT would get the # of responses with the tag).
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table