Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to carve out uniques based on col header whose position may va
In an active sheet, I have col headers in row1, data from row2 down.
I need to carve out uniques based on the "Order ID" col header, and paste these unique lines into a new sheet, then name this new sheet as: UniqueOrderIDs How could the above functionality be done by a sub? There will be only one col header: Order ID within row1. But its position may vary from day-to-day, hence the sub needs to locate the col via the header text, then do the necessary Manually, I extract like this Assuming the col header: Order ID is in R1 Using a empty col to the right, in say, AF2: =IF(R2="","",IF(COUNTIF($R$2:R2,R2)1,"","x")) AR2 is then copied down to last row of data in col B (Col B is always used to determine data extent here). Then I'd apply autofilter on col AF for "x", copy the entire sheet (with the filtered results) n paste it into a new sheet, then rename the sheet as: UniqueOrderIDs |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to carve out uniques based on col header whose position may va
Hi Max
Try the below macro and feedback. Sub Macro() 'For Max 07 Oct 2009 Dim ws1 As Worksheet, ws2 As Worksheet, lngCol As Long Set ws1 = ActiveSheet lngCol = ws1.Rows(1).Find("Order ID").Column Set ws2 = Worksheets.Add(After:=ActiveSheet) ws1.Columns(lngCol).AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ws2.Range("A1"), Unique:=True ws2.Name = "UniqueOrderIDs" End Sub If this post helps click Yes --------------- Jacob Skaria "Max" wrote: In an active sheet, I have col headers in row1, data from row2 down. I need to carve out uniques based on the "Order ID" col header, and paste these unique lines into a new sheet, then name this new sheet as: UniqueOrderIDs How could the above functionality be done by a sub? There will be only one col header: Order ID within row1. But its position may vary from day-to-day, hence the sub needs to locate the col via the header text, then do the necessary Manually, I extract like this Assuming the col header: Order ID is in R1 Using a empty col to the right, in say, AF2: =IF(R2="","",IF(COUNTIF($R$2:R2,R2)1,"","x")) AR2 is then copied down to last row of data in col B (Col B is always used to determine data extent here). Then I'd apply autofilter on col AF for "x", copy the entire sheet (with the filtered results) n paste it into a new sheet, then rename the sheet as: UniqueOrderIDs |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to carve out uniques based on col header whose position ma
Thanks Jacob. But I need the entire sheet of "filtered results" as the output
pasted in UniqueOrderIDs not just the col itself. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to carve out uniques based on col header whose position may va
a non-vba method is simplyto data/ advanced filter
have you tried that? 1) copy the column header to the sheet where you want to put the unique results 2) staying on that sheet run the Advanced filter, using the main sheet to select the source data. Advanced filters don't allow data to be extracted to another sheet, but you can extract TO another sheet this way. "Max" wrote: In an active sheet, I have col headers in row1, data from row2 down. I need to carve out uniques based on the "Order ID" col header, and paste these unique lines into a new sheet, then name this new sheet as: UniqueOrderIDs How could the above functionality be done by a sub? There will be only one col header: Order ID within row1. But its position may vary from day-to-day, hence the sub needs to locate the col via the header text, then do the necessary Manually, I extract like this Assuming the col header: Order ID is in R1 Using a empty col to the right, in say, AF2: =IF(R2="","",IF(COUNTIF($R$2:R2,R2)1,"","x")) AR2 is then copied down to last row of data in col B (Col B is always used to determine data extent here). Then I'd apply autofilter on col AF for "x", copy the entire sheet (with the filtered results) n paste it into a new sheet, then rename the sheet as: UniqueOrderIDs |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to carve out uniques based on col header whose position ma
Thanks for the response. In this instance its needed to be a "hands-free"
process done by a sub, which can then be called from another sub as part & parcel of the entire process. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to carve out uniques based on col header whose position ma
OK...So I have misunderstood.. Try the below
Sub Macro() 'For Max 07 Oct 2009 Dim ws1 As Worksheet, ws2 As Worksheet, lngCol As Long Dim lngLastrow As Long, lngLastCol As Long Set ws1 = ActiveSheet lngCol = ws1.Rows(1).Find("Order ID").Column lngLastrow = ws1.Cells(Rows.Count, lngCol).End(xlUp).Row lngLastCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column Set ws2 = Worksheets.Add(After:=ActiveSheet) ws1.Columns(lngCol).AdvancedFilter Action:=xlFilterInPlace, Unique:=True ws1.Range(ws1.Range("A1"), ws1.Cells(lngLastrow, _ lngLastCol)).Copy ws2.Range("A1") ws2.Name = "UniqueOrderIDs" ws1.AutoFilterMode = False: ws1.ShowAllData End Sub If this post helps click Yes --------------- Jacob Skaria "Max" wrote: Thanks Jacob. But I need the entire sheet of "filtered results" as the output pasted in UniqueOrderIDs not just the col itself. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to carve out uniques based on col header whose position ma
That worked marvellous. Many thanks, Jacob.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Blank Row at a Position Based on Value? | Excel Discussion (Misc queries) | |||
Change delimiter position based off of Last Name | Excel Programming | |||
Header horizontal position | Excel Discussion (Misc queries) | |||
Count Uniques within a list based on value of cell... | Excel Discussion (Misc queries) | |||
change horizontal header position in excel | Excel Discussion (Misc queries) |