Can you beat chat GPT on this question

B

Beau the dog

Board Regular
Joined
Mar 8, 2021
Messages
74
Office Version
  1. 365
Platform
  1. Windows
I may not be asking Chat GPT the right question, or in the right way. So I'm going to try here, as this forum has always come up with the right answer!

One Sheet1 in cells A1:L1 I have - Jan-2023, feb-2023, Mar-2023 and so on up to Dec-2023.
On Sheet2 I have a list of training course titles in cells V5:AC5 (8 titles). starting 3 rows below that (ignore the two rows just beneath the course titles) are dates in which these course are completed, each row represents one member of staff. There are also future dates added, in which courses are booked.

The challenge is....underneath the month dates in sheet1, to have a list of all the future training course titles that are booked underneath the correct month and year in Sheet1

For example, May-2023 would look like the example below, as there are two OLAT courses booked, and one Daily Check course booked in May 2023
Dec-2023 would look like the below, as one First aid course and one manual handling course are booked in the future

As always, any help would be greatly appreciated.

Sheet1
Jan-2023Feb-2023Mar-2023Apr-2023May-2023Jun-2023Jul-2023Aug-2023Sep-2023Oct-2023Nov-2023Dec-2023
OLATFirst Aid
OLATManual handling
Daily checks


Sheet2
OLATDriver hoursFirst aidDaily checksManual handlingLoad SecurityLoCITYBridge Strikes
55355555
20/03/202120/03/2023
02/05/2023
11/05/2023
21/04/202314/12/202415/12/2023
10/08/202320/11/2023
15/05/2023
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Chatgpt has a tendency to invent plausible but untrue situations, I wouldn't trust what it offers me

1) I created on Sheet1, from B1 to the right, the sequence mmm-yyyy
B1 contains the date 1-jan-2023
C1 contains the formula=EDATE(B1,1) then copied to the right

2) Then copied the training courses in Sheet2 V5:AC100

3) Then I got the list of the Jan-2023 courses (no one) by setting in C3 the formula
Excel Formula: 
=LET(Tutto,Sheet2!$V$5:$AC$100,Uno,IF((Tutto<>"")*(IFERROR(IF(YEAR(Tutto)>2020,TRUE,FALSE),FALSE)),COLUMN(Tutto)-COLUMN(INDEX(Tutto,1,1))+1&"#"&Tutto,""),Tuno,TEXTJOIN("|",TRUE,Uno),cCol,TEXTSPLIT(Tuno,"#","|"),inMese,FILTER(cCol,TEXT(INDEX(cCol,0,2),"yyyy-mm")=TEXT(B$1,"yyyy-mm")),IFERROR(INDEX(INDEX(Tutto,1,0),1,INDEX(inMese,0,1)),"--"))

3) Copy C3 to the right for as many headers you have in Row1

And got the following output

MULTI_C30319.xlsm
BCDEFGHIJKLMN
1gen-23feb-23mar-23apr-23mag-23giu-23lug-23ago-23set-23ott-23nov-23dic-23gen-24
2
3----First aidDriver hoursOLAT----OLAT----Daily checksManual handling
4Daily checks
5OLAT
6
7
8
9
Sheet1
Cell Formulas
RangeFormula
C1:N1C1=EDATE(B1,1)
B3:E3,G3:M3,F3:F5B3=LET(Tutto,Sheet2!$V$5:$AC$100,Uno,IF((Tutto<>"")*(IFERROR(IF(YEAR(Tutto)>2020,TRUE,FALSE),FALSE)),COLUMN(Tutto)-COLUMN(INDEX(Tutto,1,1))+1&"#"&Tutto,""),Tuno,TEXTJOIN("|",TRUE,Uno),cCol,TEXTSPLIT(Tuno,"#","|"),inMese,FILTER(cCol,TEXT(INDEX(cCol,0,2),"aaaa-mm")=TEXT(B$1,"aaaa-mm")),IFERROR(INDEX(INDEX(Tutto,1,0),1,INDEX(inMese,0,1)),"--"))
 
Upvote 0
Solution
Alternatively a VBA solution...
VBA Code: 
Sub AssignCourse()

    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    Dim arr, LC As Range
    Dim i As Long, col As Long, yr As Long, x As Long
    
    Application.ScreenUpdating = False
    ws2.Activate
    Set LC = ws2.Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
    yr = Format(ws1.Range("A1"), "yyyy")
    
    For x = 0 To 7
        arr = ws2.Range(Cells(5, 22 + x), Cells(LC.Row, 22 + x))
        For i = 5 To LC.Row - 4
            If IsDate(arr(i, 1)) And Format(arr(i, 1), "yyyy") = yr Then
                col = Format(arr(i, 1), "mm")
                ws1.Cells(ws1.Cells(Rows.Count, col).End(xlUp).Row + 1, col) = arr(1, 1)
            End If
        Next
        Erase arr
    Next
    ws1.Activate
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Anthony47 said:
Chatgpt has a tendency to invent plausible but untrue situations, I wouldn't trust what it offers me

1) I created on Sheet1, from B1 to the right, the sequence mmm-yyyy
B1 contains the date 1-jan-2023
C1 contains the formula=EDATE(B1,1) then copied to the right

