SharePoint List - Advanced Formula - 3 Criteria

Question

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!

Answer 1

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

2.The [Test111] Formula:

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

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

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

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

5.Create the below Microsoft Flow:

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