Help needed: Lookup & output of multiple values related to ID incolumn

[VanF00]

Hi folks!

I would really appreciate your help for solving the following excel
problem, as it is quite urgent:

I have a table like this:
A B
C D E
|CustomerID |Product |UsesProduct X |
UsesProduct Y |UsesProductZ |
|1 |X | | | |
|1 |Y | | | |
|2 |Z | | | |
|3 |X | | | |
|3 |Y | | | |
|3 |Z | | | |

If a customer uses multiple products, there are multiple rows with the
respective product.

I would need the aggregated information about the used products per
Customer in one Row, so that the sheet looks like that:
A B
C D E
|CustomerID |Product |UsesProduct X |
UsesProduct Y |UsesProductZ |
|1 |X |1 |1 |0 |
|1 |Y |1 |1 |0 |
|2 |Z |0 |0 |1 |
|3 |X |1 |1 |1 |
|3 |Y |1 |1 |1 |
|3 |Z |1 |1 |1 |

Does anybody know a solution for this (preferable no VBA)?

Thank you so much in advance!!!!!!

kr,
Stefan

 

[Markus Fischer]

You can do this by adding a new column which will aggregate the customerId
and the product name:
A | B | C | D | E | F
1 | X | 1X ---> ROw 2
1 | X | 1X
1 | Y | 1Y
2 | Z | 2Z
3 | X | 3X
3 | Y | 3Y
3 | Z | 3Z


The formnula for cell C2 is "=A2&B2".

Then use a countif in cell D2, E2 and F2:

Paste "=COUNTIF($C$2:$C$8,"="&$A2&D$1)" in cell D2 and drag in column D then
to drag to colum E and F.

It should work. You can hide the column C if you do not want it to appear.

Best regards,
Markus