Is there a function in Excel to find the first cell with a different value than the previous one

[Uumas]

Here's a demonstration of what I want to achieve, but in a much larger scale: https://imgur.com/a/8FZvA
So in the A column there's numbers which can be anything really and I need to list them in C column. I can easily do it manually on an example that small, but I have a sheet with ~30 different numbers on 500 rows. So is there a function I could use to do that?

 

[palaeontology]

in C2 enter the following ....

=IFERROR(INDEX($A$2:$A$37, MATCH(0,COUNTIF($C$1:C1, $A$2:$A$37), 0)),"")

Change $A$37 to whatever you want to be the last cell in the range.

Enter it as an array formula ... ie: press ctrl shift enter, not just enter

drag down as far as you need, and you will have your list

Kind regards,

Chris

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td]

4​

[/td][/tr]
[tr][td]

2​

[/td][td]

2​

[/td][td][/td][td]result[/td][/tr]
[tr][td]

3​

[/td][td]

3​

[/td][td][/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]

1​

[/td][td][/td][td]

3​

[/td][/tr]
[tr][td]

5​

[/td][td]

1​

[/td][td][/td][td]

1​

[/td][/tr]
[tr][td]

6​

[/td][td]

5​

[/td][td][/td][td]

5​

[/td][/tr]
[tr][td]

7​

[/td][td]

5​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[/table]

In C1 just enter:

=SUM(IF(FREQUENCY(A2:A9,A2:A9),1))

In C3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($C$3:C3)>$C$1,"",INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$9),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($C$3:C3))))

The foregoing keeps the order of occurrence in the data. If this is irrelevant, as an option in C3 control+shift+enter and copy down:

=IF($C$3:C3)>$C$1,"",MIN(IF(ISNA(MATCH($A$2:$A$9,$C$2:C2,0)),$A$2:$A$9)))

 

[Aladin Akyurek]

@palaeontology;4924342]in C2 enter the following ....

Leave e.g. A3 empty. Do you still get the list you are after?

 

[shift-del]

Chris,

besides formulas there are also Advanced Filter (copy with no duplicates) and Remove Duplicates.

https://support.office.com/en-us/ar...=en-US&rs=en-US&ad=US&ocmsassetID=HP010073943

 

[Peter_SSs]

Leave e.g. A3 empty. Do you still get the list you are after?

Aladin
I think the same question would apply to your second solution (after inserting the missing bit)

=IF(ROWS($C$3:C3)>$C$1,"",MIN(IF(ISNA(MATCH($A$2:$A$25,$C$2:C2,0)),$A$2:$A$25)))

 

[Aladin Akyurek]

Aladin
I think the same question would apply to your second solution (after inserting the missing bit)

=IF(ROWS($C$3:C3)>$C$1,"",MIN(IF(ISNA(MATCH($A$2:$A$25,$C$2:C2,0)),$A$2:$A$25)))

Simply add an ISNUMBER test.

=IF(ROWS($C$3:C3)>$C$1,"",MIN(IF(ISNUMBER($A$2:$A$25),IF(ISNA(MATCH($A$2:$A$25,$C$2:C2,0)),$A$2:$A$25))))

I was merely addressing the unique list formulas...

 

[Peter_SSs]

I was merely addressing the unique list formulas...

If the data is numeric and order is not important, here is another way, that doesn't require the C+s+e entry.

Book1
	A	B	C
1	Original		6
2	1		Result
3			1
4	1		2
5	1		4
6	1		6
7	1		8
8	1		9
9	2
10	2
11	2
12	2
13	4
14	4
15	8
16	8
17	9
18	6
19	6
20
List 2

Cell Formulas
Range	Formula
C1	=SUM(IF(FREQUENCY(A2:A24,A2:A24),1))
C3	=IF(ROWS(C$3:C3)>C$1,"",SMALL(A$2:A$20,COUNTIF(A$2:A$20,"<="&C2)+1))

 

[Ingolf]

Hi,

If the data is numeric and order is important, here is another way, that doesn't require the CSE entry.

C3 =IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$2:C2,$A$2:$A$9),0,0),0)),"")

 

[Aladin Akyurek]

Hi,

If the data is numeric and order is important, here is another way, that doesn't require the CSE entry.

C3 =IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$2:C2,$A$2:$A$9),0,0),0)),"")

In-between empty cells will still cause havoc.

By the way, it's still array-processing formula. Replacing control+shift+enter with an INDEX call is not generic, that is, it won't hold for all array-processing cases requiring control+shift+enter (CSE).

The obsession with CSE has apparently convinced Google Sheets: This spreadsheet software has an extra function called ARRAYFORMULA, so we get in all cases:

=ARRAYFORMULA(array-processing-formula)

followed by just enter.

Perhaps Excel could introduce something like:

#IF(ROWS($C$3:C3)>$C$1,"",INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$9),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($C$3:C3))))

or

=#IF(ROWS($C$3:C3)>$C$1,"",INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$9),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($C$3:C3))))

followed by just enter. Hopefully parsing for # is as cheap as parsing for control+shift+enter.