How do I compare data in 2 workbooks

[Mzansi]

I have 2 workbooks, In WK1 Data is listed as (First & Last name) in a single
cell. In Wk2 data is listed as (Last & First name) also in a single cell. Now
i need to compare or match entries in both workbooks. I tried VLOOKUP but its
not picking up bcoz the text strings have been transposed.

Pls can anyone help
Tnx

 

[tina]

Hi
You could try this formula to change wk2 data to first & last name presuming
there is a space between names eg
smith john
assuming data in column a

=concatenate(mid(a1,find(" ",a1)+1,len(a1))," ",mid(a1,1,find(" ",a1)-1))
then use vlookup
or in one formula on wk2 presuming data on wk1 on sheet1 column a
=if(isna(vlookup(concatenate(mid(a1,find(" ",a1)+1,len(a1)),"
",mid(a1,1,find(" ",a1)-1)),[wk1]sheet1!a:a,1,false)),"no match","match"))
Hope this helps
Tina

 

[gfish]

Just general note, sorry;

I looked through many requests like that and found some really good
solutions on how to compare data in Excel spreadsheets. But when I tried some
on very long lists my machine just hungs. I am talking about 5,000 - 50,000
rows in the column. I am wondering if there is any chance to create a VB/VBA
application that compares 10,000 rows in each worksheet?? any info would be
greatly appreciated.
--
just gfish

Hi
You could try this formula to change wk2 data to first & last name presuming
there is a space between names eg
smith john
assuming data in column a

=concatenate(mid(a1,find(" ",a1)+1,len(a1))," ",mid(a1,1,find(" ",a1)-1))
then use vlookup
or in one formula on wk2 presuming data on wk1 on sheet1 column a
=if(isna(vlookup(concatenate(mid(a1,find(" ",a1)+1,len(a1)),"
",mid(a1,1,find(" ",a1)-1)),[wk1]sheet1!a:a,1,false)),"no match","match"))
Hope this helps
Tina

I have 2 workbooks, In WK1 Data is listed as (First & Last name) in a single
cell. In Wk2 data is listed as (Last & First name) also in a single cell. Now
i need to compare or match entries in both workbooks. I tried VLOOKUP but its
not picking up bcoz the text strings have been transposed.

Pls can anyone help
Tnx

 

[Bruno Campanini]

Just general note, sorry;

I looked through many requests like that and found some really good
solutions on how to compare data in Excel spreadsheets. But when I tried
some
on very long lists my machine just hungs. I am talking about 5,000 -
50,000
rows in the column. I am wondering if there is any chance to create a
VB/VBA
application that compares 10,000 rows in each worksheet?? any info would
be
greatly appreciated.

What do you exactly mean when you say
"that compares 10,000 rows in each worksheet"?
Do you mean 10,000 rows in Sheet1 to be compared
with 10,000 rows in Sheet2 in order to chek what?
Cells with the same contents, values in Sheet1 wich
do not exist in Sheet2, or what?
The sheets are in number of two or how many?

Ciao
Bruno

 

[bangity]

yeah, i want to compare like that too! compare a row in sheet1 with a
row in sheet2

 

[gfish]

I was talking about really large size spreadsheets. Let's say I have 2
worksheets with 50 columns and 10,000+ rows and I would like to compare
columns "F" in each sheet if they are different. I tried some macros to
compare just Ids (i.e. one column) in 2 different sheets and it worked for
3,000+ rows but did not for 5,000+ rows. The rows were not sorted so I had to
sort them in the script before the comparing. It could take a lot of memory
but in general : are there any limitations on the number of rows being
processed?

p.s. I know how to do that comparison easily in MS Access but unfortunately
our users get outputs more often in the Excel spreadsheets out of Unix and
they are pretty lazy to import spreadsheets into Access.
Hope this clarifies the subject... Is there a way to automate this part??
(importing Excel spreadsheet into MS Access and run a query in VBA??)
--
just gfish

Just general note, sorry;

I looked through many requests like that and found some really good
solutions on how to compare data in Excel spreadsheets. But when I tried some
on very long lists my machine just hungs. I am talking about 5,000 - 50,000
rows in the column. I am wondering if there is any chance to create a VB/VBA
application that compares 10,000 rows in each worksheet?? any info would be
greatly appreciated.
--
just gfish

Hi
You could try this formula to change wk2 data to first & last name presuming
there is a space between names eg
smith john
assuming data in column a

=concatenate(mid(a1,find(" ",a1)+1,len(a1))," ",mid(a1,1,find(" ",a1)-1))
then use vlookup
or in one formula on wk2 presuming data on wk1 on sheet1 column a
=if(isna(vlookup(concatenate(mid(a1,find(" ",a1)+1,len(a1)),"
",mid(a1,1,find(" ",a1)-1)),[wk1]sheet1!a:a,1,false)),"no match","match"))
Hope this helps
Tina

I have 2 workbooks, In WK1 Data is listed as (First & Last name) in a single
cell. In Wk2 data is listed as (Last & First name) also in a single cell. Now
i need to compare or match entries in both workbooks. I tried VLOOKUP but its
not picking up bcoz the text strings have been transposed.

Pls can anyone help
Tnx