0

I need some help to sort out an old, bulky, crappy file.

So here is my problem.

I have an excel file like this:

enter image description here

and i need to sort it like this:

enter image description here

Keep in mind that the food type changes for each user.

Thanks,

1
  • 1
    Are the cells in the User column merged ?? Jul 12, 2017 at 13:09

1 Answer 1

0

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.

Screenshot

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.

5
  • hi, thanks for this. but i cannot get the same result when i try. where do you put this formula? which cell? thanks in advance.
    – Mozie1
    Jul 13, 2017 at 17:16
  • Can you provide screenshot or elaborate what went wrong? The first formula goes in D column, but you need to remove the header of the source table for this to work. The second formula goes in the content area of the content table (the conversion table)
    – Vylix
    Jul 13, 2017 at 19:49
  • did exactly what u said and the first part is good. the second part returns an error. can you please tell me in which cell to put the second formula. Thanks.
    – Mozie1
    Jul 14, 2017 at 13:36
  • @Mozie1 H3:J4 expand as needed
    – Vylix
    Jul 14, 2017 at 13:41
  • Hello, check this: imgur.com/5YUecUT
    – Mozie1
    Jul 15, 2017 at 5:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .