Record consolidation

[tigerzen]

I have the following situation. Information is recorded in separate rows which needs to be consolidated into a single row so that a single letter detailing each activity can be generated. The spreadsheet shows the current situation, row 9 and below shows the required output. Note, the person I'm helping is not familiar with Power Query so I'm seeking a formula based approach if possible. Next choice would be a macro if it's simple enough to adapt to the actual data.

Sample 2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Faculty	Work completed	Hours	Cost Centre
2	Sam	Medicine	Lecture	2	Nursing
3	Sam	Arts	Tutorial	3	Admin
4	Jack	Psychology	Lecture	1	Admin
5	Jack	Arts	Tutorial	4	General
6	Jack	Arts	Tutorial	3	General
7
8
9	Name	Faculty	Work completed	Hours	Cost Centre	Faculty	Work completed	Hours	Cost Centre	Faculty	Work completed	Hours	Cost Centre
10	Sam	Medicine	Lecture	2	Nursing	Arts	Tutorial	3	Admin
11	Jack	Psychology	Lecture	1	Admin	Arts	Tutorial	4	General	Arts	Tutorial	3	General
Sheet1

 

[JEC]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Faculty	Work completed	Hours	Cost Centre
2	Sam	Medicine	Lecture	2	Nursing
3	Sam	Arts	Tutorial	3	Admin
4	Jack	Psychology	Lecture	1	Admin
5	Jack	Arts	Tutorial	4	General
6	Jack	Arts	Tutorial	3	General
7
8
9	Name	Faculty	Work completed	Hours	Cost Centre	Faculty	Work completed	Hours	Cost Centre	Faculty	Work completed	Hours	Cost Centre
10	Sam	Medicine	Lecture	2	Nursing	Arts	Tutorial	3	Admin
11	Jack	Psychology	Lecture	1	Admin	Arts	Tutorial	4	General	Arts	Tutorial	3	General
12
13
Sheet1

Cell Formulas
Range	Formula
A10:M11	A10	=IFERROR(DROP(REDUCE("",UNIQUE(A2:A6),LAMBDA(a,b,VSTACK(a,HSTACK(b,TOROW(FILTER(B2:E6,(A2:A6=b),"")))))),1),"")
Dynamic array formulas.

 

[mohadin]

Hi
a VBA Option

Sub test()
Dim a
Dim i&
a = Cells(1, 1).CurrentRegion
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(a)
        If Not .Exists(a(i, 1)) Then
        .Add a(i, 1), a(i, 2) & "|" & a(i, 3) & "|" & a(i, 4) & "|" & a(i, 5) & "|"
            If i = 1 Then .Item(a(1, 1)) = ""
        Else
            .Item(a(1, 1)) = .Item(a(1, 1)) & a(1, 2) & "|" & a(1, 3) & "|" & a(1, 4) & "|" & a(1, 5) & "|"
            .Item(a(i, 1)) = .Item(a(i, 1)) & a(i, 2) & "|" & a(i, 3) & "|" & a(i, 4) & "|" & a(i, 5) & "|"
        End If
    Next
Cells(12, 1).Resize(.Count, 2) = Application.Index(Application.Transpose(Array(.keys, .items)), 0, 0)
Cells(12, 2).Resize(.Count).TextToColumns Cells(12, 2), 1, , , , , , , True, "|"
End With
End Sub

 

[JEC]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cType = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Faculty", type text}, {"Work completed", type text}, {"Hours", type text}, {"Cost Centre", type text}}),
    unPiv = Table.UnpivotOtherColumns(cType, {"Name"}, "Attribute", "Value"),
    cols = Table.FromList(List.Zip({unPiv[Attribute],Table.AddIndexColumn(unPiv, "Index", 1, 1, Int64.Type)[Index]}), Splitter.SplitByNothing(),null,null),
    ext = Table.ToList(Table.TransformColumns(cols, {"Column1", each Text.Combine(List.Transform(_, Text.From)), type text})),
    grp = Table.Group(unPiv, {"Name"}, {{"Count", each Text.Combine([Value],"|")}}),
    xList =  List.Max(Table.AddColumn(grp, "Custom", each List.Count(Text.PositionOfAny([Count], {"|"}, Occurrence.All)))[Custom])+1,  
    Split = Table.SplitColumn(grp, "Count", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Range(ext,0,xList))
in
    Split

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Faculty	Work completed	Hours	Cost Centre
2	Sam	Medicine	Lecture	2	Nursing
3	Sam	Arts	Tutorial	3	Admin
4	Jack	Psychology	Lecture	1	Admin
5	Jack	Arts	Tutorial	4	General
6	Jack	Arts	Tutorial	3	General
7
8
9
10	Name	Faculty1	Work completed2	Hours3	Cost Centre4	Faculty5	Work completed6	Hours7	Cost Centre8	Faculty9	Work completed10	Hours11	Cost Centre12
11	Sam	Medicine	Lecture	2	Nursing	Arts	Tutorial	3	Admin
12	Jack	Psychology	Lecture	1	Admin	Arts	Tutorial	4	General	Arts	Tutorial	3	General
Sheet1

 

[Fluff]

Another formula option

=LET(u,UNIQUE(FILTER(A2:A8,A2:A8<>"")),m,MAX(COUNTIFS(A2:A8,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:E8,A2:A8=y,"")),,m*4,""))))),1))

 

[tigerzen]

Thanks so much everyone and I really appreciate your responses. In this case it's great to have different options because I'm trying to educate a young employee about how to solve data issues using Excel. The formula based solutions are very clever but I'm finding them testing as I'm more familiar with Excel 2019 but the new dynamic array formulae are terrific, even so, teaching them to someone with limited Excel skills will be challenging.

