Return the list of marked cells

[kissmydrone]

Hi!

Please help me with the following problem:

I have a range of cells that are marked by me, such as

A ; Football
A ; Football
A ; Basketball
A ; Tennis
B ; Swimming
B ; Swimming
B ; Running
A ; Climbing
A ; Tennis
B ; Baking
B ; Swimming

What i want is to have a list of A's and B's, such as:

Football
Football
Basketball
Tennis
Tennis
Climbing
Tennis

and a second list

Swimming
Swimming
Running
Baking
Swimming

Can't handle that, tried indexing and such things, but I'm not an excel pro so any help would be appreciated

[TMS]

Marked in what way? Return to where? Why not just use Autofilter. There's no real reason to put the data anywhere else.


Regards, TMS

[kissmydrone]

TMS,

The first columnd is the mark - A or B, the second column are the "activities".
They should be returned in a new sheet within the same file.
Basically, ive got lots of cells and i need them to be sorted and split automatically.

[kissmydrone]

example.xlsx

Ok basically I want the file to look like the one in the file

[TMS]

For 1: Array Formula:

=IFERROR(INDEX(B$2:B$100,SMALL(IF($A$2:$A$100=1,ROW(A$2:A$100)-ROW(A$2)+1),ROWS(A$2:A2))),"")

Committed with Ctrl-Shift Enter rather than just Enter (will show as {...}



Regards, TMS

[kissmydrone]

Brilliant, works great !

Thank You very much !

[Fotis1991]

My suggestion.Also Array formula.

=IFERROR(INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!$A$2:$A$100=1,ROW(Sheet1!$B$2:$B$100)-1),ROW(Sheet1!A1))),"")

[TMS]

You're welcome. Thanks for the rep.

[Fotis1991]

TMS,your formula gives me 0(that's why i post another suggestion).

Any idea why?

[TMS]

@Fotis: not really. It's a formula I found, copied and adapted. It worked for the 1 marker but not the 0 marker. The reason being that the 1 was numeric and the 0 was text (we how it is left aligned. I remedied that in my test by using"0" rather than just 0 in the IF statement.

I haven't tried your version yet; it may well be a superior option.

Regards, TMS