SOLVED
Home

Help me create a formula please.

%3CLINGO-SUB%20id%3D%22lingo-sub-377672%22%20slang%3D%22en-US%22%3EHelp%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-377672%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EPlease%20see%20attached%20and%20help%20me%20if%20you%20can.%26nbsp%3B%3C%2FP%3E%3CP%3EBR%2FClau%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-377672%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378438%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378438%22%20slang%3D%22en-US%22%3EHow%20would%20you%20then%20create%20a%20formula%20with%20randbetween%20to%20get%20what%20we%20want%20to%20achieve%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378254%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F264624%22%20target%3D%22_blank%22%3E%40ClauP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERAND()%20and%20RANDBETWEEN()%20always%20recalculate%20when%20something%20changes%20in%20the%20workbook.%3C%2FP%3E%3CP%3ESo%20every%20time%20you%20type%20in%20new%20data%20the%20value%20in%20SKU%20Main%20C%20will%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378204%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378204%22%20slang%3D%22en-US%22%3EHello%3CBR%20%2F%3ESorry%20for%20not%20specify%20better%2C%20I%20wanted%20to%20generate%20a%20random%20number%20A1A%20was%20an%20example%20only.%20Your%20formula%20works%20and%20I%20think%20I%20would%20be%20able%20to%20use%20it%20but%20what%20do%20you%20think%20about%20my%20thoughts%20here%20in%20comment%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%22I%20have%20though%20that%20it%20needs%20an%20IF(B2%3DB1)%20randbetween(0%2C999999)%20and%20release%20a%20value%20in%20the%20next%20column%20along%20side%20the%20identical%20numbers.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DmuAVKDY-UHk%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DmuAVKDY-UHk%3C%2FA%3E%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378200%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F264624%22%20target%3D%22_blank%22%3E%40ClauP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20exactly%20did%20not%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378190%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378190%22%20slang%3D%22en-US%22%3E%3CP%3EBut%20can%20you%20please%20put%20it%20in%20the%20excel%20and%20upload%20it%20here%20then%3F%20To%20really%20see%20that%20it%20works.%20I%20have%20copy%2Fpaste%20and%20didnt%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20though%20that%20it%20needs%20an%20IF(B2%3DB1)%20randbetween(0%2C999999)%20and%20release%20a%20value%20in%20the%20next%20column%20along%20side%20the%20identical%20numbers.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DmuAVKDY-UHk%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DmuAVKDY-UHk%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378187%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F264624%22%20target%3D%22_blank%22%3E%40ClauP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20my%20formula%20does%20exactly%20what%20you%20want.%3C%2FP%3E%3CP%3EMaybe%20someone%20else%20can%20bring%20in%20some%20light.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378170%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378170%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%2C%26nbsp%3B%3Ca%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%3E%40Sergei%20Baklan%3C%2Fa%3E%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20new%20file%20and%20added%20%3F%3F%3F%20mark%20so%20no%20misunderstanding%20will%20occur.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20formula%20which%20randomly%20creates%20an%20article%20when%20two%20cells%20in%20a%20column%20are%20identical.%20The%20result%2Fartnr%20should%20be%20pasted%20in%20the%20next%20column%20aside%20the%20identical%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378134%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378134%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20I%20have%20taken%20that%20and%20inserted%20it%20but%20it%20doesnt%20work.%20The%20thing%20is%20that%20I%20want%20only%20to%20generate%20some%20random%20number%20to%20use%20for%20article%20number.%20Is%20there%20such%20a%20way%20to%20do%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-377793%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20me%20create%20a%20formula%20please.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-377793%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F264624%22%20target%3D%22_blank%22%3E%40ClauP%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DB2%26amp%3BIF(COUNTIFS(%24B%242%3A%24B%2412%2CB2%2C%24K%242%3A%24K%2412%2CK2)%26gt%3B1%2C%22A%22%26amp%3BCOUNTIFS(B%242%3AB2%2CB2%2CK%242%3AK2%2CK2)%26amp%3B%22A%22%2C%22%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
ClauP
Occasional Contributor

Hello

Please see attached and help me if you can. 

BR/Clau

9 Replies

@ClauP 

 

=B2&IF(COUNTIFS($B$2:$B$12,B2,$K$2:$K$12,K2)>1,"A"&COUNTIFS(B$2:B2,B2,K$2:K2,K2)&"A","")

@Detlef Lewin 

 

Hello 

Thank you. I have taken that and inserted it but it doesnt work. The thing is that I want only to generate some random number to use for article number. Is there such a way to do it?

@Detlef Lewin, @Sergei Baklan,

 

I have attached the new file and added ??? mark so no misunderstanding will occur.

 

I need to create a formula which randomly creates an article when two cells in a column are identical. The result/artnr should be pasted in the next column aside the identical numbers.

Solution

@ClauP 

I believe my formula does exactly what you want.

Maybe someone else can bring in some light.

 

But can you please put it in the excel and upload it here then? To really see that it works. I have copy/paste and didnt work.

 

I have though that it needs an IF(B2=B1) randbetween(0,999999) and release a value in the next column along side the identical numbers.

https://www.youtube.com/watch?v=muAVKDY-UHk

Here is quick method to generate randomly alphanumeric texts in Excel. A good lesson for beginners. The following formulas can help you generate random numbers, letters and alphanumeric values in a range in Excel. 1. To create a random 5-digit number between 10000 and 99999, use this formula: ...

@ClauP 

What exactly did not work?

 

Hello
Sorry for not specify better, I wanted to generate a random number A1A was an example only. Your formula works and I think I would be able to use it but what do you think about my thoughts here in comment?

"I have though that it needs an IF(B2=B1) randbetween(0,999999) and release a value in the next column along side the identical numbers.

https://www.youtube.com/watch?v=muAVKDY-UHk"

@ClauP 

RAND() and RANDBETWEEN() always recalculate when something changes in the workbook.

So every time you type in new data the value in SKU Main C will change.

 

How would you then create a formula with randbetween to get what we want to achieve?
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies