Who is finding genAI actually useful, and in what kinds of tasks? Who has thoughts about what will make it more useful, and when?
I've been trying to find a use for the generally available "generative AI" tools, like ChatGPT 4.0 or Bard or Bing. I mean an actual use that will save me time at work.
I started out asking them to retrieve financial data or calculate financial ratios for publicly traded companies. This seemed great, until I found that the answers were
routinely incorrect. And not by a little.
Today I thought, people say genAI is great for
coding, so I'll ask a genAI tool to do a simple coding task. I asked Bard:
write VB Excel Code to review a range of cells and place a list of all unique values in another range of cells
I got
Sure, here is the VBA code to review a range of cells and place a list of all unique values in another range of cells:
VBA
Sub GetUniqueValues()
'Define variables
Dim rngSource As Range
Dim rngUnique As Range
Dim strValue As String
'Set the source range
Set rngSource = Range("A1:A10")
'Create a new range to store the unique values
Set rngUnique = Range("B1")
'Loop through the source range and add unique values to the unique range
For Each strValue In rngSource
If Not rngUnique.Exists(strValue) Then
rngUnique.Offset(rngUnique.Rows.Count, 0).Value = strValue
End If
Next strValue
End Sub
Use code with caution. Learn more
This code will first define three variables: rngSource, rngUnique, and strValue. rngSource will be used to store the source range of cells, rngUnique will be used to store the unique values, and strValue will be used to store a single value from the source range.
Next, the code will set the source range to Range("A1:A10"). This is the range of cells that we want to review for unique values.
Then, the code will create a new range to store the unique values. This range is set to Range("B1").
Finally, the code will loop through the source range and add unique values to the unique range. For each value in the source range, the code will check to see if the value already exists in the unique range. If it does not exist, the value will be added to the unique range.
The code will then end.
To run this code, you can copy and paste it into the Visual Basic Editor (VBE) in Excel. Then, you can press F5 to run the code. The unique values from the source range will be placed in the unique range.
The problem is that the code wouldn't run. I started the process of giving Bard the error message, it gave me new code and explained how it had fixed the problem, and the new code wouldn't run.
After four iterations I gave up and used
this code, which I found in about 30 seconds using a "standard" Google search.
Sub UniqueValues()
Sheets("InvSale").Range("D11
1510").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("InvSale").Range("V1"), Unique:=True
End Sub
So I asked Bard
Why is your code better than this code
[followed by the code I found]
It said
The code that you provided is also a good way to extract unique values from a range of cells. The AdvancedFilter method of the Range object allows you to filter a range of cells and copy the filtered results to a new range. In this case, the AdvancedFilter method is being used to filter the range Sheets("InvSale").Range("D111510") for unique values and copy the results to the range Sheets("InvSale").Range("V1").
The Unique:=True argument of the AdvancedFilter method ensures that only unique values are copied to the new range. This is a very efficient way to extract unique values from a range of cells.
The main difference between my code and the code that you provided is that my code uses a loop to iterate through the source range and check if each value is unique. This is a more reliable way to extract unique values, but it is also less efficient.
The code that you provided is a good choice if you need to extract unique values quickly. However, my code is a good choice if you need to extract unique values reliably.
And I replied
But your code doesn't run and despite several tries you haven't successfully revised it.
To which Bard said
You are correct. My code does not run because the Sheets("InvSale").Range("D111510") range does not exist. I have tried to revise the code several times, but I have not been able to successfully get it to run.
I believe that the problem with my code is that I am trying to use the AdvancedFilter method on a range that does not exist. The AdvancedFilter method requires a valid range to work properly. If the range does not exist, then the AdvancedFilter method will not work.
I am still learning about Excel VBA, and I am not yet very familiar with the AdvancedFilter method. I am going to continue to try to learn more about the AdvancedFilter method, and I hope that I will be able to revise my code so that it runs properly.
In the meantime, I suggest that you use the code that you provided. The code that you provided is a good way to extract unique values from a range of cells, and it is more efficient than my code.
I apologize for the inconvenience. I am still under development, and I am always learning new things. I will continue to try to improve my code, and I hope that I will be able to provide you with a working solution in the future.
Which is nicely contrite, but its explanation makes no sense.
It's impressive - make that very impressive - that I can have a conversation with Bard and it seems to understand what I want and say, but so far its basically useless - for me. I have been paying $20/mo for Chat GPT 4.0, but think I'll stop.
P.S. The code I used requires the target range (starting at V1) be empty. For some reason it won't simply overwrite those cells.