SOLVED

Auto populate data based on unique values

Copper Contributor

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
best response confirmed by Mak_Ludwig (Copper Contributor)
Solution

@Mak_Ludwig 

 

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.)

 

 

@mathetes 

 

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

@Mak_Ludwig 

 

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.

1 best response

Accepted Solutions
best response confirmed by Mak_Ludwig (Copper Contributor)
Solution

@Mak_Ludwig 

 

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.)

 

 

View solution in original post