How to rename data (prior to data output to excel) like I could with a vlookup in excel.

Question

Hello,

I am trying to automate a daily report which would save me a great deal of time everyday. I have the basic operations of my workflow down, but am trying to set in some rules that would take effect before the data is output to an excel sheet.

The 3 big columns I am hoping to change are:

* "Quantity", which is measured in 1000s, so I am hoping to divide each entry in the column by 1,000 before the data is exported.
* "Effective Date", which I am hoping to switch the format of from "yyyy-mm-dd" to "mmmyy" (like Aug22 for Aug 2022 with no day).
* "Location", there are 4 basic locations which I am responsible for reporting on, but after each entry (in the same cell) is a complicated "after label", to show the actual container within the location that each specific shipment came from, which is irrelevant to my task. I'm hoping to do like I used to in excel with vlookups to have something like "every time the base info in a cell says 'A', return 'X"', and so forth, to present the data as cleanly as possible without the container info which no one wants.

For example it might read "New York - A154768T1" and I would like it just to report back something like "NYC" with nothing else.

Please let me know what you think, I would really appreciate any help you can offer.

Henry

davidskaife · Accepted Answer

Hi @Henry_Gunn

Here is one way of doing it:

A formula tool that updates the 'Quantity' field by divides the data by 1000, one that creates a new date field formatted in the right way (as a string field), and a lookup that matches the start of the 'Location' field and replaces the text.