Excel: Transforming automatically a cell based on the input in another one
I'm creating a database. Column A contains different elements. Column B is a ".type" column, so I need to assign one number to each of those elements and that number needs to be consistent across the whole database, as follows:
| Column A | Column B |
|---|---|
| Cow | 1 |
| Chicken | 2 |
| Horse | 3 |
| Butterfly | 4 |
| Cow | 1 |
I need a way to make column B take the number corresponding to each one of the elements of column A. Is there a way to do this with an excel function?
I've tried the IF function, but doesn't seem to work for my purposes, perhaps because I have several elements. It works with only one value and I know which number it's got attributed. For instance:
=IF(A5="Cow", "1", "False")
For this reason, I tried to use multiple arguments, as explained here: Change value in a cell based on value in another cell
However, it gave me error. It said that I had used too many arguments. Example to follow:
=IF(A5="Cow", "1", "False", IF(A5="Chicken", "2", "False"))
Is it possible to do what I want to do? For the values in Column B to be automatically populated when an element in Column A is included, while having several (more than 6) elements in Column A?
=LET(a,UNIQUE($A$1:$A$9),b,SEQUENCE(COUNTA(UNIQUE(a)),,1,1),XLOOKUP(A1,a,b,,0))
Drag it down or:
=LET(c,A1:A9,a,UNIQUE(c),b,SEQUENCE(COUNTA(UNIQUE(a)),,1,1),MAP(c,LAMBDA(z,XLOOKUP(z,a,b,,0))))
Result:
- 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