Getting the next value of the same user if the type is "no type"
I'm trying to write a formula for this table that will show the subsequent value if a row containing the same user has "no type" as its type.
When Orange in column A, for instance, has a type of "no type" in column B, the result in column D will show "milk" since there is another Orange in row 4 that does not have a "no type" in column B. If "no type" is selected, the value will remain unchanged.
In addition, the following user (apple, for example) will only replicate the user's type underneath it rather than the value above.
| Name | Type | Result |
|---|---|---|
| Orange | No Type | Milk |
| Orange | No Type | Milk |
| Apple | Juice | Juice |
| Orange | Milk | Milk |
| Orange | No Type | No Type |
| Apple | No Type | Pie |
| Grapes | Wine | Wine |
| Apple | Pie | Pie |
| Apple | No Type | No Type |
I tried using this formula,
=XLOOKUP(1, ([Name]= [@Name])*([Type]<>"No Type"),[Type], [Type])
However, all of the "No type" are changed regardless whether they are above or below.
| Name | Type | MyFormula(not correct) | Expected Result |
|---|---|---|---|
| Orange | No Type | Milk | Milk |
| Orange | No Type | Milk | Milk |
| Apple | Juice | Juice | Juice |
| Orange | Milk | Milk | Milk |
| Orange | No Type | Milk | No Type |
| Apple | No Type | Juice | Pie |
| Grapes | Wine | Wine | Wine |
| Apple | Pie | Juice | Pie |
| Apple | No Type | Juice | No Type |
| Banana | Shake | Shake | Shake |
=XLOOKUP(1;(A2:$A$11=A2)*(B2:$B$11<>"No Type");B2:$B$11;"No Type")
- Move values from column to rows in Excel
- Application.FileDialog vs GetOpenFilename: What is the difference?
- Only display the values in a certain rank position from filtered and sorted list
- Convert table to reflect column names as row values
- Excel: Dynamic stacking or arrays n-number of times
- Convert Excel formula to Power Query (if countifs)
- Trying to hide rows in excel based on two values
- Combine every unique value from column A with duplicated values into a new sheet
- Import formula from variable - Excel VBA
- VBA Macro to detect unique entries and copy them from one workbook to another is copying a small fraction of duplicates along with the uniques
- Copy paste cells from button row
- Get the value of the second non-empty cell in a row
- SUMIF with UNIQUE criteria for multiple columns, adding all values with same name
- How can I use filter function in Excel to filter based on selections including those that are "ALL"?
- Openpyxl - I can't set cell format and sum values correctly in time format (h:mm)
- how to mask hash users into random values [email protected] in azure databricks
- Find/Replace Row number within Formula
- Extracting & summing data from a single cell in excel
- How to map table references in a JSON file to corresponding values in an Excel file using Python?
- Conditional Formatting to highlight current date on calendar
- Convert number in excel to M or B
- How to have instructional text in Excel?
- How to copy a range to several worksheets by using a list of worksheets to copy to
- VBA - exclude certain boxes from a general check
- How to count resolved comments only via VBA in Excel
- Excel VBA - how do I get the RowSource of a ComboBox to change depending on the value of another Combobox?
- check if a file is open in Python
- How to copy only plain text of cells in Excel?
- VBA - Capitalise range of cells before the last period
- Getting the next value of the same user if the type is "no type"