software

Hey there,

I'm trying to extract key data from a spreadsheet in this way: the
spreadsheet has cells in different columns which may have matching
data. I want Excel to find these matching cells and copy the rows in
which they reside onto a second sheet.

For example:

The first column in question is a listing of Service Orders. I want
Excel to search the second column in question, Purchase Orders, for a
match. When a match or matches are found I want any row containing
that info to be presented on the second spreadsheet.

The example is probably redundant, but hopefully stating it slightly
differently will help illuminate my goal.

Thanks in advance,

Nate--
ThalesNate
------------------------------------------------------------------------
ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
View this thread: www.excelforum.com/showthread...hreadid=507028Life is usually much simpler if you keep your data in one location.

I'd just use one worksheet, but then apply Data|Filter|Autofilter to see the
rows I want to see.

But if you really, really want...

Debra Dalgleish and Ron de Bruin have samples that you may like. Ron's addin
may be sufficient right out of the box.

Debra's site:
www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

And Ron de Bruin's easyfilter.
www.rondebruin.nl/easyfilter.htm

ThalesNate wrote:
gt;
gt; Hey there,
gt;
gt; I'm trying to extract key data from a spreadsheet in this way: the
gt; spreadsheet has cells in different columns which may have matching
gt; data. I want Excel to find these matching cells and copy the rows in
gt; which they reside onto a second sheet.
gt;
gt; For example:
gt;
gt; The first column in question is a listing of Service Orders. I want
gt; Excel to search the second column in question, Purchase Orders, for a
gt; match. When a match or matches are found I want any row containing
gt; that info to be presented on the second spreadsheet.
gt;
gt; The example is probably redundant, but hopefully stating it slightly
gt; differently will help illuminate my goal.
gt;
gt; Thanks in advance,
gt;
gt; Nate
gt;
gt; --
gt; ThalesNate
gt; ------------------------------------------------------------------------
gt; ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
gt; View this thread: www.excelforum.com/showthread...hreadid=507028

--

Dave Peterson


Dave,

I don't think a filter will work because I'm trying to compare data
from separate columns. I did take a peek at Debra's link but there's a
lot of data there and it's hard to discern what pertains to me. Ron's
link was down at the time, but I'll check it later.

I'm including a pic this time to better illustrate my needs, I want
Excel to find any data in the quot;Customer POquot; column beginning with a
quot;20quot; and search the quot;Order Numberquot; column to find the match. Can
filtering do this?

Thanks for your feedback,

Nate -------------------------------------------------------------------
|Filename: ExcelExample.GIF |
|Download: www.excelforum.com/attachment.php?postid=4293 |
-------------------------------------------------------------------

--
ThalesNate
------------------------------------------------------------------------
ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
View this thread: www.excelforum.com/showthread...hreadid=507028Since I connect the newsgroups directly, I can't see the picture.

You'll usually get more responses if you post in plain text--lots of people skip
by posts with attachments (pictures or excel files).
ThalesNate wrote:
gt;
gt; Dave,
gt;
gt; I don't think a filter will work because I'm trying to compare data
gt; from separate columns. I did take a peek at Debra's link but there's a
gt; lot of data there and it's hard to discern what pertains to me. Ron's
gt; link was down at the time, but I'll check it later.
gt;
gt; I'm including a pic this time to better illustrate my needs, I want
gt; Excel to find any data in the quot;Customer POquot; column beginning with a
gt; quot;20quot; and search the quot;Order Numberquot; column to find the match. Can
gt; filtering do this?
gt;
gt; Thanks for your feedback,
gt;
gt; Nate
gt;
gt; -------------------------------------------------------------------
gt; |Filename: ExcelExample.GIF |
gt; |Download: www.excelforum.com/attachment.php?postid=4293 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; ThalesNate
gt; ------------------------------------------------------------------------
gt; ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
gt; View this thread: www.excelforum.com/showthread...hreadid=507028

--

Dave Peterson


Thanks for the tip...

Basically, the details are thus:

_*Service_Order___:_______Purchase_Order__*_
-*2016500 lt;---| -*SO-56137
2016501 | 20273896054
2016502 -* |---gt; 2016500- *I need Excel to associate the first and third lines by associating
cells that share neither a common row nor column, can filtering do
that?

Thanks (again)

Nate--
ThalesNate
------------------------------------------------------------------------
ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
View this thread: www.excelforum.com/showthread...hreadid=507028Not easily and depending on your data--maybe not at all.

Is there anyway you can put the common stuff in a dedicated column--and on each
row?

ThalesNate wrote:
gt;
gt; Thanks for the tip...
gt;
gt; Basically, the details are thus:
gt;
gt; _*Service_Order___:_______Purchase_Order__*_
gt; -*2016500 lt;---| -*SO-56137
gt; 2016501 | 20273896054
gt; 2016502 -* |---gt; 2016500- *
gt;
gt; I need Excel to associate the first and third lines by associating
gt; cells that share neither a common row nor column, can filtering do
gt; that?
gt;
gt; Thanks (again)
gt;
gt; Nate
gt;
gt; --
gt; ThalesNate
gt; ------------------------------------------------------------------------
gt; ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
gt; View this thread: www.excelforum.com/showthread...hreadid=507028

--

Dave Peterson


Well...there's a lot of each data and most items don't match, that's why
I thought query would be the best route initially, like maybe a do-loop
using VBA.--
ThalesNate
------------------------------------------------------------------------
ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
View this thread: www.excelforum.com/showthread...hreadid=507028So if column B contains a po--any po in column A, then copy that single row to a
different location???

You could insert a new column (C?)
and use:

=isnumber(match(b1,a:a,0))

And drag down.

Then filter to show just the trues and copy those rows.

But I'm not sure how that matches up with what you want.

You may want to try explaining it once more.
ThalesNate wrote:
gt;
gt; Well...there's a lot of each data and most items don't match, that's why
gt; I thought query would be the best route initially, like maybe a do-loop
gt; using VBA.
gt;
gt; --
gt; ThalesNate
gt; ------------------------------------------------------------------------
gt; ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
gt; View this thread: www.excelforum.com/showthread...hreadid=507028

--

Dave Peterson


Dave,

That's doing something, which is a start. It's flaky, though, when I
re-sort I have to re-drag so it will reference the correct cell because
it likes to shift down a cell. Hard to explain.

Anyway, I'll monkey with it in my free time tomorrow, hopefully I can
get it to make sense. I appreciate you taking the time to make me some
code, though.

Regards,

Nate--
ThalesNate
------------------------------------------------------------------------
ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
View this thread: www.excelforum.com/showthread...hreadid=507028
Dave,

I used your code to isolate the data I need. Good stuff! Now it comes
down to re-organizing the pertinent data...--
ThalesNate
------------------------------------------------------------------------
ThalesNate's Profile: www.excelforum.com/member.php...oamp;userid=31037
View this thread: www.excelforum.com/showthread...hreadid=507028