Compare 2 work sheets to extract matcing data

January 20, 2011 at 03:56:21
Specs: Windows XP, Pentium IV
I have two work sheets with 6 columns ( can be more ) of rows of data on both , data can be of any number of rows in either worksheet ie. 200 rows in Sheet 1 & 500 rows in Sheet 2 , I need to compare & extract exact matching rows of data from both worksheets and paste it on to a new worksheet.

I use old EXCEL 97 and EXCEL 2000 on different computers .

Any solution and if possible a Macro to work on both is highly appreciated .

Thanks

Vijay


See More: Compare 2 work sheets to extract matcing data

Report •

#1
January 22, 2011 at 19:57:03
re: "I have two work sheets with 6 columns ( can be more )...

I need to compare & extract exact matching rows of data"

In order to determine if a row is an "exact" match to another, does each row have to be checked cell by cell or there a single column that can be checked to see if they match?

In other words, let's say the first row I am trying to find a match for is this one:

     A   B    C    D    E   F
1   cat dog mouse car home bike


Might I have a row like this:

     A   B    C    D     E    F
1   cat dog wall phone truck ski

Meaning that I would have to check if A1 matched A10, then if it did, check if B1 matched B10 and if it did, check if C1 matched C10 and whoops, that's not a match, we can move on to the next row.

Do you see what I'm getting at?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 22, 2011 at 22:02:45
Thanks Derbydad03 for the reply,

Data needs to be checked one cell at a time from sheet1 compared

with each cell and each row in the second worksheet.

ie. to make it simple I am using

A / B / C / D / E / F itself as six columns of one row of data in
worksheet 1

Possible exact matches : A B C D E F
in worksheet 2
B A D C F E

B D A E F C

.
.

F E D C B A

ie. position of each cell data in a row does not matter as far
as all six cell data matches in a row.

P.N. 1. Number of columns can be 6 - 25 for each row of data.

2. Each Worksheet row data to be compared cell to cell with each
row data in 2nd worksheet and common data as above to be extracted
and pasted in to new worksheet ( duplicates row matching data
not needed if same data is found twice or more in same worksheet. )

3. Each cell data is not more than 4 character text ie. 1234 , a234,
ab56, z23 , 999, k2, abc9, abcd if that helps in any way for creating a
macro or any program.

P.N. I am not a coder / programmer, a novice at using excel for solving
problems.

Hope I am clear,

Thanks once again,

Vijay


Report •

#3
January 23, 2011 at 17:46:16
re:
Possible exact matches : A B C D E F
in worksheet 2
B A D C F E
B D A E F C

I don't know of anyone that would consider B A D C F E or B D A E F C to be exact matches of A B C D E F.

The only exact match of A B C D E F is A B C D E F.

What you appear to be asking for is to find ranges of cells that contain the same values, but not necessarily in the same order. That is very different (and considerably more difficult) than finding exact matches of each range.

Assuming that is correct, that are few things that are still unclear.

1 - If a given row of data in Sheet 1 contains e.g. 6 columns, then it only needs to be compared to rows of 6 columns in Sheet 2, correct? In other words:

If Sheet 1 contained A B C D E F then E F A C D B would fit your criteria of a "match" but not A B C D E F G because there are 7 columns in that set.

Is that correct?

2 - re: "duplicates row matching data not needed if same data is found twice or more in same worksheet."

Since your idea of "matching data" is different than mine, this is not clear.

If Sheet 1 contained A B C D E F then E F A C D B would fit your criteria of a "match" and should be "extracted".

However, E A F C D B is also a "match" but I don't know if you would consider that a "duplicate row of matching data".

Please clarify.

3 - re:"Each cell data is not more than 4 character text ie. 1234 , a234,
ab56, z23 , 999, k2, abc9, abcd if that helps in any way for creating a
macro or any program.
"

What would help is if you posted some actual examples of your data. Since what you are asking for is fairly complex, I don't want to waste time working on something that won't work simply because I don't know what your actual data looks like.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 23, 2011 at 19:43:38
Thanks for the reply,

3. "Each cell data is not more than 4 character text ie. 1234 , a234,
ab56, z23 , 999, k2, abc9, abcd if that helps in any way for creating a
macro or any program. "

What would help is if you posted some actual examples of your data. Since what you are asking for is fairly complex, I don't want to waste time working on something that won't work simply because I don't know what your actual data looks like.

Reply to 3 : Lets assume its 4 digits instead of text to make things easier

ie. 1234, 3782 , 4597, 0099 , this will make it a lot easier

to extract & match data.

1 - If a given row of data in Sheet 1 contains e.g. 6 columns, then it only needs to be compared to rows of 6 columns in Sheet 2, correct? In other words:

If Sheet 1 contained A B C D E F then E F A C D B would fit your criteria of a "match" but not A B C D E F G because there are 7 columns in that set.

Is that correct?

Reply to 1 : Yes you are correct, one row containing 6 columns in sheet 1 will

always be compared to only one row of data containing 6 columns in sheet 2.

similarly if for another set of data say contain 10 columns of row data in

sheet 1 will only be compared with 10 columns of row data in sheet 2.

2 : duplicates row matching data not needed if same data is found twice or more in

same worksheet." Since your idea of "matching data" is different than mine,

this is not clear.

If Sheet 1 contained A B C D E F then E F A C D B would fit your criteria of a

"match" and should be "extracted".

Reply to 2 : What can be done is once a match is found it can be extracted and

stored and further matching & extraction process stopped for that row , ie.

check for the new next row of data from sheet 1.

If duplicates show up in the final extracted result sheet ( because sheet 1

may also contain a duplicate ) it can be sorted and filtered.


If this can be done , it would be great as a starter in solving the problem.

Appreciate your time and effort ,

Thanks

Vijay


I hope I am clear,

P.N.







Report •

Ask Question