I'm trying to create a formula to lookup the master sheet, find all
occurances of the loaction and return the values in the location sheet.
(sample outlined below...)

I HAVE A MASTER SHEET WHICH CONTAINS DATA IMPORTED VIA A CSV FILE

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7


I THEN HAVE SEPARATE SHEETS FOR EACH LOCATION

Sheet 1 = Location A
Sheet 2 = Location B
Sheet 3 = Location C

THEN EACH LOCATION SHEET WILL ONLY SHOW THE DATA FOR THE ROWS WHERE THE
VALUE IN COL B = THE LOCATION.

Therefo

*Sheet 1 *

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4

*Sheet 2 *

Row 1 = 10003 || Location B || Brand 4 || Model 8
Row 2 = 10005 || Location B || Brand 3 || Model 7

*Sheet 3 *

Row 1 = 10004 || Location C || Brand 2 || Model 2

Any help is appreciated...


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

I can't think of a way to do this with IF functions without getting stuck
with a lot of false returns in each of the location sheets. If i were you i
would just select the row 1 in your master cheet, go to data, filter, then
select auto filter. Now you can click on location and scroll down to
whichever location you want, and you'll only see that data.

Sorry i couldn't be of more help.

Derek

jarviscars wrote:
I'm trying to create a formula to lookup the master sheet, find al
occurances of the loaction and return the values in the location sheet
(sample outlined below...)

I HAVE A MASTER SHEET WHICH CONTAINS DATA IMPORTED VIA A CSV FIL

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7

I THEN HAVE SEPARATE SHEETS FOR EACH LOCATIO

Sheet 1 = Location A
Sheet 2 = Location B
Sheet 3 = Location C

THEN EACH LOCATION SHEET WILL ONLY SHOW THE DATA FOR THE ROWS WHERE TH
VALUE IN COL B = THE LOCATION

Therefo

*Sheet 1 *

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4

*Sheet 2 *

Row 1 = 10003 || Location B || Brand 4 || Model 8
Row 2 = 10005 || Location B || Brand 3 || Model 7

*Sheet 3 *

Row 1 = 10004 || Location C || Brand 2 || Model 2

Any help is appreciated..


--
Message posted via http://www.officekb.com

Assume A1:D1 is the header row on all 4 sheets(Master, LocationA,
LocationB, and LocationC), and data starts on row 2.

