Index Match / cleaning data

[ifthenwhat]

Annotation 2020-07-22 023405.png

Hi guys,

I am working on a large workbook, with three columns;

Column 1: unique ID (person)

Column 2: several categories that can be matched with column 1 and would be useful to transpose into individual columns

Column 3: data corresponding to entries in column 1 and 2

In the attached workbook (and screenshot) the simplified problem is summarized in TABLE 1, and I need help on what formulas I can use to transform TABLE 1 into TABLE 2.

I have spent some time struggling with different variations of Index Match and IFS, but can't quite make it work in my workbook of more that 70k rows.

Hopefully someone out there can help me - I am sure there is a simple solution to this problem!

Thanks

[bebo021999]

Try in F5:

Please Login or Register  to view this content.

Fill down and accross

[avk]

"F5"

Please Login or Register  to view this content.

"G5"

Please Login or Register  to view this content.

One time by pressing shift+ctrl+enter
copy paste across.

[ifthenwhat]

Thank you bebo021999 and avk for your replies!

I ended up with the solution suggested by avk, and have now solved the problem I needed help with.

Saved me a ton of time and frustration!

Cheers