Ask Your Question
0

How to compare numbers in two columns [closed]

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

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
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 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
1

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

m.a.riosv gravatar image

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

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

edit flag offensive delete link more

Comments

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 +0200 )edit

Please see the attached file

Ram.BM gravatar imageRam.BM ( 2014-03-20 05:44:07 +0200 )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 +0200 )edit
0

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

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

Stats

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

Seen: 18,141 times

Last updated: Mar 20 '14