CODEWITHSUNDEEP
excel
safex
safex

Reputation: 2514

Excel create a group id based on string column

I want excel to create column B. Basically an arbitary id for each "level of A". in Stata this would be egen groupid = group(A) or in R you could use dplyrs group_indices(). Whats the best way to do it in Excel? Preferably a non-VBA solution.

B is what I need

Upvotes: 1

Views: 985

Answers (1)

user11138753
user11138753

Reputation:

Formulas for these situations are better when the first row holds unrelated column labels instead of data.

For your situation, put a 1 in B1 then this formula in B2 and drag down.

=IFERROR(VLOOKUP(A2, A$1:B1, 2, FALSE),SUMPRODUCT(1/COUNTIF(A$1:A2,A$1:A2&"")))

Upvotes: 1

Related Questions