SOLVED

Data in the correct order

Copper Contributor

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

@Jan_Z10myusername 

 

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

 

Regards

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
best response confirmed by Jan_Z10myusername (Copper Contributor)
Solution

@Jan_Z10myusername 

 

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

 

Regards

@Jos_Woolley 

 

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

@Jan_Z10myusername 

 

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

@Jos_Woolley 

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

@Jan_Z10myusername 

 

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

@Jos_Woolley 

 

Thank you for your help.

After copy / paste there was an error message

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

I will try that.

 

Grt.

 

Jan

@Jan_Z10myusername 

 

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:

 

https://en.excel-translator.de/translator/ 

 

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

@Jos_Woolley 

 

Dear Jos,

 

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

 

Thank you very much,

 

Grt.

 

Jan

@Jan_Z10myusername 

 

Hi Jan,

 

Glad to hear it.

 

Cheers,

 

Jos

1 best response

Accepted Solutions
best response confirmed by Jan_Z10myusername (Copper Contributor)
Solution

@Jan_Z10myusername 

 

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

 

Regards

View solution in original post