SOLVED

# Data in the correct order

Highlighted
Occasional Contributor

# Data in the correct order

I would like to be able to see in what order for example the letter a2 a3 a 4 a 6 and a1 is used in a vertical column.

In this column are many other data without an "a .

I hope someone can help me.

11 Replies
Highlighted

# Re: Data in the correct order

Can you post a small example dataset together with expected results? Make clear which cells within the worksheet are being used.

Regards

Highlighted

# RE: Data in the correct order

Her is an example Data Result 3a6+ 3a6+ 4s1+ 3a4r 12vh4+ 311a35+ 6so1 3a4r 11b2+ 6vp3+ 311a35+ Every code with an a is selected an put in de correct order
Highlighted
Best Response confirmed by Jan_Z10myusername (Occasional Contributor)
Solution

# RE: Data in the correct order

Thanks, but I'm afraid it's not clear which cells in the worksheet those entries are in. Can you clarify?

Regards

Highlighted

# RE: Data in the correct order

Column A is the column the entrys are put in and I ope i can see in wich order all entrys with the letter a are used,

Grt.

Jan

Highlighted

# RE: Data in the correct order

I'm afraid it's still not clear.

Let's start again. Give me a before and after, clearly specifying what values you have in what cells.

For example:

Before (what you have now)

cell A1: x

cell A2: y

cell A3: z

...

After (what you want to see)

cell A1: z

cell A2: x

cell A3: y

...

Better still, post an actual workbook.

Regards

Highlighted

# RE: Data in the correct order

This is a list of data that I have made.

 2b2+ 11b2+ 6s6+ 6s6+ 7b4+ 6s1+ 11rp+ 107a4p0 7b4- 2b4- 6sp1+ 107a4r- 12sp6+ 102a31b- 2s60 10vp3+ 2s60 2s5+ 2s60 6vh2+ 107a4d0 2s5- 2u 12sp1- 2vp2+ 106a6+ 12vp4+ 1011a2d+ 11s1+ 11vh4+ 107a4p+ 7b40 11b40 6rp0 1011a3p0

Ik would like to know all the data with an “a”in it in proper order: Result:

107a4p0

107a4r-

102a31b-

102a31b-

107a4d0

 1011a2d+107a4p+1011a3p0

These are dat of a volleybal game. Nr 10 is the setter and I want to find or there is a pattern in the way the setter plays.

A 4 – A2 – A 3 are positions at the net.

Grt.

Jan

Highlighted

# RE: Data in the correct order

Thanks. Based on a range A1:A35:

=IF(ROWS(\$1:1)>COUNTIF(A\$1:A\$35,"*a*"),"",INDEX(A\$1:A\$35,AGGREGATE(15,6,(ROW(A\$1:A\$35)-MIN(ROW(A\$1:A\$35))+1)/SEARCH("a",A\$1:A\$35)^0,ROWS(\$1:1))))

and copied down.

Regards

Highlighted

# RE: Data in the correct order

After copy / paste there was an error message

Maybe I will have to translate "count" etc. into Dutch.

I will try that.

Grt.

Jan

Highlighted

# RE: Data in the correct order

Yes, of course, if you're not using an English language-version of Excel then you'll need to do that. I always use this online Excel translation tool:

which, in this case, gives:

=ALS(RIJEN(\$1:1)>AANTAL.ALS(A\$1:A\$35;"*a*");"";INDEX(A\$1:A\$35;AGGREGAAT(15;6;(RIJ(A\$1:A\$35)-MIN(RIJ(A\$1:A\$35))+1)/VIND.SPEC("a";A\$1:A\$35)^0;RIJEN(\$1:1))))

I should also have mentioned that this won't work for you if you're using Excel 2007 or earlier. I can provide you with an alternative formula if that's the case.

Regards

Highlighted

# RE: Data in the correct order

Dear Jos,

I have translated the code and now it is working correctley.

Thank you very much,

Grt.

Jan

Highlighted

Hi Jan,