Find value depending on two values from different sheet

[Masun]

Hi,

I got stuck. Hope for some assistance.

I have two sheets, "Data" and "Product-Custnr".

In the "Data" sheet is a list of customer number and their diffrent products related to the customer number. Each customer number may have more than one product related to it. For every product a customer number has there is a new row so customer number may be on many rows but the product will differ from row to row .

In the "Product-Custnr" sheet I have all the unique customer numbers in a list (vertical) and in the header I have all the products (horizontal). In "Product-Custnr" i need to make a list of every product that a customer nr has. If a customer number has the product that are placed in G1:AV1 i want that product number to appear.

I find it a bit hard to explain. Please see attached file. For example see row 4 in "Product-Custnr". For customer number in C4 (nr 65061), that customer number has in sheet "Data" two products F5 (OK370026) and F6 (GRFTG370). I want that to get in the column for that product in sheet "Product-Custnr", T1 is OK370026 and AO1 is GRFTG370 and I need them to be written on row 4 so on T4 there should be OK370026 and on AO4 there should be GRFTG370. This is marked green in the attached file so I think you understand what i am trying to do if you take a look at that. Really hope that somone can help me!

/Masun

[benishiryo]

hi Masun. try this in G2:
=IF(COUNTIFS(DATA!$A:$A,$C2,DATA!$F:$F,G$1),G$1,"")

copy down & across

[Masun]

benishiryo,

Many thanks! Works great!

/Masun