May 26 2020 06:47 AM
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.
May 26 2020 06:49 AM
Can you post a small example dataset together with expected results? Make clear which cells within the worksheet are being used.
Regards
May 26 2020 07:06 AM
May 26 2020 07:12 AM
Solution
Thanks, but I'm afraid it's not clear which cells in the worksheet those entries are in. Can you clarify?
Regards
May 26 2020 07:17 AM
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
May 26 2020 07:26 AM - edited May 26 2020 07:28 AM
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
May 26 2020 07:49 AM
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
May 26 2020 08:12 AM
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
May 26 2020 11:01 AM
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
May 26 2020 11:51 AM - edited May 26 2020 11:54 AM
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
May 27 2020 04:22 AM
Dear Jos,
I have translated the code and now it is working correctley.
Thank you very much,
Grt.
Jan
May 27 2020 04:29 AM
May 26 2020 07:12 AM
Solution
Thanks, but I'm afraid it's not clear which cells in the worksheet those entries are in. Can you clarify?
Regards