Ask Your Question

How to compare numbers in two columns [closed]

asked 2014-03-19 11:40:31 +0100

this post is marked as community wiki

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

like so much data Then I want output like

ColumnA ColumnB
504109  504109
504201  504201

How to achieve this output please let me know

edit retag flag offensive 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

2 Answers

Sort by » oldest newest most voted

answered 2014-03-19 23:35:49 +0100

m.a.riosv gravatar image

updated 2014-03-20 18:50:13 +0100

I think with a third column and using a formula like:


you can get the desired result.


if your version doesn't accept IFNA() change the formula to:


edited: 20140320 Your sample file modifying the search range for VLOOKUP() Adapted file

edit flag offensive delete link more


It is not working fine . I will attache my file can u pls do that

Ram.BM gravatar imageRam.BM ( 2014-03-20 05:41:39 +0100 )edit

Please see the attached file

Ram.BM gravatar imageRam.BM ( 2014-03-20 05:44:07 +0100 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2014-03-20 18:46:40 +0100 )edit

answered 2014-03-19 18:59:10 +0100

jleslie48 gravatar image

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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-03-19 11:40:31 +0100

Seen: 24,983 times

Last updated: Mar 20 '14