0

I need to use a SharePoint list instead of an excel and I can't find a way to write the following formula. In the excel I use VBA. The case is:

Each user will import the following data: Column A: Month/Year Column B: Location (text) Column C: Category (text)

Column D: unique code (formula):

  • Combine the columns A,B,C: "Location/Category/Month"
  • Read the whole table, count how many items have already been imported for the specific month, location & category, give me the right letter in alphabetical order. First read the month, then the location and last is the category.

Your help will be much appreciated! Thank you!

1 Answer 1

0

According to your needs, you want to auto-increment by comparing [Month], [Location], and [Category].

Since we have no automatic incremental sorting in the form of A, B, C, D, we recommend using 1, 2, 3, 4 instead.

Please follow the steps:

1.Create a list and add the below columns in the list

  • [Month] : Date and Time
  • [Location] : Single line of text
  • [Category] : Choice
  • [The unique code should be] : Calculated
  • [Test111] : Calculated
  • [Auto ID] : Single line of text

enter image description here

2.The [Test111] Formula:

=CONCATENATE(Location,"/",Category,"/",IF(ISBLANK(Month),"",TEXT(Month,"mm")))

enter image description here

3.The [The unique code should be] Formula:

=CONCATENATE([Location],"/",[Category],"/",IF(ISBLANK(Month),"",TEXT([Month],"mm")),"/",[Auto ID])

enter image description here

4.Click Intergrate >> Power Automate >> Create a flow

enter image description here

5.Create the below Microsoft Flow:

enter image description here

6.Save & Test this flow. Here is my test:

enter image description here

3
  • Thank you very much for you quick reply! I run the suggested flow and the results are as shown in the picture. Also, I have created one more column with the correct results. Actually ,the Auto ID column should count how many items have already been imported for the specific month, location & category. Thank you again for your help!!!
    – ANDRIANA
    Dec 13, 2021 at 22:13
  • Hi ANDRIANA. I'm glad I can help you solve the problem. If the answer is helpful to you, you can accept it as answer. The behavior will help others who meet the similar issue in the future quickly find the correct answer. Dec 14, 2021 at 2:06
  • Great, I accepted the answer thank you so much! Do you know if it's possible to add in the flow the action "Send an email notification (V3)" after the "Update item"? I tried it but the following error appears: An action failed. No dependent actions succeeded.
    – ANDRIANA
    Dec 17, 2021 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.