Replicating database query with formulas

  • Thread starter Thread starter Hardy
  • Start date Start date
H

Hardy

Well, that's the best way I can describe it.

I have worksheet A of data in Excel, c. 1000 rows. Col A has unique
identifier for each row. Col B has categories ( 5 different ones).

What I want to do in another sheet (B) is essentially replicate what I
would do with the Auto filter function, i.e. show just those rows that
have 'Sovereigns' in Col B (refering to sovereing bonds), but not use
autofilter and also show data in one continuous block.

I think I am half way there in terms of thinking it out. In the other
worksheet B, you can use COUNTIF to determine the number of occurances
of 'Sovereign' in Col B. Then in the first row in worksheet, you can
have formula that could find the first occurance of sovereign in col B
(returning row number or unique identifier via INDIRECT function), then
row two would return the second occurence... right up to the number of
occurances of sovereign. I don't think I can use MATCH because what I
am looking for is not unique. Rather, I want to say find me the ith
occurance of it, where i is variable. Also, I cannot sort Col B
because I will have several worksheets doing the same thing for
different categories.

In databases you do it all the time via queries. Thing is I need to
perform complex calculations and sort the data in excel before I send
it to datbase program.

Grateful for any thoughts to fill in the gaps, or for someone to tell
me not to make life complicated and write macro code to do this. :)
 
You may want to use a macro:

Debra Dalgleish's has some code that does this kind of thing:

http://www.contextures.com/excelfiles.html

There are a couple of files you may want to steal from:

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb
 
You can do this with the MATCH function.
Suppose that you have your database on Sheet1 with 100 rows in it and
the search terms in column B.
Now on Sheet2 enter the following formula in cell A2
=MATCH(search_term,OFFSET(Sheet1!$B$1,A1,0,100,1),0)+A1
and copy this formula down.
This will give you a column containing the row numbers of your records
which match the search_term.
You can then use an INDIRECT function to return whatever fields you
require from you database.
 
Back
Top