Look up Data from Worksheet within same Workbook

[Aine]

Hi All,

Sorry, this may seem like an elementary question but....

First of all I have three seperate worksheets containing data within my
workbook.

I want to return all rows from each of those worksheets that matches a
certain criteria to another worksheet, without having to manipulate the
data.

e.g.
I have charge codes beginning in 1-90XX, 1-91xx, etc...
These appear multiple times in the worksheets that contain data
I want to search sheets 1 - 3 to see if any rows contain them & if they
do, I want all these complete rows to appear in another worksheet
within that same workbook.



VLOOKUP function will not work for me as the charge code is in column O
& I can only get it to return data within that row for column O onward.

Also, I am unsure if you can get VLOOKUP to check for multiple
conditions

* What function should I be using?
* Can you use VLOOKUP in a nested statement to search for the various
conditions(charge codes)?
* Can VLOOKUP return a whole row, without having to specify the column?
* Can VLOOKUP return data previous to the column where the criteria of
the search is met?

* Finally: Should I try to use Macros & if so, can someone point me in
the right direction???


Thanks,

Aine

 

[Guest]

Short answers:
Yes - you can use VLOOKUP() within nested (IF) statements.
No - VLOOKUP() only returns a single value out of the matched row
No - VLOOKUP() can't return data to the left of the column the match is
sought in, But!! the LOOKUP() function can do that.

All of the various lookups (HLOOKUP, VLOOKUP and LOOKUP) are pretty much
limited to finding the first entry meeting the lookup parameters, although
LOOKUP can be used to "You can also use the LOOKUP function as an alternative
the IF function for elaborate tests or tests for more than seven conditions.
See the examples in the array form." - from the Help topic on LOOKUP

But in your case, I think that a macro is probably going to be the better
way to develop a solution since you want to return entire rows and you need
to look for mulitple occurances of the same value in your data.

Not knowing how familiar you are with writing macros or coding in general,
it's difficult to point you to far. But I'll give a rough logic flow for you:

The could would have to go to the sheet with the list of charge codes to
find on the other sheets and then loop through all cells containing those
charge codes, on at a time, saving the contents for comparisons on the other
sheets. Then for each sheet with data to be matched it would go to the
beginning of the list and look through it for matches and on each match, copy
that row to a sheet designated to be used to receive those rows of
information, then move to next sheet and repeat and once it has examined all
sheets, then move to the next cell on your charge code sheet, get the next
lookup value and work through the other sheets from beginning to end again.
This could be quite time consuming if you have really long lists.

Do you want to give the macro solution a shot?

 

[Guest]

Should have been...
"The code would have to...", not "The could would have to..."

 

[Guest]

Here's one play using non-array formulas which might also deliver this ..

A sample construct is available at:
http://www.savefile.com/files/9870225
Multiple search & auto-extract rows from 3 sheets based on key charge code
col.xls

Assume source data is in Sheets 1 to 3 (identically structured)
data within cols A to J, from row6 down to say,
a max expected row15 (Col headers in row5)
The key charge codes are assumed in col A

In Sheet1,

Put in K6
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A$1:$A$3,A6))*(Summ!$A$1:$A$3<>""))>0,ROW(A1),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet2

Put in K6
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A$1:$A$3,A6))*(Summ!$A$1:$A$3<>""))>0,ROW(A1)+MAX(Sheet1!K:K),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet3

Put in K6
=IF(A6="","",IF(SUMPRODUCT(ISNUMBER(SEARCH(Summ!$A$1:$A$3,A6))*(Summ!$A$1:$A$3<>""))>0,ROW(A1)+MAX(Sheet2!K:K),""))

Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In a new sheet: Summ,

Entry of the charge code(s) to search will be made within A1:A3 (can be in
any order). To facilitate search input(s), format A1:A3 as text (Format >
Cells > Text)
Input 2 charge codes into A1:A2, say: 1-90, 1-91

Place the same col labels in A5:J5

Then put in A6:

