Trying to filter sheet so it only shows the column codes against the Product Codes

Hi and welcome. Could you attach a sample workbook with a before and after view of your data?
As you can see from your post above, the formatting somewhat lost and it's not clear what the requirement is.

BSB

Thank you. Please find attached excel document

Cell C10 formula , Drag down and across

Formula:

=IFERROR(INDEX($2:$2,AGGREGATE(15,6,COLUMN($D$2:$N$2)/(($A$3:$A$5=$A10)*($D$3:$N$5=1)),COLUMN(A$1))),"")

Thank you very much, it's much appreciated

What if the raw data was within another sheet? for example sheet2?

And also what is significant about COLUMN 15,6?? What it's purpose?

Thanks

So I've tried your formula on my other data set and it hasn't worked. I have attached the spreadsheet again but if you go to sheet 2 you'll see my first data set and below it your worked solution you provided.

I have looked at the formula code time and time again and they look exactly the same but it shows a 1 instead of letter a code. I'm assuming I 'm missing something pretty obvious but at this point I cannot see it.

Thank you again

AJ

Thank you Nick. Yep, as suspected I made an obvious mistake. Spotted it

Thank you all.

I am curious though if someone in simple terms can explain what the column 15,6 does?

Thanks

AJ

Try changing the $2:$2 to $1:$1 as your header row is in row 1 now instead of row 2 in your original data.

IF you happen to have Excel 365 and your profile is out of date, then you could use this in C10 of your original sheet, copied down:

=LET(x,FILTER($D$3:$N$5,$A$3:$A$5=A10),FILTER($D$2:$N$2,x=1))

The 15 and 6 in the formula are nothing to do with COLUMN but are arguments of the AGGREGATE function.
I think 15 = Small and 6 = Ignore Errors. Don't quote me on that though.

Give it a try, in a blank cell type =AGGREGATE( then the intelisense will kick in and you can find those numbers in the list and it will show you what they're for.

BSB

paste code into module ,
run RemapProdCodes

Option Explicit
Public gcolPC As Collection
Const kRESULT = "results"

Sub RemapProdCodes()
Dim vPC
Dim iMax As Integer, i As Integer
Dim sFrm As String

Set gcolPC = New Collection

Range("c1").Select
While ActiveCell.Value <> ""
  vPC = ActiveCell.Value
  gcolPC.Add vPC
    
  ActiveCell.Offset(0, 1).Select 'next prod code
Wend
iMax = gcolPC.Count

Range("A2").Select

On Error Resume Next
Sheets(kRESULT).Delete

ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.Name = kRESULT

Range("A2").Select
While ActiveCell.Value <> ""
   'scan all columns
  For i = 1 To iMax
     If ActiveCell.Offset(0, i + 1).Value = 1 Then
        ActiveCell.Offset(0, i + 1).Value = gcolPC(i)
     End If
  Next
  
     'NO LONGER NUMERIC, SWITCH TO COUNTA
  sFrm = ActiveCell.Offset(0, 1).Formula
  sFrm = Replace(sFrm, "SUM", "CountA")
  ActiveCell.Offset(0, 1) = sFrm
  
  ActiveCell.Offset(1, 0).Select 'next prod code row
Wend

MsgBox "Done"
Set gcolPC = Nothing
End Sub