Compare column A in two sheets ... return associated data to third sheet

[brenakie]

Hi,

Kind of a xls noob. I search for an answer but didn't find what I was looking for.

Here's the scenario:
Compare 2 worksheets and pull common data from both and add to 3rd worksheet.

Data:
Multiple rows
Using columns A thru T
Primary column for query A (text alphanumeric)

Example:

Sheet 1

Col A
GC1
GC2
GC3
GC7
GC8
GC9

Sheet 2

Col A
GC2
GC4
GC7
GC10
GC11

Results

Col A
GC2
GC7

Thank in advance for your help.

Bren

[JBeaucaire]

You can actually get your results on sheet2 or sheet1. The sheet3 thing isn't really necessary.

1) On sheet2, if row1 is data and not titles, then insert an empty row at the top and put a title at the top of the data, so the real data starts in A2.

2) On sheet2 cell B2 put this formula:

=ISNUMBER(MATCH(A2, Sheet1!A:A, 0))

3) Copy that cell down column B all the way down the column A data.

4) Click on Data > Filter > AutoFilter

5) Use the B1 drop down to filter for TRUE

You're now looking at a list of the matching values only. Copy it to Sheet3 if you really think you need to.

[brenakie]

Hi Jerry,

Thanks for your reply. It's not really working for me, it's probably the interface between the chair and keyboard.

1) I'm using office 2010

2) Sheet 1: data range A1 - T1059
Sheet 2: A1 - T250

3) You stated "On sheet2 cell B2 put this formula"
? Since my data range is from Ax - Tx should I paste in U2?

4) Again you said to "Copy that cell down column B all the way down the column A data."
?Am I copying U2 and pasting to A3 down to A250

5) the "B2" dropdown in U2 does not show TRUE option I have some sort options and text filters

Again, I've used excel for years but not to this degree, and I'm sure what you're explaining to me is pretty basic.

Thanks again for your help I appreciate it,

Bren

[JBeaucaire]

Obviously you would need to use an empty column, so if A:T are populated then of course you could move the suggested formula out to column U, or wherever.