2) Then copied the training courses in Sheet2 V5:AC100

3) Then I got the list of the Jan-2023 courses (no one) by setting in C3 the formula
Excel Formula: 
=LET(Tutto,Sheet2!$V$5:$AC$100,Uno,IF((Tutto<>"")*(IFERROR(IF(YEAR(Tutto)>2020,TRUE,FALSE),FALSE)),COLUMN(Tutto)-COLUMN(INDEX(Tutto,1,1))+1&"#"&Tutto,""),Tuno,TEXTJOIN("|",TRUE,Uno),cCol,TEXTSPLIT(Tuno,"#","|"),inMese,FILTER(cCol,TEXT(INDEX(cCol,0,2),"yyyy-mm")=TEXT(B$1,"yyyy-mm")),IFERROR(INDEX(INDEX(Tutto,1,0),1,INDEX(inMese,0,1)),"--"))

3) Copy C3 to the right for as many headers you have in Row1

And got the following output

MULTI_C30319.xlsm
BCDEFGHIJKLMN
1gen-23feb-23mar-23apr-23mag-23giu-23lug-23ago-23set-23ott-23nov-23dic-23gen-24
2
3----First aidDriver hoursOLAT----OLAT----Daily checksManual handling
4Daily checks
5OLAT
6
7
8
9
Sheet1
Cell Formulas
RangeFormula
C1:N1C1=EDATE(B1,1)
B3:E3,G3:M3,F3:F5B3=LET(Tutto,Sheet2!$V$5:$AC$100,Uno,IF((Tutto<>"")*(IFERROR(IF(YEAR(Tutto)>2020,TRUE,FALSE),FALSE)),COLUMN(Tutto)-COLUMN(INDEX(Tutto,1,1))+1&"#"&Tutto,""),Tuno,TEXTJOIN("|",TRUE,Uno),cCol,TEXTSPLIT(Tuno,"#","|"),inMese,FILTER(cCol,TEXT(INDEX(cCol,0,2),"aaaa-mm")=TEXT(B$1,"aaaa-mm")),IFERROR(INDEX(INDEX(Tutto,1,0),1,INDEX(inMese,0,1)),"--"))
Click to expand...

Thank you, but when I enter the formula (which is way beyond my understanding!), I get one date back in the wrong column, rather than the course names, which isn't a future date?

Might it be the column formula within the formula? I have moved the location of the example below on Sheet1 to G5:R5 (Apologies!)

Jan-2023Feb-2023Mar-2023Apr-2023May-2023Jun-2023Jul-2023Aug-2023Sep-2023Oct-2023Nov-2023Dec-2023
--------20/03/2021------------0
 
Upvote 0
igold said:
Alternatively a VBA solution...
VBA Code: 
Sub AssignCourse()

    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    Dim arr, LC As Range
    Dim i As Long, col As Long, yr As Long, x As Long
   
    Application.ScreenUpdating = False
    ws2.Activate
    Set LC = ws2.Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
    yr = Format(ws1.Range("A1"), "yyyy")
   
    For x = 0 To 7
        arr = ws2.Range(Cells(5, 22 + x), Cells(LC.Row, 22 + x))
        For i = 5 To LC.Row - 4
            If IsDate(arr(i, 1)) And Format(arr(i, 1), "yyyy") = yr Then
                col = Format(arr(i, 1), "mm")
                ws1.Cells(ws1.Cells(Rows.Count, col).End(xlUp).Row + 1, col) = arr(1, 1)
            End If
        Next
        Erase arr
    Next
    ws1.Activate
    Application.ScreenUpdating = True
   
End Sub
Click to expand...

Thank you, trying to find a non-VBA solution as my work IT system disables VBA :confused:
 
Upvote 0
Can you attach an XL2BB view of your area, so I can see the formulas behind the values? Or share a sample workbook using a file share service, like dropbox, googledrive, onedrive and similar

Is "English" your Office language?
 
Upvote 0
i have been playing with chatgpt for a bout a month now - and also provided a solution and then tried various questions on chatgpt - not great answers - it covers a lot of the basic questions ok, but then anything else it gets wrong a lot , and if you question to try and get a better response , it tends to go around in a circle - thats for functions , apparently its not to bad with code , VBA etc
so for anything but the simplest questions , i have not got a correct solution yet, so the forums are better
 
Upvote 0
Anthony47 said:
Can you attach an XL2BB view of your area, so I can see the formulas behind the values? Or share a sample workbook using a file share service, like dropbox, googledrive, onedrive and similar

Is "English" your Office language?
Click to expand...
For some reason I can never get XL2BB on my excel, I can download, install, but cant get it on the ribbon...

I can file share?
 
Upvote 0
For sharing a test file you need to upload it on a filesharing service and then publish the link to access it
 
Upvote 0
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
Fluff
Fluff
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
761
Fluff
Fluff
T
  • Question Question
SUMIFS not Working
Replies
3
Views
354
Peter_SSs
Peter_SSs
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel
H
  • Question Question
search for macro to sum amount for each sheet based on month
2
Replies
10
Views
816
Hasson
H

Forum statistics

Threads
1,224,828
Messages
6,181,206
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top