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

[andreashermle]

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

 

[Ron Rosenfeld]

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>

 

[Don Guillett Excel MVP]

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

 

[andreashermle]

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 youwant
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

 

[andreashermle]

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














- 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

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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
=================================

 

[andreashermle]

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