SOLVED

Auto populate data based on unique values

%3CLINGO-SUB%20id%3D%22lingo-sub-1288054%22%20slang%3D%22en-US%22%3EAuto%20populate%20data%20based%20on%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288054%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20formula%20in%20Excel%20to%20auto%20generate%20values%20based%20on%20data%20in%20another%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EColumn%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%20B%3C%2FP%3E%3CP%3E%26nbsp%3BValue%20x%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BStarting%20value%20%3D%201%3C%2FP%3E%3CP%3E%26nbsp%3BValue%20y%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BNext%20value%20%3D%202%3C%2FP%3E%3CP%3E%26nbsp%3BValue%20y%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSame%20value%20%3D2%3C%2FP%3E%3CP%3E%26nbsp%3BValue%20z.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Next%20value%20%3D%203%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERepeat%20this%20process%20up%20to%205%20and%20then%20start%20over%20again%20at%20value%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20support%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1288054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288213%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20data%20based%20on%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612556%22%20target%3D%22_blank%22%3E%40Mak_Ludwig%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20following%20formula%2C%20I%20do%20make%20the%20assumption%20that%20you%20enter%20the%20starting%20value%20in%20cell%20B1.%20This%20formula%20then%20works%20when%20you%20copy%20it%20all%20the%20way%20down%20as%20the%20attached%20spreadsheet%20shows.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFS(AND(A2%26lt%3B%26gt%3BA1%2CB1%3D5)%2C1%2CA2%26lt%3B%26gt%3BA1%2CB1%2B1%2CA2%3DA1%2CB1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(I%20haven't%20tried%20it%2C%20but%20it%20stands%20to%20reason%20that%20you%20could%20switch%20around%20the%20sequence%20of%20those%20conditions%20and%20their%20consequences%2C%20for%20example%2C%20first%20checking%20whether%20A2%3DA1%20and%20then%20leaving%20the%20value%20in%20column%20B%20the%20same.%20This%20just%20happens%20to%20be%20the%20way%20I%20wrote%20it.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288225%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20data%20based%20on%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20tried%20it%20with%20test%20data%20and%20it%20worked%20great!%26nbsp%3B%20Thank%20you%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288283%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20populate%20data%20based%20on%20unique%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612556%22%20target%3D%22_blank%22%3E%40Mak_Ludwig%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20very%20welcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20it%20happens%2C%20this%20is%20(if%20memory%20serves%20me)%20the%20%3CEM%3Efirst%20time%3C%2FEM%3E%20I've%20ever%20employed%20the%20%3CSTRONG%3EIFS%3C%2FSTRONG%3E%20function.%20I%20started%20off%20trying%20to%20nest%20IF%20within%20IF%2C%20and%20was%20getting%20confused%20as%20I%20went%3B%20said%20to%20myself%2C%20maybe%20that%20IFS%20function--I'd%20read%20about%20it%20and%20seen%20it%20in%20other%20postings%20here--would%20serve%20well.%20And%20not%20only%20did%20it%20work%20well%3B%20it%20was%20also%20easy%20to%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ESo%20thank%20you%20for%20that%20learning%20opportunity.%20I'll%20be%20using%20it%20more%20myself.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by Mak_Ludwig (New 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.)

 

 

Highlighted

@mathetes 

 

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

Highlighted

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