Extract unique values from multiple columns based on a set criteria to create a running list

[Silverhorne]

Good day,
I have been working to simplify a log that is used by a lot of people I work with. I originally built it years ago with helper columns and need to get rid of them.
I have 2 items I need to solve but will post the second one in a different post.

Item 1:
I have a massive log, column B has unique items. Columns D-K have the initials of the person that worked on that part of the item.
What needs to happen is the report needs to list the items from the log that match what has been entered in "E1" of the report to "M" of the log.
Any help is greatly appreciated!

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
AA/BB
BB
CC/DD
ABC/E/DD
FF/GG/HH/II/JJ/KK/LL/MMM

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: center"]Current Helper column
[/TD]
[TD="align: center"]Unique Items
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Part 1
[/TD]
[TD="align: center"]Part 2
[/TD]
[TD="align: center"]Part 3
[/TD]
[TD="align: center"]Part 4
[/TD]
[TD="align: center"]Part 5
[/TD]
[TD="align: center"]Part 6
[/TD]
[TD="align: center"]Part 7
[/TD]
[TD="align: center"]Part 8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]REPORT
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Current Helper column
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Item 1
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]AA
[/TD]
[TD="align: center"]AA
[/TD]
[TD="align: center"]AA
[/TD]
[TD="align: center"]AA
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]Item 2
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Report 1
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Item 3
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]CC
[/TD]
[TD="align: center"]DD
[/TD]
[TD="align: center"]CC
[/TD]
[TD="align: center"]DD
[/TD]
[TD="align: center"]CC
[/TD]
[TD="align: center"]DD
[/TD]
[TD="align: center"]CC
[/TD]
[TD="align: center"]DD
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]Item 4
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]ABC
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]ABC
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]ABC
[/TD]
[TD="align: center"]DD
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]ABC
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Report 1
[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]Item 5
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]FF
[/TD]
[TD="align: center"]GG
[/TD]
[TD="align: center"]HH
[/TD]
[TD="align: center"]II
[/TD]
[TD="align: center"]JJ
[/TD]
[TD="align: center"]KK
[/TD]
[TD="align: center"]LL
[/TD]
[TD="align: center"]MMM
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Report 1
[/TD]
[TD="align: center"][/TD]

</tbody>

