Overview
Your uncle runs a law firm in Utah, hears you’re pretty good with technology. He gives you a call and says he has a rather large list of clients who haven’t paid him for his services over the last quarter. He has you come into the office and explains that he wants to send a letter to each of the individuals requesting payment. He shows you the excel spreadsheet with some data from exported from their Enterprise Resource Planning (ERP) software, which you have below.
He’s found a service that’ll let him import the spreadsheet to their website and they will send the letters for him. However, the spreadsheet is very particular, and it must be set up to their exact parameters to upload properly. He’s started hardcoding the information onto another sheet formatted correctly but says “There has to be a faster way!” This is where you come in, and you are excited to put your ChatGPT skills to the test.
Instructions
Download the file and to understand the current format of the data and the desired format (Sheet2)
Upload the excel file to ChatGPT 4 (Unfortunately ChatGPT 3.5 is unable to complete this challenge. However, you can get a trial for ChatGPT add-in for Excel. See note in hints)
Ask ChatGPT to copy the names into another sheet and spilt the address into the appropriate columns. Then have ChatGPT input the following message in the language noted in the preferred language column “Please remit payment of xxxx to Fake law firm for our services rendered previously"
The xxxx should equal the appropriate value from “Amount Owed”
Get an updated file and compare the new file to the first 3 rows that were previously hardcoded to make sure they match
Ask ChatGPT to give you a preview of a few lines of the spreadsheet before downloading the file
Sometimes ChatGPT will give you unnecessary information in the chart, so you may need to correct and clarify depending on the response generated
If you do not have ChatGPT 4 you can download a Chatgpt add-in from Microsoft store that has functions such as =ai.extract() and =ai.translate() that will allow you to complete this challenge in another way
Time to complete: 30 minutes
Rating: Intermediate
It was a fun exercise to do, and everything came out easily but the importing of the excel into the ChatGPT was the most time-consuming part.
Time to complete: 30 minutes
Rating: Intermediate
I am new to using ChatGPT 4.0 and this was such a great intro exercise to practice with! Chat originally included the preferred language as a column instead of using it as the message, so I had to go back and get Chat to correct this mistake. Overall, amazing exercise to play around with ChatGPT 4.0 and really see what AI can do!
Time to complete: 20 minutes
Rating: trouble-free
I really enjoyed this challenge, and ChatGPT made it extremely uncomplicated to import the excel. Challenge184_Data_Modified.xlsx (15.5 KB)
Time to complete: 30 minutes
Rating: Intermediate
It was interesting to use ChatGPT, but I can some issues getting the desired output. I finally figured out how to use a good prompt to get the desired results. It was easy to import the Excel to extract the data and ask ChatGPT to create a new sheet with the information.
Time to Complete: 25 minutes
Rating: Intermediate
I love getting to learn more ways to use ChatGPT, and I had no idea you could input an excel file. The hardest part for me was getting the ChatGPT add-in for Excel but now that I have it, I will be using it again.
Time to complete: 30 minutes
Rating: Intermediate
Comments: I found it difficult to figure out how to import the excel into chatgpt. However, once I managed to upload the file, chatgpt was able to generate a clean file quickly, and with little prompting.
This challenge took about 10 minutes to complete. It was beginner-friendly and quite fun to work through! The task provided a great opportunity to practice data transformation and automation skills while solving a practical problem. Formatted_Client_Data.xlsx (10.3 KB)
Time to complete: 20 minutes
Difficulty: Beginner
Comments: Chat was very efficient in transforming this kind of data. There are a lot of useful applications for this sort of assistance from Chat. Challenge184_Updated_.xlsx (17.7 KB)
Time to complete: 15 minutes
Rating: Easy
Thoughts: This was very interesting and highlights how ChatGPT is able to take over a lot of monotonous automation. With the 4o model, my prompt was simply:
Please transfer the data from the first sheet “Who owes us money” to “sheet 2” so that we can send sheet 2 to a company that will mail out letters for us. Follow the format of the first couple examples on sheet 2
After doing a quick validation, it appears to have worked perfectly with that simple prompt!
Time to complete: 15 minutes
Rating: Easy
Comments:This task would have taken a significant amount of time using other tools, even in Excel. However, with ChatGPT, patterns can be easily identified, and data can be processed efficiently into the desired format. It’s impressive to see how powerful ChatGPT is for tasks like data modeling and document creation."
Time to complete: 30 minutes
Rating: Beginner
It was a fun exercise and it really showed me how much ChatGPT is able to do and how fast it can do it. This would have taken me alone at least 15 times longer than chat. Processed_Data_for_Payment_Messages.xlsx (16.4 KB)