Check if values in a range are the same.

[VytautasM]

Good day,

Looking for any help on the following problem.

Column A has variables. Column B marks the start and end of a range. Column C must display the variable, if variables in the range are all the same. If not "-".

Not sure if this explanation if clear enough. Any help, thread or tutorial to guide me to the right answer is appreciated.

Thank you.

KITEKAT	1.	KITEKAT
KITEKAT
KITEKAT
KITEKAT
SNICKERS	2.	SNICKERS
SNICKERS
SNICKERS
SNICKERS
MARS	3.	MARS
MARS
MARS
SPRITE	4.	-
FANTA
FANTA
COCA COLA	5.	COCA COLA
PEPSI	6.	PEPSI
PEPSI
PEPSI
PEPSI
PEPSI
PEPSI
PEPSI
PEPSI
PEPSI

 

[Eric W]

Try:

Book1
	A	B	C
1
2	KITEKAT	1	KITEKAT
3	KITEKAT
4	KITEKAT
5	KITEKAT
6	SNICKERS	2	SNICKERS
7	SNICKERS
8	SNICKERS
9	SNICKERS
10	MARS	3	MARS
11	MARS
12	MARS
13	SPRITE	4	-
14	FANTA
15	FANTA
16	COCA COLA	5	COCA COLA
17	PEPSI	6	PEPSI
18	PEPSI
19	PEPSI
20	PEPSI
21	PEPSI
22	PEPSI
23	PEPSI
24	PEPSI
25	PEPSI
26		7
Sheet9

Cell Formulas
Range	Formula
C2:C25	C2	=IF(B2="","",IF(COUNTIF(A2:INDEX(A:A,AGGREGATE(15,6,ROW(B3:B100)/(B3:B100<>""),1)-1),"<>"&A2)>0,"-",A2))

Note that I added the 7 in B26 to mark the end of the data. This is to keep the formula simple. I can change the formula if you can't put a value there. This also assumes that your ranges are 100 rows or less. If that's not the case, just change the 100 in the formula (2 places) to some higher value.

 

[VytautasM]

Eric,

Thank you very much. Also a off topic question. My excel knowledge is decent, but when I need something more complex like the formula you wrote I get stuck.

Do you have a any tips, book recommendations, articles that could help me crack the logic, structure of something more difficult ?

 

[Eric W]

I'm glad the formula works for you!

As far as your followup question, I'm not sure what to tell you. I'm largely self-taught. When I first started frequenting this forum, I thought I knew a lot, only to learn that I really didn't! I ended up reading a lot of the answered questions, and deconstructing those formulas to see how they worked. In time, I figured out some basic tricks, and then learned how to combine different tricks in a new formula. I don't know how to formalize that process so you can follow it. Try Googling "excel formulas cheat sheet pdf". You'll get several links where people have compiled different types of formulas. Even if you don't experiment with all of them, at least read over them to get the gist of it. Later, when you need something, it'll tickle your memory and you can go back and look up the details.

Good luck!

 

[Gilmoured]

Is it possible to adjust the formula so that blanks in column A are ignored please? The result in col C should be the same but there could be a blank in row 3 for example?