Combing rows to retain latest only the latest data in each column

[DarkV]

I have a complex spreadsheet which uses data from several sources, including a Microsoft Form which employees revisit to update details of their training course, store number etc. over time. The main page of this spreadsheet uses Index/Match formulae to summarise info from the various spreadsheets, one of which is the update sheet. However, the formulae are not finding all of the relevant information, and I think this is because there are multiple lines of data with the same user ID, and it's only finind the first one (or last one). I can change this by altering how the data are sorted, but then I'm missing vital information.

Is there a way to combine multiple rows for the same user/staff number retaining only the latest data entered in each column? The data comes from a constant Microsoft form, and so it feels as if using the ID from the form, rather than the date might work best (there can be issues with the dates in mmddyy vs ddmmyy format which due to restrictions in admin access I'm unable to resolve.

In the example data, I'd need one row with the person's name and staff number, but retaining the latest store number and the accepted, start and finish dates for the course onto one line.

This is a simple example; in reality, the worksheet has around 35 columns and there are hundreds of lines of data for around 200 employees.

Any help would be appreciated!

 

[sijpie]

If I understand it correctly from the above example you would like to get an output of one line for Minnie Mouse (although confusingly in your example she has three different ID numbers). In column G, H, I & J you want the latest info / dates, so : 36, 30/7/22, 1/10/22, 31/3/23

Just clarify the ID nr (I think this was a copy down issue)

To me the easiest is with a macro.I f you are OK with that I can build one.

 

[awoohaw]

Please update your user profile so your version of Excel is displayed on your button. Many solutions for 365/2021 do not work in earlier versions. The forum needs to know how to build the solution.

Also, help the forum help you. Please use the xl2bb add in (link below) to post your data as a mini excel worksheet. It takes time and there could be typos for the forum to recreate your scenario.

To add on to what @sijpie wrote. Minnie Mouse also has two store numbers and one blank one, so what are your tie breakers for ID and Store Number.
You can do this using the UNPIVOT process in Power Query quite easily, PQ has been available since 2013, I think; so you may have that.

a formula process would be to get your unique name list and do a MAXIFS (or sumproducts maximum) for the columns you want maximums of.

 

[awoohaw]

Here is a short example using matrix/array math. You probably need to enter it using the CNTL-SHFT-ENTER keystroke method (it is not necessary in 365 or 2021):

mr excel questions 25 (version 1).xlsb
	A	B	C	D	E	F
1
2
3	Minnie	2023-01-25		Minnie	2023-01-01
4	Mickey	2023-02-05		Mickey	2023-01-01
5	Donald	2023-01-30		Donald	2023-01-15
6				Minnie		2023-01-25
7				Donald		2023-01-30
8				Mickey		2023-02-05
DavkV

Cell Formulas
Range	Formula
B3:B5	B3	=MAX(($E$3:$F$8)*(--($D$3:$D$8=A3)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DarkV]

If I understand it correctly from the above example you would like to get an output of one line for Minnie Mouse (although confusingly in your example she has three different ID numbers). In column G, H, I & J you want the latest info / dates, so : 36, 30/7/22, 1/10/22, 31/3/23

Just clarify the ID nr (I think this was a copy down issue)

To me the easiest is with a macro.I f you are OK with that I can build one.

Thanks for this. The ID is based on the completion of the Microsoft Form; each new completion by a user has a unique ID (and so later completions will have higher IDs). The individual's staff number would be the unique identifier.

 

[DarkV]

Please update your user profile so your version of Excel is displayed on your button. Many solutions for 365/2021 do not work in earlier versions. The forum needs to know how to build the solution.

Also, help the forum help you. Please use the xl2bb add in (link below) to post your data as a mini excel worksheet. It takes time and there could be typos for the forum to recreate your scenario.

To add on to what @sijpie wrote. Minnie Mouse also has two store numbers and one blank one, so what are your tie breakers for ID and Store Number.
You can do this using the UNPIVOT process in Power Query quite easily, PQ has been available since 2013, I think; so you may have that.

a formula process would be to get your unique name list and do a MAXIFS (or sumproducts maximum) for the columns you want maximums of.

THanks for this (unfortunately I can't install the XKL2BB add-in or PowerQuery as there are admin restrictions on my work laptop). The ID is provided by Microsoft Forms, and so each new completion has a new ID - higher numbers for the ID indicate a later completion of the form. Therefore I need to latest data for each column to be combined for each individual (and the Staff Number would be the unique identified for each individual)

 

[awoohaw]

okay, so is the an issue with using the combination of the users first and last names?
Or maybe you should colllect a unique user ID from with in the form?

 

[DarkV]

okay, so is the an issue with using the combination of the users first and last names?
Or maybe you should colllect a unique user ID from with in the form?

Thanks - the staff number is collected which would be the unique user ID for each individual.

 

[sijpie]

Here is VBA code to do this. It will work very fast even on large number of rows.

Option Explicit

Sub CombineTraining()
    Dim vIn As Variant, vOut As Variant
    Dim lRi As Long, lC As Long, lRo As Long, UB1 As Long, _
        UB2 As Long, lColSN As Long
    Dim iNr As Integer
    Dim sStaffNr As String
    Dim colSN As Collection
    Dim binStaffOut As Integer, binStaffIn As Integer, iBit As Integer, binResult As Integer
    
    
    'Read full trainingschedule into array
    vIn = Range("A1").CurrentRegion.Value
    'get nr rows & columns
    UB1 = UBound(vIn, 1)
    UB2 = UBound(vIn, 2)
    
    'get column with staffnr
    For lC = 1 To UB2
        If vIn(1, lC) Like "staff number" Then  '<<<< adjust text to exact heading text
            lColSN = lC
            Exit For
        End If
    Next lC
    
    'get nr of staff in table
    Set colSN = New Collection
    On Error Resume Next    'when you add each staffnumber to the collection, _
                            an error will occur if a duplicate nr gets added. _
                            This line tells the code to continue.
    For lRi = 2 To UB1   'skip header row
        sStaffNr = vIn(lRi, lColSN)
        colSN.Add sStaffNr, sStaffNr
    Next lRi
    On Error GoTo 0         'reset error behaviour
    'colSN.count holds the number of staff in the table
    'resize the output array to have a line for the header and each staff member
    ReDim vOut(1 To colSN.Count + 1, 1 To UB2)
    
    'copy the header row
    For lC = 1 To UB2
        vOut(1, lC) = vIn(1, lC)
    Next lC
    
    lRo = 2
    'now go through the table for each staffnr from the bottom up, _
     checking for details in the 4 columns. Stop when all four are filled
    For iNr = 1 To colSN.Count
        sStaffNr = colSN(iNr)
        binStaffOut = 31    '11111 in binary. Using the five 1's in the number as flags for _
                            which of the four columns has been filled plus 1 _
                            for the rest of the staff info
        For lRi = UB1 To 2 Step -1
            If vIn(lRi, lColSN) Like sStaffNr Then
                binStaffIn = 0  'this variable will hold the flags for each of the columns to be copied
                'find which columns contain info in this line
                iBit = 2    '00010 binary this will be used for column Store Nr
                For lC = UB2 - 3 To UB2
                    If Len(vIn(lRi, lC)) Then
                        ' there is an entry in this column, so set the flag it is to be copied
                        binStaffIn = iBit + binStaffIn
                    End If
                    iBit = iBit * 2     'next bit - it will go from 00010 to 00100 to 01000 to 10000
                Next lC
                binResult = binStaffIn And binStaffOut  ' where there is a 1 in the binary number shows which column to copy
                'the AND operator will only keep 1 in those bits where both binStaffIn and binStaffOut have a 1
                
                'now check which columns need to be copied.
                If binStaffOut And 1 Then       ' if binStaffIn has rightmost bit set, this onl happens first time for each staff
                ' copy staff details
                    For lC = 1 To UB2 - 3
                        vOut(lRo, lC) = vIn(lRi, lC)
                    Next lC
                    binStaffOut = binStaffOut Xor 1
                End If
                If binResult And 2 Then 'copy store nr
                    vOut(lRo, UB2 - 3) = vIn(lRi, UB2 - 3)
                End If
                If binResult And 4 Then 'copy accepted date
                    vOut(lRo, UB2 - 2) = vIn(lRi, UB2 - 2)
                End If
                If binResult And 8 Then 'copy started date
                    vOut(lRo, UB2 - 1) = vIn(lRi, UB2 - 1)
                End If
                If binResult And 16 Then    'copy finished date
                    vOut(lRo, UB2) = vIn(lRi, UB2)
                End If
                'turn off the bits already filled out
                binStaffOut = binStaffOut Xor binResult
            End If
        Next lRi
        lRo = lRo + 1   ' increment the row counter for the output array
    Next iNr
    Range("M1").Resize(colSN.Count + 1, UB2).Value = vOut
End Sub

 

[awoohaw]

Does this work for you:

mr excel questions 27.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Start time	Completion time	Your first name(s)	Your last Name(s)	staff number	Store Number	Accepted on Course	Date Started	Date Finished
2	1	2022-08-01 15:55	2022-08-04 16:56	Minnie	Mouse	12345678	15	2022-07-30
3	2	2022-10-01 13:48	2022-08-22 14:19	Minnie	Mouse	12345678	36		2022-10-01
4	3	2023-03-31 10:39	2022-08-30 10:45	Minnie	Mouse	12345678				2023-03-31
5	4	2022-08-11 15:55	2022-08-21 16:56	Mickey	Mouse	23546987
6	5	2022-10-11 13:48	2022-11-11 14:19	Donald	Duck	35698741	24
7	6	2023-05-16 10:39	2022-10-08 10:45	Micky	Mouse	23546987		2022-10-15
8	7	2022-11-04 15:55	2022-09-26 16:56	Goofy	Dog	9876432
9	8	2023-01-14 13:48	2023-02-05 14:19	Snow	White	6549878	8		2023-01-05
10	9	2023-07-05 10:39	2022-11-19 10:45	Goofy	Dog	9876432				2022-11-25
11	10	2023-01-23 15:55	2022-12-16 16:56	Snow	White	6549878
12
13	Last Name	First Name	Staff Number	Last ID	Last Start Time	Last Completion Time	Last Store Number	Last Accepted on Course	Last Date Started	Last Date Finished
14	Mouse	Minnie	12345678	3	2023-03-31 10:39	2022-08-30 10:45	36	2022-07-30	2022-10-01	2023-03-31
15	Mouse	Mickey	23546987	6	6	2022-10-08 10:45		2022-10-15
16	Duck	Donald	35698741	5	5	2022-11-11 14:19	24
17	Dog	Goofy	9876432	9	9	2022-11-19 10:45				2022-11-25
18	White	Snow	6549878	10	10	2023-02-05 14:19	8		2023-01-05
19
DavkV

Cell Formulas
Range	Formula
B5:C11	B5	=B2+RANDARRAY(1,1,10,100,1)
D14:D18,E15:E18	D14	=MAX(($A$2:$A$11)*($C14=$F$2:$F$11))
E14	E14	=MAX(($B$2:$B$11)*($C14=$F$2:$F$11))
F14:F18	F14	=MAX(($C$2:$C$11)*($C14=$F$2:$F$11))
G14:G18	G14	=IF(SUM(($A$2:$A$11)*($C14=$F$2:$F$11)*($G$2:$G$11<>""))=0,"",INDEX($G$2:$G$11,MAX(($A$2:$A$11)*($C14=$F$2:$F$11)*($G$2:$G$11<>""))))
H14:H18	H14	=IF(SUM(($H$2:$H$11)*($C14=$F$2:$F$11))=0,"",MAX(($H$2:$H$11)*($C14=$F$2:$F$11)))
I14:I18	I14	=IF(SUM(($I$2:$I$11)*($C14=$F$2:$F$11))=0,"",MAX(($I$2:$I$11)*($C14=$F$2:$F$11)))
J14:J18	J14	=IF(SUM(($J$2:$J$11)*($C14=$F$2:$F$11))=0,"",MAX(($J$2:$J$11)*($C14=$F$2:$F$11)))