Excel 2003 - VBA - Lookup to determine ROW of Match

  • Thread starter Thread starter Craig Brandt
  • Start date Start date
C

Craig Brandt

Hi;

I would like to do a lookup with the intent of determining the row of the
match.

I have several years of data in a flat file and am interested in doing a
vertical lookup confined to a months worth of data. To do this I need to
know what row the month starts and what row it ends.

Thanks,
Craig
 
Please don't do that.
Use Pivot tables.
Pivot tables can automatically group data in months.
Pivot tables can read flat files.

Pivot tables are a teeny weeny bit difficult to master.
Once you have cracked the idea, you will save yourself years of writing
(silly) lookups.

Zumble.
 
Zumble:

I have tried Pivot Tables when I am looking for a specific answer, but what
I am currently working on is an adaptation of a complex worksheet that
contains daily returns data for in excess of 600 clients over a period of 2
years. The changes I am making are relatively simple, but just need to know
how to determine the location of groups of data.

Is it possible to get the row of a match without running "loops', which are
too time consuming.

Craig
 
There is a function in XL which returns the rownumber
= ROW()

Or you could use database functions to extract only the data which
corresponds to a specific month or range of dates.

Z.
 
Hi Craig,

You can use the MATCH() function to find the (relative) row number of a search argument in a table.

But I doubt you're on the right track with this approach.
Pivot tables, Subtotals and concatenated keys are all more likely keys for solutions, in my opinion.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Zumble:
|
| I have tried Pivot Tables when I am looking for a specific answer, but what
| I am currently working on is an adaptation of a complex worksheet that
| contains daily returns data for in excess of 600 clients over a period of 2
| years. The changes I am making are relatively simple, but just need to know
| how to determine the location of groups of data.
|
| Is it possible to get the row of a match without running "loops', which are
| too time consuming.
|
| Craig
|
| | > Please don't do that.
| > Use Pivot tables.
| > Pivot tables can automatically group data in months.
| > Pivot tables can read flat files.
| >
| > Pivot tables are a teeny weeny bit difficult to master.
| > Once you have cracked the idea, you will save yourself years of writing
| > (silly) lookups.
| >
| > Zumble.
| >
| >
| >
| > "Craig Brandt" <[email protected]> a écrit dans le message de | > [email protected]...
| > > Hi;
| > >
| > > I would like to do a lookup with the intent of determining the row of
| the
| > > match.
| > >
| > > I have several years of data in a flat file and am interested in doing a
| > > vertical lookup confined to a months worth of data. To do this I need to
| > > know what row the month starts and what row it ends.
| > >
| > > Thanks,
| > > Craig
| > >
| > >
| >
| >
|
|
 
Back
Top