Find next different value in a column

[egemencoskun]

Hi all,

I have a list with different entries (text) in one column ( lets call it A:A in sheet 1) and another spread sheet (sheet 2) I want to start from the first value in A:A and get the next different value.

Example

Column A in sheet 1
a A1
a A2
a A3
a A4
b A5
b A6
b A7
c A8
c A9
c A10
d A11
d A12
e A13
e A14
.
.
.
.
and the result should be in sheet 2
first cell = a (=A1)
second cell(formuled) = b
third cell(formuled) = c
.
.
.
Simply the formula should skip the value which already in the previous cell in sheet 2 and find the next different value in sheet 1 in column A:A.

thanks in advance....

[NBVC]

You can use Advance Filter for that...

Make A1 of Sheet1 a Column Title... then go to Sheet2, cell A1 and go to DATA and select Advanced from the Sort & Filter section...

Then select Copy to another Location

in List Range, enter or select the original data from Sheet1, including header.

in Copy to, enter or select cell to copy to in current sheet.

select Unique records only.

Click Ok.

[6StringJazzer]

If I just look at what you have, I would suggest using Advanced AutoFilter to create a list of unique values.

Is that what you really need, or is your data more complex than it looks from this example? Is it in strictly ascending sorted order? Or could you re-encounter an "a" farther down and show that again on your other sheet?

[martindwilson]

if it may repeat say

a
a
a
b
b
b
c
c
c
d
d
e
e
a
a
a
c
c
and you want
a
b
c
d
a
c
in b1 put 1
in b2 put
=IF(A1=A2,"",1) drag down to end of data in col a
filter col b on 1's
copy visible cells paste to another location

[beaunydal]

You need an array formula to return unique values.
This one assumes that the list in column A of Sheet1 does not go down any further than row 1000 and is filled down to row 30 on Sheet2.
It uses the MATCH function and so is not case sensitive so if "a" and "A" are both in the Sheet1 list you will only see an "a" or an "A" in the list of unique values not both. The one you see in the list of unique values is the first one to appear in the Sheet1 list.
The array formula in Sheet2 A1 is...

Please Login or Register  to view this content.

It is a single-valued array formula that must be entered using the Ctrl+Shift+Enter key combination and has been filled down to row 30 in the attached doc.

Beau Nydal

[egemencoskun]

Thank you all of you guys

I am using if formula and copying but not really practical for the data of 3000 - 4000.

Advanced filter doesnt work as I am not after a unique values. I want the next different value from the value in previous cell and next different value could be repeating as the list can not be in alphabetical order due to its nature.

Beau the fomula seems working if the list is in order but if it is not it doesnt get the next different value.

I mean the table could be in this order

a
a
a
b
b
d
d
a
a
f
f
d
d
and the result I need
a
b
d
a
f
d

Basicly I am not trying to gather unique values in a column but the next different value from the previous cell.which could be repeating.

thanks again.

[NBVC]

Here is one way...

Make sure your data begins in at least row 2...

so assuming the data begins at A2, then in B2 enter formula:

=IF(A3=A2,"",MAX(B$1:B1)+1) and copy down...

In D1, enter: =MAX(B:B)

in D2, enter: =IF(ROWS($A$1:$A1)>$D$1,"",INDEX(A:A,MATCH(ROWS($A$1:$A1),B:B,0))) and copy down as far as you want...

[DonkeyOte]

FWIW instead of using a Null for repeating item you could just use prior value.

If you do this you can set the MATCH up so as to use Binary Search which would be quicker (esp. with large data sets).

So adapting B formula such that:

B2: =IF(A2="","",SUM(B1,A2<>A1))
copied down

D1 as before

D2 as before with exception of match_type parameter in the MATCH which changes from 0 to 1.

[Woodgnome]

Hi All - my first post here, I stumbled across this site & above solution whilst looking for a solution to a similar problem. - how could this be adapted please to list only the first occurances of the original question i.e.

a
a
a
b
b
d
d
a
a
f
f
d
d
would result in
a
b
d
f


thereby gathering the unique values ??

Many thanks in advance -

[6StringJazzer]

Welcome to the Forum Woodgnome!

As a new member you should read the rules. One of them says:

RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Woodgnome]

Apologies - jumped in feet first, will repost in a new thread - regards Paul

[John_Bates]

I imagine you have large columns of excel data and its a pain in the friggin whatsit to manually scroll through each block of text
Here is the solution.
1) Highlight the whole friggen column (that's a technical term)
2) Press F5
3) Click on Special
4) Click on Column differences
5) Click on OK

This will take you to the top cell of the first block in the column

6) Press F4 .. this will take you to the next block of text down
7) Press F4 again .. this will take you to the next block down
8) Press F4 again .. this will take you to the next block down
and so on .. I think you get the picture!

BUT .. what happens if you need to change any of the text and by this you 'unhighlight' the column?
No problem dudes and dudettes ...
Just highlight the column from one cell above the block you want to move onto next (i.e. highlight it to the bottom of the column)
Then repeat steps 1 to 8
Very simple
No friggen macros required (technical terms)
You love me don't you!
John

[FDibbins]

John, welcome to the forum

You may not have noticed, but this thread is over 3 years old, I doubt anyone is still following it

(and please cool it a bit with the "technical" terms)