On sheet LocationA:
A2 =
INDEX(Master!$A$1:$D$6,SUMPRODUCT(SMALL(ROW(Master !$2:$6)*(Master!$B$2:$B$6="Location
A"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Location
A")+ROW()-1)),COLUMN()) (Copy across and down)

Similarly on sheet LocationB and LocationC:
A2 =
INDEX(Master!$A$1:$D$6,SUMPRODUCT(SMALL(ROW(Master !$2:$6)*(Master!$B$2:$B$6="Location
B"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Location
B")+ROW()-1)),COLUMN()) (Copy across and down)

A2 =
INDEX(Master!$A$1:$D$6,SUMPRODUCT(SMALL(ROW(Master !$2:$6)*(Master!$B$2:$B$6="Location
C"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Location
C")+ROW()-1)),COLUMN()) (Copy across and down)


Hope this helps.



jarviscars Wrote:
I'm trying to create a formula to lookup the master sheet, find all
occurances of the loaction and return the values in the location sheet.
(sample outlined below...)

I HAVE A MASTER SHEET WHICH CONTAINS DATA IMPORTED VIA A CSV FILE

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7


I THEN HAVE SEPARATE SHEETS FOR EACH LOCATION

Sheet 1 = Location A
Sheet 2 = Location B
Sheet 3 = Location C

THEN EACH LOCATION SHEET WILL ONLY SHOW THE DATA FOR THE ROWS WHERE THE
VALUE IN COL B = THE LOCATION.

Therefo

*Sheet 1 *

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4

*Sheet 2 *

Row 1 = 10003 || Location B || Brand 4 || Model 8
Row 2 = 10005 || Location B || Brand 3 || Model 7

*Sheet 3 *

Row 1 = 10004 || Location C || Brand 2 || Model 2

Any help is appreciated...


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Thanks Morrigan... but that seems to be returning any value of the same
row in the master sheet.

I found a sample workbook by Debra Dalgleish which used macros and
filters to create sheets dynamically based on the value of a certain
column. This appears to do what I want it to do but i'm no VB expert
and when trying to convert it across to my workbook, i get a runtime
error...

Run-time error '1004':
Method 'Range' of object '_Global' failed

When I click <<Debug the vb editor seta a break point at line 10

Code:
--------------------
Set rng = Range("Database")
--------------------


Am I missing something completely obvious???
(Code below)

Thanks in advance.


Code:
--------------------
Option Explicit

Sub ExtractLocations()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Cars without Photos")
Set rng = Range("Database")

'extract a list of Locations
ws1.Columns("B:B").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("B1").Value

For Each c In Range("J2:J" & r)
'add the Location to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A2"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A2"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function
--------------------


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

I thought you wanted to collect all the data from the same row. (ie.
put out all the rows that contain "Location A" to sheet LocationA)
Maybe I misunderstood what you wanted to do.

Anyway, I am not VBA expert and would not even consider myself as a
beginner. :) Sorry cannot help you on VBA.


jarviscars Wrote:
Thanks Morrigan... but that seems to be returning any value of the same
row in the master sheet.

I found a sample workbook by Debra Dalgleish which used macros and
filters to create sheets dynamically based on the value of a certain
column. This appears to do what I want it to do but i'm no VB expert
and when trying to convert it across to my workbook, i get a runtime
error...

Run-time error '1004':
Method 'Range' of object '_Global' failed

When I click <<Debug the vb editor seta a break point at line 10

Code:
--------------------
Set rng = Range("Database")
--------------------


Am I missing something completely obvious???
(Code below)

Thanks in advance.


Code:
--------------------
Option Explicit

Sub ExtractLocations()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Cars without Photos")
Set rng = Range("Database")

'extract a list of Locations
ws1.Columns("B:B").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("B1").Value

For Each c In Range("J2:J" & r)
'add the Location to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A2"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A2"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function
--------------------



--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

I thought you wanted to collect all the data from the same row. (ie. put
out all the rows that contain "Location A" to sheet LocationA)

That's exactly what i wanted to do however if my recordset is as
follows:

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7

and I have a separate sheet for each location...

[SHEET 1] Location A
If I place the formula into every cell, it transfers all the data...
(including rows containing location B & C)


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

I tested it at work and it only pulled all the rows that contained
"Location A". I will try again tomorrow.



jarviscars Wrote:
That's exactly what i wanted to do however if my recordset is as
follows:

Col A = ID Number
Col B = Location
Col C = Brand
Col D = Model

Row 1 = 10001 || Location A || Brand 1 || Model 1
Row 2 = 10002 || Location A || Brand 2 || Model 4
Row 3 = 10003 || Location B || Brand 4 || Model 8
Row 4 = 10004 || Location C || Brand 2 || Model 2
Row 5 = 10005 || Location B || Brand 3 || Model 7

and I have a separate sheet for each location...

[SHEET 1] Location A
If I place the formula into every cell, it transfers all the data...
(including rows containing location B & C)


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

I tried again and didn't seem to be wrong to me. Since I am assuming
row1 to be your header, so there is ROW()-1 in my formula. If row1 is
where your data start, then replace "ROW()-1" with "ROW()".

ie.

A1 =
INDEX(Master!$A$1:$D$5,SUMPRODUCT(SMALL(ROW(Master !$1:$5)*(Master!$B$1:$B$5="Location
A"),COUNTA(Master!$B$1:$B$5)-COUNTIF(Master!$B$1:$B$5,"Location
A")+ROW())),COLUMN())

I've also attached my file(without header).


Hope it helps.


+-------------------------------------------------------------------+
|Filename: Choose.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3657 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Thanks morrigan,

I looked at your sample file and can follow the steps through... It
works fine with the sample but when I apply it to the data sheet i'm
working with... something gets 'lost in translation'

I've attached my data file for you to look at?

Cheers,


+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3663 |
+-------------------------------------------------------------------+

--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Have a look at the attached file. Hope it will help.


+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2-R1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3665 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Thanks for that..... Works great!

Could you please help me with an IF statement to return an empty cell
if the formula evaluates to false??? (at the moment the cell returns
#NUM if it does not evaluate to true.


Code:
--------------------
=INDEX('Cars without Photos'!$A$1:$H$500,SUMPRODUCT(SMALL(ROW('Cars without Photos'!$3:$500)*('Cars without Photos'!$B$3:$B$500=" Jarvis Gepps Cross"),COUNTA('Cars without Photos'!$B$3:$B$500)-COUNTIF('Cars without Photos'!$B$3:$B$500," Jarvis Gepps Cross")+ROW()+500-COUNTA('Cars without Photos'!$B$3:$B$500)-2-2)),COLUMN())
--------------------


Cheers,


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Maybe something like:

IF(ISERROR(...),"",(...))


jarviscars Wrote:
Thanks for that..... Works great!

Could you please help me with an IF statement to return an empty cell
if the formula evaluates to false??? (at the moment the cell returns
#NUM if it does not evaluate to true.


Code:
--------------------
=INDEX('Cars without Photos'!$A$1:$H$500,SUMPRODUCT(SMALL(ROW('Cars without Photos'!$3:$500)*('Cars without Photos'!$B$3:$B$500=" Jarvis Gepps Cross"),COUNTA('Cars without Photos'!$B$3:$B$500)-COUNTIF('Cars without Photos'!$B$3:$B$500," Jarvis Gepps Cross")+ROW()+500-COUNTA('Cars without Photos'!$B$3:$B$500)-2-2)),COLUMN())
--------------------


Cheers,


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Fantastic.....

Muchly appreciated!


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Is it possible to substitute the string with wildcards???

I want to have a couple of sheets that search for stock # prefix...

here's the modified formula that i've come up with but it's not
returning the correct values...


Code:
--------------------
=IF(ISERROR(INDEX(Norwood!$A$1:$H$500,SUMPRODUCT(S MALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$500="D* "),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())),"",(INDEX(Norwood!$A$1:$H$500,SUMPR ODUCT(SMALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$ 500="D*"),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())))
--------------------


Essentially i've modified the formula to look on the sheet 'Norwood' in
'Column A' for anything with containing 'D*'

Unfortunately it doesn't return all the results. It appears to count
the number of rows containing the D prefix but then just returns the
top X number of rows as counted.

Thanks


--
jarviscars
------------------------------------------------------------------------
jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631
View this thread: http://www.excelforum.com/showthread...hreadid=390438

I am not sure how to use wildcards in a formula.

I edited the sheet "Norwood" and added a new sheet "Test". However,
formula gets fairly long after the IF(ISERROR(...),"",(...)) statement
in implemented. Personally I do not like it. It seems like you will
have quite a few sheets and each will have up to 500 rows of formula.
Time to complete calculation can be increased dramatically.

Good luck.



jarviscars Wrote:
Is it possible to substitute the string with wildcards???

I want to have a couple of sheets that search for stock # prefix...

here's the modified formula that i've come up with but it's not
returning the correct values...


Code:
--------------------
=IF(ISERROR(INDEX(Norwood!$A$1:$H$500,SUMPRODUCT(S MALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$500="D* "),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())),"",(INDEX(Norwood!$A$1:$H$500,SUMPR ODUCT(SMALL(ROW(Norwood!$3:$500)*(Norwood!$A$3:$A$ 500="D*"),COUNTA(Norwood!$A$3:$A$500)-COUNTIF(Norwood!$A$3:$A$500,"D*")+ROW()+500-COUNTA(Norwood!$A$3:$A$500)-2-2)),COLUMN())))
--------------------


Essentially i've modified the formula to look on the sheet 'Norwood'
in 'Column A' for anything with containing 'D*'

Unfortunately it doesn't return all the results. It appears to count
the number of rows containing the D prefix but then just returns the
top X number of rows as counted.

Thanks


+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2-R2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3672 |
+-------------------------------------------------------------------+

--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390438

Hi,

I think another option for you is make a database query. While making
the query you can filter the data.

Have a look at the attachment.

HTH


+-------------------------------------------------------------------+
|Filename: WebsiteStockPhotoReport_template2-R1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3674 |
+-------------------------------------------------------------------+

--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=390438