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:

Please Login or Register  to view this content.

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

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))

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

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

Please Login or Register  to view this content.