A couple of questions, can the formula based solutions be modified easily so that if information is added to the original data (eg employee Sam does more work and is added to the original list) the resultant arrays are automatically updated?

Mohadin, will your macro work if there are more fields than what I've provided in the sample data?

 

[mohadin]

Hi
And thank you

will your macro work if there are more fields than what I've provided in the sample data?

If you mean more row and employee the answer is yes
For more columns need some amendment.
let me know if you need any
Best regards

 

[Fluff]

A couple of questions, can the formula based solutions be modified easily so that if information is added to the original data (eg employee Sam does more work and is added to the original list) the resultant arrays are automatically updated?

The formula I provided already does that.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Faculty	Work completed	Hours	Cost Centre
2	Sam	Medicine	Lecture	2	Nursing
3	Sam	Arts	Tutorial	3	Admin
4	Jack	Psychology	Lecture	1	Admin
5	Jack	Arts	Tutorial	4	General
6	Jack	Arts	Tutorial	3	General
7	Jack	Arts	Tutorial	1	Admin
8
9
10
11	Sam	Medicine	Lecture	2	Nursing	Arts	Tutorial	3	Admin
12	Jack	Psychology	Lecture	1	Admin	Arts	Tutorial	4	General	Arts	Tutorial	3	General	Arts	Tutorial	1	Admin
Data

Cell Formulas
Range	Formula
A11:Q12	A11	=LET(u,UNIQUE(FILTER(A2:A8,A2:A8<>"")),m,MAX(COUNTIFS(A2:A8,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:E8,A2:A8=y,"")),,m*4,""))))),1))
Dynamic array formulas.

 

[tigerzen]

The formula I provided already does that.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Faculty	Work completed	Hours	Cost Centre
2	Sam	Medicine	Lecture	2	Nursing
3	Sam	Arts	Tutorial	3	Admin
4	Jack	Psychology	Lecture	1	Admin
5	Jack	Arts	Tutorial	4	General
6	Jack	Arts	Tutorial	3	General
7	Jack	Arts	Tutorial	1	Admin
8
9
10
11	Sam	Medicine	Lecture	2	Nursing	Arts	Tutorial	3	Admin
12	Jack	Psychology	Lecture	1	Admin	Arts	Tutorial	4	General	Arts	Tutorial	3	General	Arts	Tutorial	1	Admin
Data

Cell Formulas
Range	Formula
A11:Q12	A11	=LET(u,UNIQUE(FILTER(A2:A8,A2:A8<>"")),m,MAX(COUNTIFS(A2:A8,u)),DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,HSTACK(y,EXPAND(TOROW(FILTER(B2:E8,A2:A8=y,"")),,m*4,""))))),1))
Dynamic array formulas.

Hi Fluff,

It only adds data up to and including row 8, once more data is added, eg row 9 the output table is not updated.

 

[JGordon11]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cType = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Faculty", type text}, {"Work completed", type text}, {"Hours", type text}, {"Cost Centre", type text}}),
    unPiv = Table.UnpivotOtherColumns(cType, {"Name"}, "Attribute", "Value"),
    cols = Table.FromList(List.Zip({unPiv[Attribute],Table.AddIndexColumn(unPiv, "Index", 1, 1, Int64.Type)[Index]}), Splitter.SplitByNothing(),null,null),
    ext = Table.ToList(Table.TransformColumns(cols, {"Column1", each Text.Combine(List.Transform(_, Text.From)), type text})),
    grp = Table.Group(unPiv, {"Name"}, {{"Count", each Text.Combine([Value],"|")}}),
    xList =  List.Max(Table.AddColumn(grp, "Custom", each List.Count(Text.PositionOfAny([Count], {"|"}, Occurrence.All)))[Custom])+1, 
    Split = Table.SplitColumn(grp, "Count", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Range(ext,0,xList))
in
    Split

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Faculty	Work completed	Hours	Cost Centre
2	Sam	Medicine	Lecture	2	Nursing
3	Sam	Arts	Tutorial	3	Admin
4	Jack	Psychology	Lecture	1	Admin
5	Jack	Arts	Tutorial	4	General
6	Jack	Arts	Tutorial	3	General
7
8
9
10	Name	Faculty1	Work completed2	Hours3	Cost Centre4	Faculty5	Work completed6	Hours7	Cost Centre8	Faculty9	Work completed10	Hours11	Cost Centre12
11	Sam	Medicine	Lecture	2	Nursing	Arts	Tutorial	3	Admin
12	Jack	Psychology	Lecture	1	Admin	Arts	Tutorial	4	General	Arts	Tutorial	3	General
Sheet1

another finish to your first three steps:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    cType = Table.TransformColumnTypes(Source,List.Zip({tcn, List.Repeat({type text}, List.Count(tcn) )})),
    unPiv = Table.UnpivotOtherColumns(cType, {"Name"}, "Attribute", "Value"),
    Group = Table.Group(unPiv, {"Name"}, {{"All", each Table.CombineColumns(Table.TransformColumns(Table.AddIndexColumn(_, "Index",4),{"Index", (x)=> 
        Number.RoundDown(x/4)}), {"Attribute", "Index"}, (y)=> y{0} & "." & Text.From(y{1}), "ColNames")  }}),
    Expand = Table.ExpandTableColumn(Group, "All", {"Value", "ColNames"}),
    Pivot = Table.Pivot(Expand, List.Distinct(Expand[ColNames]), "ColNames", "Value")
in
    Pivot