I want to categorize data in google sheets without the help of helper tables by using array function. I cannot figured out how to put incremented values in an array function. For ex, if I have a table like, |Name| |:--:| |cup| |cup| |pencil| |pencil| |book| |table|
I can use a helper table to name each group and use a Vlookup formula to get the below table with the formula,
vlookup($A1,$D$2:$E$4,2,0)
, where $D$2:$E$4 is the reference table.
| Name | Group |
|---|---|
| cup | groupid1 |
| cup | groupid1 |
| pencil | groupid2 |
| pencil | groupid2 |
| book | groupid3 |
| table | groupid4 |
Instead of the reference table I was wondering if I could use a array function or array in the VLOOKUP formula.
For example something like,
{unique(A1:A),if(/referance-to-index-1 of unique(A1:A) != 0/,"GROUPID[1 auto-incremented]","" )}
So that I get an array like this, please ignore the heading,
| Name | Group |
|---|---|
| cup | groupid1 |
| pencil | groupid2 |
| book | groupid3 |
| table | groupid4 |
CodePudding user response：
See my comment to your original post.
That being said, here is my guess as to what you mean.
Suppose that your original list is in Sheet1!A2:A. Try this formula in A1 of some other empty sheet:
=ArrayFormula({UNIQUE(FILTER(Sheet1!A2:A,Sheet1!A2:A<>"")),"GROUPID"&SEQUENCE(COUNTA(UNIQUE(Sheet1!A2:A)))})