Author Topic: Year End Cleanup  (Read 355 times)

theparmans

  • Newbie
  • *
  • Posts: 5
    • View Profile
Year End Cleanup
« on: December 28, 2019, 10:26:52 am »
Is there an Excel Marco to cleanup at year end.  Clean Up would be to remove all sold items from the previous year from the product master. That way when the new year spread sheet is created it will only contain the products not sold instead of all products

Scott

  • Administrator
  • Full Member
  • *****
  • Posts: 214
  • https://mycostpro.com
    • View Profile
    • MyCostPro
Re: Year End Cleanup
« Reply #1 on: December 28, 2019, 11:51:14 am »
Hi,  thats not a bad idea for a future update so the user can clean up their product master tab very quickly whenever they want...  I will look into adding that in the future.
In the mean-time, you have a few options depending on your comfort in using excel.

The easiest way would be to:

Refresh your active item list.
Add a formula to the right side of your Product Master data (either in an unused column, or add a new column header).
Place a formula [if you are using item title to match]  =match(C4, ActiveList!C:C,0)
[or if you are using SKU to match]  =match(G4, ActiveList!M:M,0)
Then add an auto filter, filter by #N/A
Select all rows, right click, select Delete (or select rows and hit CTRL -)

Just save first :)
Let me know if you have any questions or need further help.

theparmans

  • Newbie
  • *
  • Posts: 5
    • View Profile
Re: Year End Cleanup
« Reply #2 on: December 28, 2019, 02:09:57 pm »
So if the column value is #NA the item was not found in the active list so it is assumed to be sold.

Scott

  • Administrator
  • Full Member
  • *****
  • Posts: 214
  • https://mycostpro.com
    • View Profile
    • MyCostPro
Re: Year End Cleanup
« Reply #3 on: December 29, 2019, 09:22:57 am »
Right, when checking for a match, if there is no match it will show #N/A, therefore it is not in your active item list.
Just make sure you see ones that are numbers (instead of NA) to make sure the formula doesnt have a typo.  If the formula is put in wrong they might ALL show NA.

Also, dont forget to update your active item list.

One more tip, to save a backup, you can copy and paste the file in windows, and it will make COPY OF... or you can exit FULL SCREEN within MyCostPro and select SAVE AS from the menu to save it as a new file name.