• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Need to filter row data by comparison of two columns

I hope I can explain this well enough.  I have a spreadsheet that shows a list of employees and their information (about 2000 rows), one of the columns (B) is their ID, I then have another column (column A) that I imported into the spreadsheet so it is not in the same order as column B; however, it does correspond to column B (about 128 rows), I am trying to setup excel to only show me the 128 rows (not all 2000), but I want to see the corresponding employee information from columns C-H but filtered by the employee ID's from column A?  Does this make sense?  

So, Column A has ID's and Column B has ID's, the additional columns C-H have information about the employee's as related to the ID from column B, but I want to filter column B to match the same ID from column A so that I can ultimately see the employee information about all the employees who's ID's are listed in column A.

Thank you
0
WJM
Asked:
WJM
  • 4
  • 4
  • 2
  • +1
2 Solutions
 
teedo757Commented:
Are you using excel?
0
 
WJMAuthor Commented:
Sorry, Yes.
0
 
AdrianMECommented:
See if you can use AutoFilters to solve this problem

It lets you filter and keep the rows related to each other.

You can see a video demonstrating this process below.

http://www.youtube.com/watch?v=4-UcgW8NMnU
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
WJMAuthor Commented:
I have used filters before, but watched the video nonetheless just in case there was something but did not see anything she went over that would help out here.

I even tried filters before I posted here because I thought there might be a way, what I need to do is be able to filter off the unique values between two columns.  So filters will work off one column but what I need to do is say, only filter where the value from Column A = the Value from Column B and where those values match, then show me the corresponding columns C-H
0
 
AdrianMECommented:


So you need to match up Column A and Column B because currently column A is next to the correct column B?

Column A	Column B	Column C	Column D
New		DataID		Data Set 1	DataSet 2
****************************************************************
ID1		ID3		Data1		DataXYZ1
ID2		ID22		Data2		DataXYZ2
ID3		ID21		Data3		DataXYZ3
		ID1		Data4		DataXYZ4
		ID34		Data5		DataXYZ5
		ID44		Data6		DataXYZ6
		ID2		Data7		DataXYZ7
		ID33		Data8		DataXYZ8
		ID5		Data9		DataXYZ9
		ID10		Data10		DataXYZ10

Open in new window

0
 
AdrianMECommented:
I tested this one that compares two columns when they are not next to the corresponding row yet.

Screenshot MS Website

http://support.microsoft.com/kb/213367 
0
 
WJMAuthor Commented:
Thank you Adrian, that didn't quite do what I needed, it simply populated the B column with the same numbers from the A column.  What I need is the ability to filter columns B-H based on the whether or not Column B = Column A, if it does not equal then I don't want to see those rows.

As I have thought more about this, I do not think it is possible in Excel to accomplish what I'm trying to do here, I may need to take another approach...
0
 
AdrianMECommented:
Sorry about that, I fixed the formula so that it performs the operation the opposite way and it can be filtered to match columns on the right.

This is the formula I used based on the way you described the problem.

=IF(ISERROR(MATCH(C3,$A$3:$A$12,0)),"",C3)

Here is a link to an example I made including directions on filtering data.

http://dl.dropbox.com/u/4060061/789078097890780975545474567456756789bvbh/Sample%20Workbook.xlsx




0
 
Rob HensonIT & Database AssistantCommented:
Rather than pasting into column A, ie right next to the existing data, paste into a separate area and then do lookup formulae to pull the data from the existing data table.

Cheers
Rob H
0
 
WJMAuthor Commented:
Thank you Rob.  How would I got about writing a lookup formula that references one worksheet to another?  Or am I misunderstanding?

By the way, I was able to accomplish what I needed through a query in SQL Server; however, I'd still like to know if this can be done in Excel, so any additional thoughts by anyone would be greatly appreciated, thank you.

Bill
0
 
Rob HensonIT & Database AssistantCommented:
See basic example in attached.

if the lookup and the data are in separate workbooks the cell references for the data will obviously need to include the path. Move the data sheet to another workbook and the references will change so you can see what it would be.

Cheers
Rob H lookup-example.xls
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now