Assigning value to the newly created duplicate rows without changing the first entry
I am working on a formula for the column that should tell me whether Vendor Name in my Vendor column is entered for the first time ("New Vendor") or whether it has been already used ("Renewal"). Logic for those values New Vendor/Renewal values is:
New vendor (Meaning first ever request raised for the vendor) if:
- No other requests for this vendor (OR)
- Other requests came in later (so when this request was raised, it was new and it will stay new)
Renewal if:
- At least one other request exists for this vendor that is older than this request even if they were not completed, just initiated.
I tried variety of options: =IF([System Created]@cell < (MIN(COLLECT(Vendor:Vendor, Vendor@row, [System Created]:[System Created]))), "New Vendor", "Renewal") also =IF(countifs(Vendor:Vendor,Vendor@row,[Date Created]:[Date Created],@cell>=[Date Created]@row),<1,"New Vendor", IF(COUNTIFS(Vendor:Vendor, Vendor@row, [Date Created]:[Date Created], @cell <= [Date Created]@row) > 1, "Renewal"))) but I am getting changes to the first ever entry for that Vendor or #UNPARSEABLE result. Any suggestions for the correct formula?
Best Answer
-
Try this...
=IF(COUNTIFS(Vendor:Vendor, @cell = Vendor@row, [Date Created]:[Date Created], @cell < [Date Created]@row) = 0, "New Vendor", "Renewal")
Answers
-
Try this...
=IF(COUNTIFS(Vendor:Vendor, @cell = Vendor@row, [Date Created]:[Date Created], @cell < [Date Created]@row) = 0, "New Vendor", "Renewal")
-
Thanks so much @Paul Newcome Your suggestion worked a treat.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 206 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!