Can you beat chat GPT on this question

Beau the dog · Mar 22, 2023

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-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023
				OLAT							First Aid
				OLAT							Manual handling
				Daily checks

Sheet2

OLAT	Driver hours	First aid	Daily checks	Manual handling	Load Security	LoCITY	Bridge Strikes
5	5	3	5	5	5	5	5

20/03/2021		20/03/2023
02/05/2023
			11/05/2023
	21/04/2023	14/12/2024		15/12/2023

10/08/2023			20/11/2023



15/05/2023

Anthony47 · Mar 22, 2023

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

B

C

D

E

F

G

H

I

J

K

L

M

N

1

gen-23

2

3

4

Daily checks

5

OLAT

6

7

8

9

Sheet1

Cell Formulas
Range	Formula
C1:N1	C1	=EDATE(B1,1)
B3:E3,G3:M3,F3:F5	B3	=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)),"--"))

igold · Mar 23, 2023

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

Beau the dog · Mar 23, 2023

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
B C D E F G H I J K L M N
1 gen-23 feb-23 mar-23 apr-23 mag-23 giu-23 lug-23 ago-23 set-23 ott-23 nov-23 dic-23 gen-24
2
3 -- -- First aid Driver hours OLAT -- -- OLAT -- -- Daily checks Manual handling
4 Daily checks
5 OLAT
6
7
8
9
Sheet1
Cell Formulas
Range Formula
C1:N1 C1 =EDATE(B1,1)
B3:E3,G3:M3,F3:F5 B3 =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)),"--"))

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-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023
--	--	--	--	20/03/2021	--	--	--	--	--	--	0

Beau the dog · Mar 23, 2023

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

Thank you, trying to find a non-VBA solution as my work IT system disables VBA

Anthony47 · Mar 23, 2023

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?

etaf · Mar 23, 2023

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

Beau the dog · Mar 23, 2023

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?

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?

Anthony47 · Mar 23, 2023

For sharing a test file you need to upload it on a filesharing service and then publish the link to access it

Beau the dog · Mar 23, 2023

Anthony47 said:
For sharing a test file you need to upload it on a filesharing service and then publish the link to access it

Driver Training Matrix.xlsx

docs.google.com

I don't know why it has converted it to google sheets...

https://docs.google.com/spreadsheets/d/1YdT4UPRkWSxyeIPWRbRNKhKEpBQYHOa6/edit?usp=share_link&ouid=115654472919774498007&rtpof=true&sd=true

Can you beat chat GPT on this question

Beau the dog

Board Regular

Excel Facts

Anthony47

Well-known Member

igold

Well-known Member

Beau the dog

Board Regular

Beau the dog

Board Regular

Anthony47

Well-known Member

etaf

Well-known Member

Beau the dog

Board Regular

Anthony47

Well-known Member

Beau the dog

Board Regular

Driver Training Matrix.xlsx

Similar threads

Forum statistics

Share this page

Can you beat chat GPT on this question

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock