Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ================================= |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with sorting numbers with text suffix | Excel Discussion (Misc queries) | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
How to add a suffix to a whole column of different part numbers? | Excel Worksheet Functions | |||
Sorting Numbers w/Aplha Suffix | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |