Page 1 of 1
find same values(duplicate) or vlookup formula
Posted: 16 Aug 2010 19:02
by santhosh
hi,
i have two text files, i want to find common values in that(duplicate) like vlookup formula which we use in excel sheet.
example:-
sample1.txt contains
33
445
667
22
45
97
67
57
86
8585
789
sample2.txt
565
46534
656
87
35
33
86
output in sample3.txt:-
33
86
Re: find same values(duplicate) or vlookup formula
Posted: 17 Aug 2010 04:49
by aGerman
Try this
Code: Select all
>"sample3.txt" type nul
for /f "usebackq delims=" %%A in ("sample1.txt") do (
for /f "usebackq delims=" %%a in ("sample2.txt") do (
if "%%A"=="%%a" >>"sample3.txt" echo\%%A
)
)
Regards
aGerman
Re: find same values(duplicate) or vlookup formula
Posted: 18 Aug 2010 08:34
by ghostmachine4
download
gawk for windows, then use this one liner
Code: Select all
c:\test> gawk.exe "FNR==NR{a[$1];next}($1 in a)" file1 file2
33
86
this has the advantage of using memory which is faster than double for loops from aGerman's solution.
Re: find same values(duplicate) or vlookup formula
Posted: 20 Aug 2010 00:17
by santhosh
hi friends
thanks for both of u.it works good.
In my office pc i dont have authority to install any package,already i told this to agerman that dont use any vbsctipt r gawk.
Anyway ghostmachine, thanks for your idea i will use in my home pc.
Re: find same values(duplicate) or vlookup formula
Posted: 20 Aug 2010 01:41
by ghostmachine4
santhosh wrote:hi friends
thanks for both of u.it works good.
In my office pc i dont have authority to install any package,already i told this to agerman that dont use any vbsctipt r gawk.
you can download from your home, its just an exe file. Put it inside your portable disk or USB drive, then bring to workplace to use.
Re: find same values(duplicate) or vlookup formula
Posted: 08 Sep 2010 01:10
by santhosh
hi ghostmachine,
c:\test> gawk.exe "FNR==NR{a[$1];next}($1 in a)" file1 file2
now i want to print values which is not same.
Re: find same values(duplicate) or vlookup formula
Posted: 08 Sep 2010 01:28
by ghostmachine4
santhosh wrote:hi ghostmachine,
c:\test> gawk.exe "FNR==NR{a[$1];next}($1 in a)" file1 file2
now i want to print values which is not same.
use a "!" to invert the condition
Code: Select all
gawk.exe "FNR==NR{a[$1];next}( !( $1 in a) )" file1 file2
Re: find same values(duplicate) or vlookup formula
Posted: 08 Sep 2010 06:24
by santhosh
Thanks ghostmachine.one more doubt.
want to compare only 2nd and 3rd column (both column should be equal)example:-
File1 contains:-
sat,005,87
sat1,09,95
sat3,40,98
sat4,50,99
sat5,40,97
File2 contains:-
ggg,010,55
hth,68,95
sat,42,97
sat6,40,98
output should be File3:-
sat,40,98
Thanks in advance
Re: find same values(duplicate) or vlookup formula
Posted: 08 Sep 2010 06:37
by ghostmachine4
and how is that sat and not sat3 and sat6 ?
Re: find same values(duplicate) or vlookup formula
Posted: 07 Oct 2010 04:07
by santhosh
yes sorry ghost,
i missed that line
output is :-
sat3,40,98
sat6,40,98