Return cell contents based on conditional lookup

J

jarviscars

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

Therefore:

*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..
 
D

Derek Y via OfficeKB.com

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
 
M

Morrigan

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="Locatio
A"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Locatio
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="Locatio
B"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Locatio
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="Locatio
C"),COUNTA(Master!$B$2:$B$6)-COUNTIF(Master!$B$2:$B$6,"Locatio
C")+ROW()-1)),COLUMN()) (Copy across and down)


Hope this helps.
 
J

jarviscars

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

I found a sample workbook by Debra Dalgleish which used macros an
filters to create sheets dynamically based on the value of a certai
column. This appears to do what I want it to do but i'm no VB exper
and when trying to convert it across to my workbook, i get a runtim
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 Functio
 
M

Morrigan

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.
 
J

jarviscars

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)
Click to expand...

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)
 
M

Morrigan

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


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)
Click to expand...
 
M

Morrigan

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 |
+-------------------------------------------------------------------+
 
J

jarviscars

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 |
+-------------------------------------------------------------------+
 
J

jarviscars

Thanks for that..... Works great!

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


Code
 
M

Morrigan

Maybe something like:

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

Thanks for that..... Works great!

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

jarviscars

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(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())),"",(INDEX(Norwood!$A$1:$H$500,SUMPRODUCT(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
 
M

Morrigan

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(...),"",(...)) statemen
in implemented. Personally I do not like it. It seems like you wil
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.


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 no
returning the correct values...
Code
-------------------

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 coun
the number of rows containing the D prefix but then just returns th
top X number of rows as counted.

Thank
Click to expand...

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

Krishnakumar

Hi,

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

Have a look at the attachment.

HT

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help required about vlook fomula 1
Scrabble Value calculation for Welsh words 0
Lookup Formula 4
Formula Help (IF) ... INDEX 1
Using VLOOKUP inside SUMPRODUCT => complex problem 3
Using SUMIF inside SUMPRODUCT => complex problem (suite) 1
Lookup two different columns 10
sorting array formula 2

Top