 SOLVED

# Excel Challenge

Highlighted
Occasional Contributor

# Excel Challenge

I have big problem in writing
Excel formula for the following problem:

I have Three feature of my costumer I want to separate my costumers by these
three features and give them Index 1, 2 and 3,

For Costumers which they have A feature give 1 index

For costumers which they have B feature give 2 Index

and for the costumers which they have A and B (both of them) give the 3 index

also I send attachments,

How I can do this in Excel ? 8 Replies
Highlighted

# Re: Excel Challenge

Are you able to reorganize the data to a cleaner table, as in the attached? If so, a simple IFS formula gives the resulting "Index" value.   In general, if it's possible for all your data, you're better off having any one customer occupy only one row. Especially where the only differences have to do with one or two elements of information, because they can be handled by different columns on the same, single row.

If there's some other compelling reason to have multiple rows per person, please spell that out. You might then be better served by having two or more tables, connecting them via Power Query or some other feature.

I've attached the spreadsheet, but here's an image for your review. The formula is visible as well. That formula is just copied down to the other rows. Highlighted
Best Response confirmed by Jalal_1988 (Occasional Contributor)
Solution

# Re: Excel Challenge

``=MMULT(--(COUNTIFS(\$B\$2:\$B\$17,B2,\$A\$2:\$A\$17,{"A","B"})>0),{1;2})``
Highlighted

# Re: Excel Challenge

MMULT is a function I'd not seen before.

Frankly, the Help text on the function isn't very helpful in understanding it. Can you explain how it's working here to solve the question asked by @Jalal_1988

Highlighted

# Re: Excel Challenge

I'm not good at explaining MMULT(). So I have to refer to the support page. The key text is: The result is an array with the same number of rows as array1 and the same number of columns as array2.

array1 has one row (and two columns) and array2 has one column (and two rows). So the output is an array with one row and one column.

Highlighted

# Re: Excel Challenge

@Detlef Lewin  that is a clever solution

@mathetes  mmulti() stands for matrix multiply which goes back to linear algebra days.

I started to try and explain it here but you would be much better served with a website designed to teach it like this

Highlighted

# Re: Excel Challenge

Thanks for that link. I'm printing the page now for careful study.....and then I'll keep my eyes open for opportunities to apply MMULTI. It's in the using that it'll really (finally) make sense.

Highlighted

# Re: Excel Challenge

One use can be to create an array of subtotals for use in an array formula or lookup formula where, for whatever reason, you don't/can't have a subtotal helper column. So, if you had numbers in A1:A10, and entered this in B1:B10, you would get a subtotal array (credit to Harlan Grove):

=MMULT(--(ROW(A1:A10)>=TRANSPOSE(ROW(A1:A10))),A1:A10)

I'm not an expert on it's use, but I've used it in situations where I needed an OR criteria in an array calculation, but it had to be condensed into a single dimension array (instead of leaving it 2D and multiplying it out-sometimes the other parts of the formula were also OR criteria with different dimensions and the normal array multiplication wouldn't work and MMULT was more compact than the usual array addition I would use for OR).

Here's an example file of using mmult on a 2D data table with multiple OR criteria in both dimensions.

Highlighted