SOLVED

Highlighted
New Contributor

Auto populate data based on unique values

Hello friends,

I am looking for a formula in Excel to auto generate values based on data in another column.

Example:

Column A        Column B

Value x         Starting value = 1

Value y         Next value = 2

Value y         Same value =2

Value z.        Next value = 3

Repeat this process up to 5 and then start over again at value 1.

Any support would be greatly appreciated.

3 Replies
Highlighted
Best Response confirmed by Mak_Ludwig (New Contributor)
Solution

Re: Auto populate data based on unique values

In the following formula, I do make the assumption that you enter the starting value in cell B1. This formula then works when you copy it all the way down as the attached spreadsheet shows.

=IFS(AND(A2<>A1,B1=5),1,A2<>A1,B1+1,A2=A1,B1)

(I haven't tried it, but it stands to reason that you could switch around the sequence of those conditions and their consequences, for example, first checking whether A2=A1 and then leaving the value in column B the same. This just happens to be the way I wrote it.)

Highlighted

Re: Auto populate data based on unique values

I just tried it with test data and it worked great!  Thank you so much.

Highlighted

Re: Auto populate data based on unique values

You're very welcome.

As it happens, this is (if memory serves me) the first time I've ever employed the IFS function. I started off trying to nest IF within IF, and was getting confused as I went; said to myself, maybe that IFS function--I'd read about it and seen it in other postings here--would serve well. And not only did it work well; it was also easy to use.

So thank you for that learning opportunity. I'll be using it more myself.