Copying rows to new worksheet when col A in worksheet 1 = vaule in col A in worksheet 2

**Murtys Right Foot** · 10-16-2012, 06:01 AM

Hi there,

I'm new to all this, and would appreciate some help please.

I have a worksheet with 3000+ rows, and a second with 7000+ rows. I would like to create a third that contains any rows from worksheet 2 (7000+ Rows) that occur in worksheet 1 (3000+ Rows) when the content in column A is the same in both worksheets. If the row in worksheet 2 cannot be found worksheet 1, then a blank row would be acceptable.

I suspect I may have to add another column(s) to provide uniqueness.Copy rows.xlsx

How would I do this?

Example attached.

**Richard Buttrey** · 10-16-2012, 06:43 AM

Hi,

Yes, just add a helper column on sheet 2. In J2 enter

Formula:

Please Login or Register  to view this content.

and copy it down.

Now filter sheet 2 for values that are <> #N/A in column J. Either use autofilter and then copy and paste the filtered rows, or use data filter advanced to extract the rows to another sheet. But in which case you'd need to add column labels and a criteria range. The autofilter in this case is simpler.

**Murtys Right Foot** · 10-16-2012, 06:54 AM

Hi Richard,

Thanks for that - ironically I'd just tried that very approach and it works to a point. Trouble is that the value "Oracle" (for example) occurs more than once and in each case relates to a different heading. I believe I need some way of adding another column so that the combination of "Oracle" + "heading" gives me the string I'm trying to match.

Cheers,
Murts

**Richard Buttrey** · 10-16-2012, 09:02 AM

Hi,

Yes it seems in that case that you will need to create a new helper column.

I can't see any values that indicate which rows contain the headings so the best you can do is to use the cell colours and use a UDF to identify where the row contains the purple colour. Once you know this you can use it in an IF test. So one solution is the UDF and two helper columns.

The UDF is simply:

Please Login or Register  to view this content.

then in J4 enter

Formula:

Please Login or Register  to view this content.

and in K4

Formula:

Please Login or Register  to view this content.

copy J4:K4 down and use column K as the new identifying column.
and copy

Copying rows to new worksheet when col A in worksheet 1 = vaule in col A in worksheet 2

LinkBack

Thread Tools

Rate This Thread

Display

Copying rows to new worksheet when col A in worksheet 1 = vaule in col A in worksheet 2

Re: Copying rows to new worksheet when col A in worksheet 1 = vaule in col A in worksheet

Re: Copying rows to new worksheet when col A in worksheet 1 = vaule in col A in worksheet

Re: Copying rows to new worksheet when col A in worksheet 1 = vaule in col A in worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions