You definitely deserve the like and subscribe I just gave you...plus a lot more! You are the only person who provided a reasonable solution to this issue without getting into AppScript or other more complicated approaches. Thank you!
Took a while to understand that you were explaining the basic concept first but it's worth it cos we understand how to apply in different situations. A tip would be to get to the point first and then explain the concept after. Thank you!
I have a table with hundreds of stock trades. I would like exel to auto generate a new unique ID for each trade as I add it to the table. I would like that ID to stick with the row that contains the trade even when I sort the table by various columns (such as date or name of stock etc.). I can get excel to auto generate a number using concate but that makes the numbers long and awkward. Is there a way to get excel to auto generate a Unique ID for each trade so that I can re-sort by ID and get them back in chronological order?
That is an interesting problem. The solution of it depends on how unique ID is generated: 1. Unique ID is generated as an increasing sequence number. 2. Unique ID is generated based on one or more cell values of a particular row. For case 2, sorting will not have any effect. For case 1, we need to keep our original sheet as it is and copy everything and paste everything as values (Paste Special / Paste as Values) into another temporary sheet and perform sorting etc. This requires additional effort of copy and paste, but it would work. If we do not want to go for case 1, we can think of some unique ID that is dependent on cell values of that particular row for which we want to generate unique ID. For example, combination of Scrip and timestamp (date and time represented as a long number) should be sufficient to avoid duplicate id.
Hello, thank you for the thorough video. I'm trying to create the following sequence: AA-0001, once it reaches to AA-9999, it should flip to AB-0001 and so on. This gives over 6+ million possibilities. How would I go about creating this?
If we want to use the sequence for buy and sell transactions, it is possible. We need to add one column in which we select BUY or SELL and based on the value of that column, we can generate BUY-001 then next could be SELL-002
@@petermillett2248 I have provided solution of your problem in the video, How to Generate Customizable Sequence Number in Excel or Google Sheets Automatically, ruclips.net/video/iTkQ1_PY6rg/видео.html
Thank you sir, this works well. Nevertheless, Could the final output (Cloumn A) be done without the whole column B? or could we just merge the formula from column B into column A formula for output because the whole column B seems redundant although it works.
Two columns will be needed because if the output of sequence() is concatenated with some other text in a single formula, then all sequence numbers will be 1 (the starting number for each sequence) and we will not get the desired result.
You definitely deserve the like and subscribe I just gave you...plus a lot more! You are the only person who provided a reasonable solution to this issue without getting into AppScript or other more complicated approaches. Thank you!
Thanks for the sub! Thank you for feedback!
Thank you so very much, you literally saved my life! Very clear and understandable explanation.
You are welcome! Feel happy for it being useful to you.
Took a while to understand that you were explaining the basic concept first but it's worth it cos we understand how to apply in different situations. A tip would be to get to the point first and then explain the concept after. Thank you!
Thank you for your suggestion.
Thank you!! Exactly what I was looking for?
Glad I could help!
Just what I've been looking for. Can't thank you enough. God bless.
This is helpful, Thank you.
Glad it was helpful!
I have a table with hundreds of stock trades. I would like exel to auto generate a new unique ID for each trade as I add it to the table. I would like that ID to stick with the row that contains the trade even when I sort the table by various columns (such as date or name of stock etc.). I can get excel to auto generate a number using concate but that makes the numbers long and awkward. Is there a way to get excel to auto generate a Unique ID for each trade so that I can re-sort by ID and get them back in chronological order?
That is an interesting problem. The solution of it depends on how unique ID is generated:
1. Unique ID is generated as an increasing sequence number.
2. Unique ID is generated based on one or more cell values of a particular row.
For case 2, sorting will not have any effect.
For case 1, we need to keep our original sheet as it is and copy everything and paste everything as values (Paste Special / Paste as Values) into another temporary sheet and perform sorting etc. This requires additional effort of copy and paste, but it would work.
If we do not want to go for case 1, we can think of some unique ID that is dependent on cell values of that particular row for which we want to generate unique ID. For example, combination of Scrip and timestamp (date and time represented as a long number) should be sufficient to avoid duplicate id.
Thanks alot for the simple and clear explanation 👍🏻
Thank you for your feedback.
Pls make a video on how to make a trading journal in Google sheet .thanks for ur efforts
Thank you for your suggestion and input. We will do it in near future.
Hello, thank you for the thorough video.
I'm trying to create the following sequence:
AA-0001, once it reaches to AA-9999, it should flip to AB-0001 and so on. This gives over 6+ million possibilities. How would I go about creating this?
That is an interesting and very useful requirement. Will explore and let you know.
You can find the following video useful to address the solution of the mentioned problem. ruclips.net/video/_WBvJbIz23w/видео.html
If i want to generate a number+letter+date to come out together will that work?
We can add date also using TODAY() function.
Thanks a lot!
its really wonderful and easy solutions
Thank you for your feedback!
Thank you
You're welcome
EXELLENT INFORMATION SIR. SO I WANT 100 & 200 EMA IN WEELY BASIS, HOW FORMULA CREAT ? PLEASE PROVIDE ME.
I like how to explain and why to use this function only
For which problem, you used this function?
@ to generate a number automatically using sequence and counta function
How would this work if you wanted to have ‘buy’ or ‘sell’ with the sequenced number?
Thanks in advance 👍
If we want to use the sequence for buy and sell transactions, it is possible. We need to add one column in which we select BUY or SELL and based on the value of that column, we can generate BUY-001 then next could be SELL-002
@@ApplyIT2021 thanks for the response - could you demonstrate like the first video?
@@petermillett2248 Sure, I will make a video to solve this problem.
@@ApplyIT2021 legend! Thanks
@@petermillett2248 I have provided solution of your problem in the video, How to Generate Customizable Sequence Number in Excel or Google Sheets Automatically,
ruclips.net/video/iTkQ1_PY6rg/видео.html
Thank you sir, this works well.
Nevertheless, Could the final output (Cloumn A) be done without the whole column B? or could we just merge the formula from column B into column A formula for output because the whole column B seems redundant although it works.
I think I figure it out, thanks.
=ARRAYFORMULA(IF(NOT(ISBLANK(F2:F)),"MYPREFIX"&TEXT((SEQUENCE(COUNT(F2:F))),"0000"),""))
Two columns will be needed because if the output of sequence() is concatenated with some other text in a single formula, then all sequence numbers will be 1 (the starting number for each sequence) and we will not get the desired result.
ขอบคุณนะครับ
nice
Thank you for your feedback.
🌹🌹🌹🌹🌹🌹🌹❤❤❤❤❤❤❤❤
👍