LOG​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10"]A2
[/TH]
[TD="align: left"]=IF($M2=Report!$E$1,1,"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]A3
[/TH]
[TD="align: left"]=IF($M3=Report!$E$1,MAX($A$2:$A2)+1,"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]A4
[/TH]
[TD="align: left"]=IF($M4=Report!$E$1,MAX($A$2:$A3)+1,"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]A5
[/TH]
[TD="align: left"]=IF($M5=Report!$E$1,MAX($A$2:$A4)+1,"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]A6
[/TH]
[TD="align: left"]=IF($M6=Report!$E$1,MAX($A$2:$A5)+1,"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]O2
[/TH]
[TD="align: left"]=SUBSTITUTE(TRIM(D2&" "&IF(COUNTIF($D2:E2,E2)=1,E2,"")&" "&IF(COUNTIF($D2:F2,F2)=1,F2,"")&" "&IF(COUNTIF($D2:G2,G2)=1,G2,"")&" "&IF(COUNTIF($D2:H2,H2)=1,H2,"")&" "&IF(COUNTIF($D2:I2,I2)=1,I2,"")&" "&IF(COUNTIF($D2:J2,J2)=1,J2,"")&" "&IF(COUNTIF($D2:K2,K2)=1,K2,""))," ","/")
[/TD]
[/TR]
[TR]
[TH="width: 10"]O3
[/TH]
[TD="align: left"]=SUBSTITUTE(TRIM(D3&" "&IF(COUNTIF($D3:E3,E3)=1,E3,"")&" "&IF(COUNTIF($D3:F3,F3)=1,F3,"")&" "&IF(COUNTIF($D3:G3,G3)=1,G3,"")&" "&IF(COUNTIF($D3:H3,H3)=1,H3,"")&" "&IF(COUNTIF($D3:I3,I3)=1,I3,"")&" "&IF(COUNTIF($D3:J3,J3)=1,J3,"")&" "&IF(COUNTIF($D3:K3,K3)=1,K3,""))," ","/")
[/TD]
[/TR]
[TR]
[TH="width: 10"]O4
[/TH]
[TD="align: left"]=SUBSTITUTE(TRIM(D4&" "&IF(COUNTIF($D4:E4,E4)=1,E4,"")&" "&IF(COUNTIF($D4:F4,F4)=1,F4,"")&" "&IF(COUNTIF($D4:G4,G4)=1,G4,"")&" "&IF(COUNTIF($D4:H4,H4)=1,H4,"")&" "&IF(COUNTIF($D4:I4,I4)=1,I4,"")&" "&IF(COUNTIF($D4:J4,J4)=1,J4,"")&" "&IF(COUNTIF($D4:K4,K4)=1,K4,""))," ","/")
[/TD]
[/TR]
[TR]
[TH="width: 10"]O5
[/TH]
[TD="align: left"]=SUBSTITUTE(TRIM(D5&" "&IF(COUNTIF($D5:E5,E5)=1,E5,"")&" "&IF(COUNTIF($D5:F5,F5)=1,F5,"")&" "&IF(COUNTIF($D5:G5,G5)=1,G5,"")&" "&IF(COUNTIF($D5:H5,H5)=1,H5,"")&" "&IF(COUNTIF($D5:I5,I5)=1,I5,"")&" "&IF(COUNTIF($D5:J5,J5)=1,J5,"")&" "&IF(COUNTIF($D5:K5,K5)=1,K5,""))," ","/")
[/TD]
[/TR]
[TR]
[TH="width: 10"]O6
[/TH]
[TD="align: left"]=SUBSTITUTE(TRIM(D6&" "&IF(COUNTIF($D6:E6,E6)=1,E6,"")&" "&IF(COUNTIF($D6:F6,F6)=1,F6,"")&" "&IF(COUNTIF($D6:G6,G6)=1,G6,"")&" "&IF(COUNTIF($D6:H6,H6)=1,H6,"")&" "&IF(COUNTIF($D6:I6,I6)=1,I6,"")&" "&IF(COUNTIF($D6:J6,J6)=1,J6,"")&" "&IF(COUNTIF($D6:K6,K6)=1,K6,""))," ","/")
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C	D	E

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Item
[/TD]
[TD="align: center"]Parts
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Report 1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Item 2
[/TD]
[TD="align: center"]BB
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Item 4
[/TD]
[TD="align: center"]ABC/E/DD
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Item 5
[/TD]
[TD="align: center"]FF/GG/HH/II/JJ/KK/LL/MMM
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>

Report​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10"]B2
[/TH]
[TD="align: left"]=IFERROR(INDEX(LOG!B$2:B$6,AGGREGATE(15,6,(ROW(LOG!$B$2:$B$6)-ROW(LOG!$B$2)+1)/(LOG!$M$2:$M$6=$E$1),ROWS($A$1:$A1))),"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]C2
[/TH]
[TD="align: left"]=IF(ISNA(INDEX(LOG!B2:O6,MATCH(B2,LOG!B2:B6,0),14)),"",INDEX(LOG!B2:O6,MATCH(B2,LOG!B2:B6,0),14))
[/TD]
[/TR]
[TR]
[TH="width: 10"]B3
[/TH]
[TD="align: left"]=IFERROR(INDEX(LOG!B$2:B$6,AGGREGATE(15,6,(ROW(LOG!$B$2:$B$6)-ROW(LOG!$B$2)+1)/(LOG!$M$2:$M$6=$E$1),ROWS($A$1:$A2))),"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]C3
[/TH]
[TD="align: left"]=IF(ISNA(INDEX(LOG!B3:O7,MATCH(B3,LOG!B3:B7,0),14)),"",INDEX(LOG!B3:O7,MATCH(B3,LOG!B3:B7,0),14))
[/TD]
[/TR]
[TR]
[TH="width: 10"]B4
[/TH]
[TD="align: left"]=IFERROR(INDEX(LOG!B$2:B$6,AGGREGATE(15,6,(ROW(LOG!$B$2:$B$6)-ROW(LOG!$B$2)+1)/(LOG!$M$2:$M$6=$E$1),ROWS($A$1:$A3))),"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]C4
[/TH]
[TD="align: left"]=IF(ISNA(INDEX(LOG!B4:O8,MATCH(B4,LOG!B4:B8,0),14)),"",INDEX(LOG!B4:O8,MATCH(B4,LOG!B4:B8,0),14))
[/TD]
[/TR]
[TR]
[TH="width: 10"]B5
[/TH]
[TD="align: left"]=IFERROR(INDEX(LOG!B$2:B$6,AGGREGATE(15,6,(ROW(LOG!$B$2:$B$6)-ROW(LOG!$B$2)+1)/(LOG!$M$2:$M$6=$E$1),ROWS($A$1:$A4))),"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]C5
[/TH]
[TD="align: left"]=IF(ISNA(INDEX(LOG!B5:O9,MATCH(B5,LOG!B5:B9,0),14)),"",INDEX(LOG!B5:O9,MATCH(B5,LOG!B5:B9,0),14))
[/TD]
[/TR]
[TR]
[TH="width: 10"]B6
[/TH]
[TD="align: left"]=IFERROR(INDEX(LOG!B$2:B$6,AGGREGATE(15,6,(ROW(LOG!$B$2:$B$6)-ROW(LOG!$B$2)+1)/(LOG!$M$2:$M$6=$E$1),ROWS($A$1:$A5))),"")
[/TD]
[/TR]
[TR]
[TH="width: 10"]C6
[/TH]
[TD="align: left"]=IF(ISNA(INDEX(LOG!B6:O10,MATCH(B6,LOG!B6:B10,0),14)),"",INDEX(LOG!B6:O10,MATCH(B6,LOG!B6:B10,0),14))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Unique Items[/td][td=bgcolor:#5B9BD5]Part 1[/td][td=bgcolor:#5B9BD5]Part 2[/td][td=bgcolor:#5B9BD5]Part 3[/td][td=bgcolor:#5B9BD5]Part 4[/td][td=bgcolor:#5B9BD5]Part 5[/td][td=bgcolor:#5B9BD5]Part 6[/td][td=bgcolor:#5B9BD5]Part 7[/td][td=bgcolor:#5B9BD5]Part 8[/td][td][/td][td=bgcolor:#70AD47]Unique Items[/td][td=bgcolor:#70AD47]Part[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 1[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]BB[/td][td][/td][td=bgcolor:#E2EFDA]Item 1[/td][td=bgcolor:#E2EFDA]AA/BB[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 2[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td][/td][td]Item 2[/td][td]BB[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 3[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td][/td][td=bgcolor:#E2EFDA]Item 3[/td][td=bgcolor:#E2EFDA]CC/DD[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 4[/td][td]ABC[/td][td]E[/td][td]ABC[/td][td]E[/td][td]ABC[/td][td]DD[/td][td]E[/td][td]ABC[/td][td][/td][td]Item 4[/td][td]ABC/E/DD[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 5[/td][td=bgcolor:#DDEBF7]FF[/td][td=bgcolor:#DDEBF7]GG[/td][td=bgcolor:#DDEBF7]HH[/td][td=bgcolor:#DDEBF7]II[/td][td=bgcolor:#DDEBF7]JJ[/td][td=bgcolor:#DDEBF7]KK[/td][td=bgcolor:#DDEBF7]LL[/td][td=bgcolor:#DDEBF7]MMM[/td][td][/td][td=bgcolor:#E2EFDA]Item 5[/td][td=bgcolor:#E2EFDA]FF/GG/HH/II/JJ/KK/LL/MMM[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Unique Items"}, "Attribute", "Value"),
    Group = Table.Group(Unpivot, {"Unique Items"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Part", each List.Distinct(Table.Column([Count],"Value"))),
    Extract = Table.TransformColumns(List, {"Part", each Text.Combine(List.Transform(_, Text.From), "/"), type text})
in
    Extract[/SIZE]

I don't understand your Report option

 

[sandy666]

or like this:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Unique Items[/td][td=bgcolor:#70AD47]Parts[/td][td=bgcolor:#70AD47]Report[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Item 2[/td][td=bgcolor:#E2EFDA]BB[/td][td=bgcolor:#E2EFDA]Report1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 4[/td][td]ABC/E/DD[/td][td]Report1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Item 5[/td][td=bgcolor:#E2EFDA]FF/GG/HH/II/JJ/KK/LL/MMM[/td][td=bgcolor:#E2EFDA]Report1[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Unique Items", "Report"}, "Attribute", "Value"),
    Filter = Table.SelectRows(Unpivot, each ([Report] = "Report1")),
    Group = Table.Group(Filter, {"Unique Items"}, {{"Count", each _, type table}}),
    ListParts = Table.AddColumn(Group, "Parts", each List.Distinct(Table.Column([Count],"Value"))),
    ExtractParts = Table.TransformColumns(ListParts, {"Parts", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
    ListReport = Table.AddColumn(ExtractParts, "Report", each List.Distinct(Table.Column([Count],"Report"))),
    ExtractReport = Table.TransformColumns(ListReport, {"Report", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    ExtractReport[/SIZE]

 

[Silverhorne]

I don't want to use VBA, for the simple fact that I don't know how(I should learn).
The report is on a separate sheet from the log, I would enter "Report 1" into "E1" (or what ever report it may be) and it would look for this in column "M" of the log then return a unique list of initials separated by "/".
The first helper column is now redundant, as the index/aggregate formula in column "B" replaces the old lookup I used to have in there.

 

[sandy666]

This is not vba but PowerQuery M-language

You can put the result table anywhere and add/remove the Report filter, eg.

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Unique Items[/td][td=bgcolor:#5B9BD5]Part 1[/td][td=bgcolor:#5B9BD5]Part 2[/td][td=bgcolor:#5B9BD5]Part 3[/td][td=bgcolor:#5B9BD5]Part 4[/td][td=bgcolor:#5B9BD5]Part 5[/td][td=bgcolor:#5B9BD5]Part 6[/td][td=bgcolor:#5B9BD5]Part 7[/td][td=bgcolor:#5B9BD5]Part 8[/td][td=bgcolor:#5B9BD5]Report[/td][td][/td][td=bgcolor:#70AD47]Unique Items[/td][td=bgcolor:#70AD47]Parts[/td][td=bgcolor:#70AD47]Report[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 1[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]AA[/td][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]BB[/td][td=bgcolor:#DDEBF7]Report2[/td][td][/td][td=bgcolor:#E2EFDA]Item 1[/td][td=bgcolor:#E2EFDA]AA/BB[/td][td=bgcolor:#E2EFDA]Report2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 2[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]BB[/td][td]Report1[/td][td][/td][td]Item 2[/td][td]BB[/td][td]Report1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 3[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td=bgcolor:#DDEBF7]CC[/td][td=bgcolor:#DDEBF7]DD[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]Item 4[/td][td=bgcolor:#E2EFDA]ABC/E/DD[/td][td=bgcolor:#E2EFDA]Report1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 4[/td][td]ABC[/td][td]E[/td][td]ABC[/td][td]E[/td][td]ABC[/td][td]DD[/td][td]E[/td][td]ABC[/td][td]Report1[/td][td][/td][td]Item 5[/td][td]FF/GG/HH/II/JJ/KK/LL/MMM[/td][td]Report1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 5[/td][td=bgcolor:#DDEBF7]FF[/td][td=bgcolor:#DDEBF7]GG[/td][td=bgcolor:#DDEBF7]HH[/td][td=bgcolor:#DDEBF7]II[/td][td=bgcolor:#DDEBF7]JJ[/td][td=bgcolor:#DDEBF7]KK[/td][td=bgcolor:#DDEBF7]LL[/td][td=bgcolor:#DDEBF7]MMM[/td][td=bgcolor:#DDEBF7]Report1[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

edit:
but it's ok if you prefer many formulas
Have a nice day

 

[Eric W]

Your B2 formula on the Report sheet is fine as is. For the C2 formula, you can use:

=IFERROR(TEXTJOIN("/",1,IF(INDEX(Log!$D$2:$K$6,MATCH(B2,Log!$B$2:$B$6,0),0)<>"",IF(MATCH(INDEX(Log!$D$2:$K$6,MATCH(B2,Log!$B$2:$B$6,0),0),INDEX(Log!$D$2:$K$6,MATCH(B2,Log!$B$2:$B$6,0),0),0)=COLUMN(Log!$D$2:$K$2)-COLUMN(Log!$D$2)+1,INDEX(Log!$D$2:$K$6,MATCH(B2,Log!$B$2:$B$6,0),0),""),"")),"")

and confirm it by pressing Control+Shift+Enter. No formulas required on the LOG sheet at all. Note that this requires the TEXTJOIN function available in Excel 365. Also note that sometime within the next year or so, Microsoft will release some new functions, including UNIQUE which would make it much simpler. These would also be available in Excel 365.

 

[Silverhorne]

Thank you so much Eric W, that worked perfectly! I am using Excel 365 and have a few other places I've used the textjoin function already.
I do use excel every day but am seeing my knowledge is very basic outside of combing functions in the formula bar.

 

[Eric W]

Glad to help!

If you want to try some VBA, follow these instructions:

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM. On the window that opens, paste this code:

Public Function GetInits(ItemName As String, Log As Range)
Dim MyDict As Object, MyVals As Variant, i As Long, j As Long

    Set MyDict = CreateObject("Scripting.Dictionary")
    MyVals = Log.Value
    For i = 1 To UBound(MyVals)
        If MyVals(i, 1) = ItemName Then
            For j = 3 To UBound(MyVals, 2)
                MyDict(MyVals(i, j)) = 1
            Next j
            Exit For
        End If
    Next i
    GetInits = Join(MyDict.keys, "/")
    
End Function

Press Alt-Q to close the editor. Now in place of that long C2 formula, use:

=getinits(B2,Log!$B$2:$K$6)

Either way you go, I'm glad it works for you.