Finding the value that is different in a group

[ExcellentM]

I'm trying to make a process easier, but I'm not sure how to approach it and maybe someone here can at least lead me in the direction so i can work on it.

I have a list of products with similar names and their options on each row....like this

Product Name	Finish	Shade	Bulb type	Price	Subtract lowest priced from each similar item	Price Changing Feature
Braxton Lamp	Polished Nickel	Opal	Incandescent bulb	150	0
Braxton Lamp	Polished Nickel	Opal	LED bulb	250	100
Braxton Lamp	Bronze	Clear	Incandescent bulb	160	10
Braxton Lamp	Bronze	Opal	LED bulb	260	110
Vivian Lamp	Polished Nickel	Opal	Incandescent bulb	100	0
Vivian Lamp	Polished Nickel	Clear	Incandescent bulb	100	0
Vivian Lamp	Chrome	Opal	Fluorescent	120	20
Vivian Lamp	Chrome	Opal	LED bulb	200	100
Vivian Lamp	Bronze	Opal	LED bulb	220	120

Easy enough to see the item that causes the price difference in a small table, but i have thousands in a spreadsheet and I have to manually check each grouping.

How would I go about making Excel go through each group, analyze, and return the feature/features that change the base price in the last column?( example: LED bulb in last cell next to 100) I can use a formula easy enough to price separate, but not sure how to attempt a faster result.

Thank you

[daffodil11]

What are the correct answers that go down the right last column?

Are you trying to generate the names of the cheapest item of each set? Or are you trying to generate the name of each feature where the difference is not equal to zero?

[ExcellentM]

I've put the answers in the last column

Product Name	Finish	Shade	Bulb type	Price	Subtract lowest priced from each similar item	Price Changing Feature
Braxton Lamp	Polished Nickel	Opal	Incandescent bulb	150	0
Braxton Lamp	Polished Nickel	Opal	LED bulb	250	100	LED bulb
Braxton Lamp	Bronze	Clear	Incandescent bulb	160	10	Bronze
Braxton Lamp	Bronze	Opal	LED bulb	260	110	Bronze, LED
Vivian Lamp	Polished Nickel	Opal	Incandescent bulb	100	0
Vivian Lamp	Polished Nickel	Clear	Incandescent bulb	100	0
Vivian Lamp	Chrome	Opal	Fluorescent	120	20	Fluorescent
Vivian Lamp	Chrome	Opal	LED bulb	200	100	LED
Vivian Lamp	Bronze	Opal	LED bulb	220	120	Bronze,LED

[daffodil11]

Ok, so the logic is:

If for each A, if Price is not the minimum, then show name of each feature that is different than the minimum's.

Still plugging away at this..

=IF(MIN(IF(A2:A10=A2,E2:E10))=E2,"",????)

Working on the ???? part. Maybe using row() to pull the index of each.

[daffodil11]

Put this in H2:

Please Login or Register  to view this content.

This is an array formula that needs to be confirmed with Ctrl+Shift+Enter to exit the cell, instead of hitting enter as normal.

Then drag down.

(Sorry, I misplaced Mr. Occam's razor)

arrays are hard.xlsx

[ExcellentM]

Wow!....Impressive!

Pretty much does what I need.........The only thing I forgot is that it puts two features together (example "Bronze,LED") when I'm trying to get to the single feature that changes it. That's my fault for not specifying that i run the price difference again and again in successive columns until they all become "0". I still love this and I can tweak it a bit to get the final result.

Very nice indeed. You have been given an up rep by me and I thank you.

Can you explain this a little for me? I totally get the MIN(IF($A$2:$A$10=A2,$E$2:$E$10))=E2. that i do a lot. After that it's "Do nothing " ". Then.........

Can you generally explain how you came to this?

[daffodil11]

No problem, it was quite a journey and definitely put my skills to the test.

Rewritten for clarity:

Please Login or Register  to view this content.

If you have questions working through a formula, you can always highlight portions of it in the formula bar and hit F9 to see how it calculates. It's a great way to start to breakdown what the formula is doing.

For example, in the first portion highlight $A$2:$A$10=A2 and hit F9; it becomes {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

Highlight the second part of the IF: $E$2:$E$10 and hit F9: it becomes {150;250;160;260;100;100;120;200;220}

Lastly, highlight: IF({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},{150;250;160;260;100;100;120;200;220}) and hit F9

I use this to constantly reverse engineer MVP formulas.


I'll go through my logic step by step:

First, there's the trim. It's actually the last step; it removes the spaces before and after whatever it pulls from the next part so if the result is Bronze and nothing, it drops the nothing and the space in between. Mostly, it makes nothing and Bronze: " Bronze" become "Bronze" by dropping the leading space.

So, onto the real meat:

If the value of E isn't the lowest of the group, then I need to find out if B or D matches the lowest in the group's respective B or D.

What is the current rows value? It's B2. I have no idea why I had initially made it more complicated than it had to be. (insert Occam reference here)

What is the min row's value? Well, we already have a formula for finding the MIN: MIN(IF($A$2:$A$10=A2,$E$2:$E$10)), now we need to find it. I used MATCH to look for it, but used another nested IF so when A2:A10=A2, only consider those E values. This way if there are more than one of the MIN value in E, it only looks for what's revelant to the grouping of A.

MATCH returns a number representing what row of E2:E10 it found it on, in this case it's 1. INDEX returns the value of a cell x rows down, y columns over. My index is B2:B10, and we go down the # of rows equal to the match.

• What is min: 150
• What row of E2:E10 is 150 on when A2:A10=A2: 1st row
• For INDEX B2:B10 give me the value on the first row: Polished Nickel
• Is B2 different than Polished Nickel: No
• Then do nothing

If the answer had been yes, then it would have printed the value of B2

The formula then prints a space, and does the same thing again for the D column.

[ExcellentM]

You are swesome! It going to tale a few read throughs to completely understand the explanation, but it gets my mind working on arrays, which I really need to improve. Thanks again buddy!