13952684201163267.ods I have data in libreoffice like
ColumnA ColumnB
504231 504109
504109 504201
504201
504299
like so much data Then I want output like
ColumnA ColumnB
504231
504109 504109
504201 504201
504299
How to achieve this output please let me know
I think with a third column and using a formula like:
=IFNA(VLOOKUP($A2;$B$2:$B$5;1;0);"")
you can get the desired result.
if your version doesn’t accept IFNA() change the formula to:
=IF(ISNA(VLOOKUP($A2;$B$2:$B$5;1;0));"";VLOOKUP($A2;$B$2:$B$5;1;0))
edited: 20140320
Your sample file modifying the search range for VLOOKUP()
Adapted file
It is not working fine . I will attache my file can u pls do that
Please see the attached file
You need to adapt the range for search in VLOOKUP(): =IF(ISNA(VLOOKUP($A2;$B$2: $B$16075 ;1;0));"";VLOOKUP($A2;$B$2: $B$16075 ;1;0)) change :$b$5 by :$b$16075
well this isnt’ a complete answer, but this will get you partially, there, cut all the data from columnb and append it to the end of column A so that all your data is in a single column.
then sort the data so same values will be in corresponding rows.
in column c1, put a “1”
in column C2 put =if (A2=A1;=c1+1;1)
(I might have the syntax wrong, but bascially you are keeping a running count of the number of times a number repeats.)
copy C2 to all the other column c cells for each value in in column A.
at his point, you now have a column that has the max count for each value in columnA as a max value in column C
now if you copy the VALUES of column A and C to D and F, and reverse sort Dand F on column D, the first occurance of any value in column d will have number of occurances of that value in its column c.
make a column G that is only the occurances of the first line:
hard code G1,
starting on G2 =if(g1=G2;"";F2) for all the remaining cells in column G