# How to compare numbers in two columns [closed] This post is a wiki. Anyone with karma >75 is welcome to improve it.

C:\fakepath\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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-20 08:34:01.798064

Sort by » oldest newest most voted 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. SampleFile.ods 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 more ## Comments 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

more