I need some help to sort out an old, bulky, crappy file.
So here is my problem.
I have an excel file like this:
and i need to sort it like this:
Keep in mind that the food type changes for each user.
Thanks,
You'll need to make a helper column to translate the merged cells to single cells, or translate them manually - which I don't recommend for a lot of data.
Use this to automatically convert the merged cells: (taken from here)
=IF(A2="", D1, A2)
After that, it is fairly simple. Just use a multiple criteria INDEX MATCH
with this formula:
=INDEX($C$2:$C$6, MATCH(1, ($G3 = $D$2:$D$6) * (H$2 = $B$2:$B$6), 0))
If you don't like the #N/A
error thrown because no match is found, wrap the whole formula in IFNA
function:
=IFNA(INDEX($C$2:$C$6, MATCH(1, ($G3 = $D$2:$D$6) * (H$2 = $B$2:$B$6), 0)), 0)
Note: The last 0
convert #N/A
error to 0
instead.
User
column merged ??