Filter two columns by one value

[jomili]

I have lists of contracts; they come in two flavors, "Regular" and "Special". Each has backup data residing in different areas. Each is in it's own column. I am frequently given a contract number, no telling which column it's in, so I have to filter one column first and, if that's the wrong one, filter the second column. Is there a way to have one filter which will filter both columns?

BTW, putting the contract numbers in one column, with a "Regular" and "Special" lable to one side, isn't really workable, because for each contract I have about 50 data fields, and there are thousands of these contracts. To put them in one column would double the information in my workbook, which is pretty large as it is.

[shg]

Are the contract numbers numbers?

Do they have a consistent number of digits and no leading zeros?

[jomili]

No.
No.

Some have leading zeros. Some have mixes of text and numbers. Some have dashes, some have underscores. Label length is between 15 and 100 characters. It's a hodgepodge of formats all considered "Contract Numbers".

[shg]

Not knowing what the layout is, why not just do a Find All for the given string?

[jomili]

Since some contracts might hit various regions, I would need to pull up all the lines that that contract falls in. Doing a Find All I could find all those lines, but how would I go about filtering to just those lines, so I can see all the related data on each line?

[shg]

Again, I don't know the data layout, but once you find them all, you could apply filters (via the UI or code) in each column where they appear.

I confess, I can't really envision what you're trying to do, or why the data is scattered hither, thither, and yon.

[shg]

I do have a short piece of code that applies autofilter to filter the column for the currently selected value:

Please Login or Register  to view this content.

[jomili]

Never have been able to figure out where "Yon" is.

I've attached a small example. If you'll filter for any contract in column f, you'll find multiple lines for that contract. For the most part, the vendor is the same (but not in all) but the Strategy, PAC (Program Code) and DeptID might differ. There are tons more criteria fields I haven't added to this example. Since my job is to trace funding streams (I'm in Budget) for any contract I need to trace where the expense should hit for a specific criteria. Easiest way is to filter for that contract number, so I can see the whole picture. Since the pertinent contract may be in either Column E and Column F (or both) I want to, if possible, filter both columns at the same time.

[jomili]

sorry, missed your last post as I was typing mine. I also have a quickfilter macro, but the problem is that I want to filter the second column by the same filter criteria I filtered the first one by, and get the full results of both columns.

[shg]

You could add a column that catenates cols E & F and filter on that:

	E	F	G	H
2	Special	Regular	HDR_VENDOR_NAME	Filter Here
633		24308647	ROWNSHELLY B	::24308647:
634		24308647	ROWNSHELLY B	::24308647:
635	E529-15-0004-00015	E529-15-0004-00015	MATTINCSHERRYHEWS	:E529-15-0004-00015:E529-15-0004-00015:
636	E529-15-0004-00015		MATTINCSHERRYHEWS	:E529-15-0004-00015::
637	E529-15-0004-00015	530-15-7777-00098	MATTINCSHERRYHEWS	:E529-15-0004-00015:530-15-7777-00098:
638	E529-15-0004-00015	E529-15-0004-00015	MATTINCSHERRYHEWS	:E529-15-0004-00015:E529-15-0004-00015:
639	E529-15-0004-00015		MATTINCSHERRYHEWS	:E529-15-0004-00015::
641	E529-15-0004-00015		MATTINCSHERRYHEWS	:E529-15-0004-00015::
643	53000-7-Z160100433		INCSHI GOVERNMENT SOLUTIONS	:53000-7-Z160100433::

[jomili]

You mean the columns I want to filter on? in the example, it's F and G. BOTH contain contract numbers.

[shg]

Looks like E F, no?

Filter col H for CONTAINS :ContractNum:

[jomili]

Why is it the straight-forward easy answers are the hard ones for me? Simple, elegant, easy solution. Thank you so much!

[shg]

You're welcome.