184|CHATGPT – Law Firm Collections

Challenges
,
1

BYU Student Author: @Dallin_Gardner
Reviewers: @Jacob_Dutton, @Carter_Lee
Estimated Time to Solve:* 15 Minutes

We provide the solution to this challenge using:

  • ChatGPT

Need a program? Click here.

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

  1. Download the file and to understand the current format of the data and the desired format (Sheet2)
  2. 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)
  3. 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"
  4. The xxxx should equal the appropriate value from “Amount Owed”
  5. Get an updated file and compare the new file to the first 3 rows that were previously hardcoded to make sure they match

Data Files

Suggestions and Hints
  • 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

Solution

Challenge184_Solution.xlsx
Solution Video: Challenge 184|CHATGPT – Law Firm Collections

2

Challenge184_Data_Output.xlsx (11.3 KB)
Great challenge! Thank you!

3

12 minutes to complete. Great challenge

4

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.

5 
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!

Corrected_Challenge184_Data.xlsx (11.1 KB)

6

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)

7

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.

8

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.

9

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.