Autofilter tables Uniquelist

[my.wandering.mind]

I have a table and say I would like to create a unique list from
column2 and store the list in a spearate sheet. Give this unique list
a name.

Use this unique list in a FOR EACH loop and apply autofilter to two
tables on two different sheets.
(one table is from which the unique list was created and other in a
different sheet)

I have attached a file wiith the table headers. In sheet2 there is a
table and i need to produce an unique list from column MODEL. and then
name that list separately.

Using this unique list I would like to autofilter the same table on
sheet2 and table on sheet3
simultaneously

Can this be done?
Any help is appreciated. I have been stuck in this for while now.

Thanks
vishnu

 

[Joel]

You can't post files on this website


Sheets("Sheet1").Columns("B:B").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Columns("C:C"), _
Unique:=True
with Sheets("Sheet2")
LastRow = .Range("C1").end(xldown).row
set ListName = .Range("C1:C" & Lastrow)
end with

 

[my.wandering.mind]

Thanks Joel,

It works fine.

Kindly excuse for the attachment, was not aware.

Could you also guide me with the following

For each value from the unique list I filter two tables in two
different sheets and would like to multiply corresponding column
values

I had used a similar code to find corresponding column values and was
trying to modify the same, the following is

Dim rFoundIt As Range
Dim iLoop As Integer

Dim model As Range


For Each model In Worksheets("temp1").Range("filterlist")

Worksheets("temp1").Activate
Selection.AutoFilter Field:=3, Criteria1:=model

Worksheets("rawdata").Activate
Selection.AutoFilter Field:=5, Criteria1:=model


'Select corresponding column value for each model


With Sheets("rawdata").Range("data")

Set rFoundIt = .Cells(1, 1)

For iLoop = 1 To WorksheetFunction.CountIf _
(Sheets("temp1").Range("output"),
model)

Set rFoundIt = .Find(What:=model, After:=rFoundIt, _
LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

Next iLoop
''' The above loop selects appropriate data from the "rawdata" sheet.
How do I select columns from "temp1" sheet. to multiply values.

Is this the correct way of doing this? Is there any other way.

Thanks
vishnu

 

[Joel]

Is this code better?

Sub test2()



Dim rFoundIt As Range
Dim iLoop As Integer

Dim model As Range


For Each model In Worksheets("temp1").Range("filterlist")

Worksheets("temp1").Activate
'selection is not defined, you should use a real range
' I think you should replace selection with model
Selection.AutoFilter Field:=3, Criteria1:=model
'use special cells
Worksheets("temp1").Columns("C:C").SpecialCells(xlCellTypeVisible).Copy
' or
Set temp1range =
Worksheets("temp1").Columns("C:C").SpecialCells(xlCellTypeVisible)

Worksheets("rawdata").Activate
Selection.AutoFilter Field:=5, Criteria1:=model
'use special cells
Worksheets("rawdata").Columns("E:E").SpecialCells(xlCellTypeVisible).Copy
' or
Set rawdatarange =
Worksheets("rawdata").Columns("E:E").SpecialCells(xlCellTypeVisible)

'you can copy the two ranges to a new worksheet
With Sheets("New")
temp1range.Copy Destination:=.Range("A1")
rawdatarange.Copy Destination:=.Range("B1")

RowCount = 1
Do While .Range("A" & RowCount) <> ""
Answer = .Range("A" & RowCount) * .Range("B" & RowCount)

RowCount = RowCount + 1
Loop
End With


Next model



End Sub

 

[my.wandering.mind]

Hi Joel,

Thanks for the code.

It works in parts, possibly because I did not explain myself clearly.

When I run your code I get an error "Run time error 9 out of range".

Secondly your code is selecting the entire criteria column, when I
filter the list I would like to select the entire visible row and put
it into a sheet.

I think I can do this by Offset(0,
ActiveCell.CurrentRegion.Columns.Count + 1) and copy the entire row.

Did I miss something?

Thanks
Vishnu

 

[Joel]

Only the visible cells get copied with the following statment

Worksheets("temp1").Columns("C:C").SpecialCells(xlCellTypeVisible).Copy

to change this so the entire row gets copied is simple

Worksheets("temp1").Columns("C:C").SpecialCells(xlCellTypeVisible).entirerow.Copy

You are probably getting a run time error because of the number of filter
columns on your worksheet.

using AutofilterField:=5 referes to the 5th filter column in the worksheet,
not column E (the fifth column). I can't tell by the code which columns on
your worksheet are filtered. You may need to adjust the columns I selected
to get the code to work with your data.

 

[my.wandering.mind]

Joel,

Thanks I got it.

I am now stuck on how to multiply column.

Sheet temp1:

Headers (A1:Q1)
country group model segment 2007penetration supplier 2005 2006 2008
2009 2010 2011 2012 2013 2014 2015

Sheet rawdata:
Headers (A1: S1)
region country group MM model type segment GS 2005 2006 2007 2008
2009 2010 2011 2012 2013 2014 2015

When I filter both the sheets using "filterlist" from "sheet2"

I need to change the following columns in table in sheet "temp1"
columns 2005 to 2015 should contain the product of the respective
columns

ex
"Temp1" after Modification should look like the following

2005 column should be = "original2005 column temp1" *
"rawdata2005 column value"
)

Would you be able to guide me?

Thanks
Vishnu

 

[Joel]

I assum the columns on both sheets are in the same order (you skipped 2007 on
temp1)

with sheets("rawdata")
LastRow = .range("A" & Rows.count).end(xlup).row
for ColCount = 1 to Range("S1").Column
for RowCount = 2 to LastRow
.Cells(RowCount,Colcount) =
sheets("temp1").Cells(RowCount,Colcount) * _
.Cells(RowCount,Colcount)
next RowCount
next ColCount
end with

 

[my.wandering.mind]

Thanks Joel for your time. Your code works. I was able to modify it
with rows