Extract item numbers with suffix -09 and copy them below the actuallist
 

Dear Experts:

I got an excel-file with item numbers (2 columns, n-rows) with the
following synthax:

90-434-07-09 item description
90-454-07-04 item description
90-422-45-07 item description
90-234-38-09 item description
etc.


I would like to be able to copy the ones (with a macro or formula)
that have the suffix -09 and insert them below the actual list.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Extract item numbers with suffix -09 and copy them below the actual list
 

On Wed, 16 Jun 2010 01:46:16 -0700 (PDT), andreashermle
wrote:

Dear Experts:

I got an excel-file with item numbers (2 columns, n-rows) with the
following synthax:

90-434-07-09 item description
90-454-07-04 item description
90-422-45-07 item description
90-234-38-09 item description
etc.


I would like to be able to copy the ones (with a macro or formula)
that have the suffix -09 and insert them below the actual list.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

You could use Data/Filter or Advanced Filter

Record a macro while you do that if this will be very repetitive. If
you decide to use a macro, post back and I can help you with selecting
the data and target ranges.

For example, with your list in A1:B5:

A1: Part Number
B1: Description
A2: 90-434-09-09
B2: Item Description
....

N1: Part Number
N2: *-09

Select some single cell in the Data List

Data/Advanced (or Advanced Filter)

Action: Copy to Another Location

List Range: (should self-select the entire list
Criteria Range: $N$1:$N$2
Copy to: select the upper left cell of the range where you want
this

<OK

Extract item numbers with suffix -09 and copy them below theactual list
 

This macro filters h1:i4 &copies to j14. Adjust to suit
Sub filterandcopy()
lr = Cells(Rows.Count, "h").End(xlUp).Row
With Range("H1:I" & lr)
.AutoFilter Field:=1, Criteria1:="=*09"
.Offset(1).Copy Range("J14")
Application.CutCopyMode = False
.AutoFilter
End With
End Sub



On Jun 16, 5:38*am, Ron Rosenfeld wrote:
On Wed, 16 Jun 2010 01:46:16 -0700 (PDT), andreashermle





wrote:
Dear Experts:

I got an excel-file with item numbers (2 columns, n-rows) with the
following synthax:

90-434-07-09 item description
90-454-07-04 item description
90-422-45-07 item description
90-234-38-09 item description
etc.

I would like to be able to copy the ones (with a macro or formula)
that have the suffix -09 and insert them below the actual list.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

You could use Data/Filter or Advanced Filter

Record a macro while you do that if this will be very repetitive. *If
you decide to use a macro, post back and I can help you with selecting
the data and target ranges.

For example, with your list in A1:B5:

A1: * * Part Number
B1: * * Description
A2: * * 90-434-09-09
B2: * * Item Description
...

N1: * * Part Number
N2: * * *-09

Select some single cell in the Data List

Data/Advanced (or Advanced Filter)

Action: *Copy to Another Location

List Range: *(should self-select the entire list
Criteria Range: *$N$1:$N$2
Copy to: * * * *select the upper left cell of the range where you want
this

<OK- Hide quoted text -

- Show quoted text -

Extract item numbers with suffix -09 and copy them below theactual list
 

On 16 Jun., 12:38, Ron Rosenfeld wrote:
On Wed, 16 Jun 2010 01:46:16 -0700 (PDT), andreashermle





wrote:
Dear Experts:

I got an excel-file with item numbers (2 columns, n-rows) with the
following synthax:

90-434-07-09 item description
90-454-07-04 item description
90-422-45-07 item description
90-234-38-09 item description
etc.

I would like to be able to copy the ones (with a macro or formula)
that have the suffix -09 and insert them below the actual list.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

You could use Data/Filter or Advanced Filter

Record a macro while you do that if this will be very repetitive. *If
you decide to use a macro, post back and I can help you with selecting
the data and target ranges.

For example, with your list in A1:B5:

A1: * * Part Number
B1: * * Description
A2: * * 90-434-09-09
B2: * * Item Description
...

N1: * * Part Number
N2: * * *-09

Select some single cell in the Data List

Data/Advanced (or Advanced Filter)

Action: *Copy to Another Location

List Range: *(should self-select the entire list
Criteria Range: *$N$1:$N$2
Copy to: * * * *select the upper left cell of the range where you want
this

<OK- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Ron,

great. This works just fine. Thank you very much for your professional
help. Regards, Andreas

Extract item numbers with suffix -09 and copy them below theactual list
 

On 16 Jun., 14:14, Don Guillett Excel MVP
wrote:
This macro filters h1:i4 &copies to j14. Adjust to suit
Sub filterandcopy()
lr = Cells(Rows.Count, "h").End(xlUp).Row
With Range("H1:I" & lr)
* * .AutoFilter Field:=1, Criteria1:="=*09"
* * .Offset(1).Copy Range("J14")
* * Application.CutCopyMode = False
* * .AutoFilter
* * End With
End Sub

On Jun 16, 5:38*am, Ron Rosenfeld wrote:



On Wed, 16 Jun 2010 01:46:16 -0700 (PDT), andreashermle

wrote:
Dear Experts:

I got an excel-file with item numbers (2 columns, n-rows) with the
following synthax:

90-434-07-09 item description
90-454-07-04 item description
90-422-45-07 item description
90-234-38-09 item description
etc.

I would like to be able to copy the ones (with a macro or formula)
that have the suffix -09 and insert them below the actual list.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

You could use Data/Filter or Advanced Filter

Record a macro while you do that if this will be very repetitive. *If
you decide to use a macro, post back and I can help you with selecting
the data and target ranges.

For example, with your list in A1:B5:

A1: * * Part Number
B1: * * Description
A2: * * 90-434-09-09
B2: * * Item Description
...

N1: * * Part Number
N2: * * *-09

Select some single cell in the Data List

Data/Advanced (or Advanced Filter)

Action: *Copy to Another Location

List Range: *(should self-select the entire list
Criteria Range: *$N$1:$N$2
Copy to: * * * *select the upper left cell of the range where you want
this

<OK- Hide quoted text -

- Show quoted text -- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Dear Don,

thank you very much for your great help. It works as desired.

Thank you very much for your professional help. Regards, Andreas

Extract item numbers with suffix -09 and copy them below the actual list
 

On Thu, 17 Jun 2010 00:15:45 -0700 (PDT), andreashermle
wrote:



Hi Ron,

great. This works just fine. Thank you very much for your professional
help. Regards, Andreas

You're welcome. Glad to help. Thanks for the feedback.

Don's method is actually a slightly modified version to what you would
get if you recorded a macro while doing the Advanced Filter I
recommended.

Extract item numbers with suffix -09 and copy them below the actual list
 

On Thu, 17 Jun 2010 00:15:45 -0700 (PDT), andreashermle
wrote:



Hi Ron,

great. This works just fine. Thank you very much for your professional
help. Regards, Andreas

I was wrong about Don's Macro. He uses the Auto filter whereas my
method uses the Advanced Filter.

If you were going to use a macro with the Advanced filter, it would
look like:

=========================
Option Explicit
Sub GetItems()
Dim rCriteria As Range
Dim rSrc As Range
Dim rDest As Range

'Could use an input box to set up criteria
'for extracting numbers

'Assume Data starts in A1, with headers
'in first row

Set rSrc = Range("A1").CurrentRegion
Set rDest = rSrc.End(xlDown).Offset(2)

'Use some unused area for the Criteria Range
Set rCriteria = Range("AA1:AA2")
rCriteria(1) = rSrc(1) 'Copy Header
rCriteria(2) = "*-09"

rSrc.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rCriteria, CopyToRange:=rDest, Unique:=False

End Sub
=================================

Extract item numbers with suffix -09 and copy them below theactual list
 

On Jun 17, 12:10*pm, Ron Rosenfeld wrote:
On Thu, 17 Jun 2010 00:15:45 -0700 (PDT), andreashermle

wrote:

Hi Ron,

great. This works just fine. Thank you very much for your professional
help. Regards, Andreas

I was wrong about Don's Macro. *He uses the Auto filter whereas my
method uses the Advanced Filter.

If you were going to use a macro with the Advanced filter, it would
look like:

=========================
Option Explicit
Sub GetItems()
*Dim rCriteria As Range
*Dim rSrc As Range
*Dim rDest As Range

*'Could use an input box to set up criteria
*'for extracting numbers

'Assume Data starts in A1, with headers
'in first row

Set rSrc = Range("A1").CurrentRegion
Set rDest = rSrc.End(xlDown).Offset(2)

'Use some unused area for the Criteria Range
Set rCriteria = Range("AA1:AA2")
*rCriteria(1) = rSrc(1) 'Copy Header
*rCriteria(2) = "*-09"

* * rSrc.AdvancedFilter Action:=xlFilterCopy, _
* * *CriteriaRange:=rCriteria, CopyToRange:=rDest, Unique:=False

End Sub
=================================

Hi Ron,

I really appreciate your great support and your giving me insight in
some many things.
Thank you very much. It works just fine.

Regards, Andreas