=IF(ISERROR(SMALL(Sheet1!$K:$K,ROW(A1))),
IF(ISERROR(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K)))
IF(ISERROR(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)))),""
INDEX(Sheet3!A:A,MATCH(SMALL(Sheet3!$K:$K,ROW(A1)-(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K))),Sheet3!$K:$K,0)))
INDEX(Sheet2!A:A,MATCH(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K)),Sheet2!$K:$K,0))),
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$K:$K,0)))

Copy A6 across to J6, fill down to cover the *total* expected range in the 3
sheets, ie to J35 (in this example, the expected max data range is: 10 rows
per sheet x 3 sheets = 30 rows)

The auto-extracted results from Sheets 1 to 3 will be returned within
A6:J35, all neatly bunched at the top. Extracted lines will be listed in the
order: Lines from Sheet1, then those from Sheet2, then those from Sheet3.
Adapt to suit ..

---

 

[Aine]

That would be ideal.

Have a good background in programming & logic, but have never touched
macros
I normally work with databases but the company I am contracting for
want it all through excel for visibility purposes(plus they prefer us
not to use MS Access)

Do you know any good site with macros tutorials for these types of
queries?

 

[Guest]

To be honest, I don't roam around a lot looking for code solutions - I
probably should do more of that since they almost always teach something new.

But an excellent book on practical programming within Excel was written by
John Walkenbach - I have a copy titled "Excel 2000 Power Programming with
VBA" (ISBN 0-7645-3263-4, and I believe there's an updated edition "Excel
2003 ..." Should be available through Amazon or your local Barnes & Noble,
Borders, etc.

 

[Guest]

If you want an example of code that will work for this, you can upload a
workbook I quickly tossed together from:
http://www.jlathamsite.com/uploads/codebook_foraine.xls

If you just want to see the working code (which is dependent on layout and
sheet names used in the sample workbook), here it is. It presumes that no
list goes all the way to the end of a worksheet - doesn't trap for that
situation.

Sub GatherDataTogether()
Dim ToBeCompared As String

Dim ListSheet As String
Dim CollateSheet As String
Dim DataSheets(1 To 3) As String
Dim SecondDataSheet As String
Dim ThirdDataSheet As String
Dim ListRowOffset As Long
Dim DataOffsetRow As Long
Dim LC As Integer ' loop counter

'initialize to find our sheets later
ListSheet = "ListSheet"
CollateSheet = "GatheredSheet"
DataSheets(1) = "Sheet1"
DataSheets(2) = "Sheet2"
DataSheets(3) = "Sheet3"

'get ready
'activate the next instruction to speed things up
'Application.ScreenUpdating = False
Worksheets(CollateSheet).Activate
'choose place to move 1st match to
Range("A1").Select
Sheets(ListSheet).Activate
'choose first 'to match' entry
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ToBeCompared = ActiveCell.Value
For LC = LBound(DataSheets) To UBound(DataSheets)
Worksheets(DataSheets(LC)).Activate
Range("A1").Select ' start of data
DataOffsetRow = 0 ' (re)initialize
Do Until IsEmpty(ActiveCell.Offset(DataOffsetRow, 0))
If ActiveCell.Offset(DataOffsetRow, 0) = ToBeCompared Then
DataOffsetRow = DataOffsetRow + 1
Rows(DataOffsetRow & ":" & DataOffsetRow).Copy
'move to destination sheet
Sheets(CollateSheet).Activate
'Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Activate ' prepare for next
'back to data sheet for more checking
Sheets(DataSheets(LC)).Activate
Range("A1").Select ' back to top of list
Else
DataOffsetRow = DataOffsetRow + 1
End If
Loop ' data on current data sheet
Next ' LC loop
Sheets(ListSheet).Activate
ActiveCell.Offset(1, 0).Activate ' move down 1 row
Loop ' lookup list
End Sub

 

[Dean Southgate]

Aine,

If you were to use MSQuery you can return all the rows from each sheet or
you can try a pivot table that could consolidate and totalise!