Replacing rows with automation

AdamT

I have a sheet where I automate importing new rows whenever there is a change in spend. I want it to replace the previous row(s) that was imported with the same placement name so only the most recent one is visible. I'm thinking of some kind of formula that would populate on rows that have the same campaign name as other rows and only for rows that are older than the latest version.

So I have one column that is: COUNTIF([Campaign Name]:[Campaign Name], [Campaign Name]@row) = 1), "",COUNTIF([Campaign Name]:[Campaign Name], [Campaign Name]@row

Then a second column that is =IF(OR([Campaign Name]@row = "",COUNTIF([Campaign Name]:[Campaign Name], [Campaign Name]@row) = 1), "",COUNTIF([Campaign Name]:[Campaign Name], [Campaign Name]@row))

I could add to the above formula to exclude anything that is TODAY() but sometimes I may get more than one new entries in the same day. So instead wanted to do a formula where it says if Created date/time is greater than the date/time of all the other rows with the same campaign name, but I haven't been able to figure that out. Alternatively I'm exploring doing a pivot that would have campaign ID in the parent column and Created date/time underneath as child rows then do a Children formula to identify the latest one. However hoping for something faster than having to use a pivot. Also I cannot use reports in my use case because my finished work all have to live within this one sheet.

Genevieve P.

Hey @AdamT

You can use MAX to look at the current date in a Created Date column for this row and see if it's the newest (or Max) date.

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Campaign Name]:[Campaign Name], [Campaign Name]@row)), value if true or rest of your formula

Does that make sense? If that doesn't work for you, it would be helpful to see a screen capture of your sheet and an explanation of what you want to appear in that cell, but please block out sensitive data.

Cheers,

Genevieve

Genevieve P.

Hey @AdamT

You can use MAX to look at the current date in a Created Date column for this row and see if it's the newest (or Max) date.

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Campaign Name]:[Campaign Name], [Campaign Name]@row)), value if true or rest of your formula

Does that make sense? If that doesn't work for you, it would be helpful to see a screen capture of your sheet and an explanation of what you want to appear in that cell, but please block out sensitive data.

Cheers,

Genevieve

AdamT

https://community.smartsheet.com/discussion/comment/400488#Comment_400488

Thank you Genevieve, this was a huge help!