vlookup variation??

[alanoreilly]

Is it possible to use a vlookup to produce a number of results…

I have a list of surnames, and I want to use the vlookup to find all the Smiths in the list, obviously a basic vlookup only brings back the first Smith it finds….

=vlookup(A1, download,1,false) , where A1 is some sort of search box for users

or is there a simple VB solution??

 

[rae30]

Mr Excel Guru's

If on Sheet 1 I had a data sheet, with 1000 lines of data.

How can I automatically list all rows in Sheet1 that in ColumnA = name of current sheet.

ie sheet2="Smith" list all six Smith occurances
Sheet3="Jackson" list all 15 Jackson occurances
etc.

I know how to use the autofilter, but I was wondering if there is a quicker way to do this, as I could have 10-15 sheets to copy and paste.

Ray

 

[rae30]

???

 

[markdenovan]

I've just used Method 1 with great success.

I've also been asked to do it with the search being a date.

The problem now is all I get are #n/a , is this down to the BIGSTR definition, do I need to change this to some sort of date format definition?

ie

Search on:
XT505
Result:
Works fine

Search on:
01/03/2004
Result:
All I get are na# s

Any ideas?

Many thanks,

Mark

 

[Aladin Akyurek]

I've just used Method 1 with great success.

I've also been asked to do it with the search being a date.

The problem now is all I get are #n/a , is this down to the BIGSTR definition, do I need to change this to some sort of date format definition?

ie

Search on:
XT505
Result:
Works fine

Search on:
01/03/2004
Result:
All I get are na# s

Any ideas?

Many thanks,

Mark

Replace BigStr with BigNum, which you define as referring to:

9.99999999999999E+307

 

[markdenovan]

Many thanks for the speedy answer.

Mark

 

[thomachr]

I've just used Method 1 with great success.

I've also been asked to do it with the search being a date.

The problem now is all I get are #n/a , is this down to the BIGSTR definition, do I need to change this to some sort of date format definition?

ie

Search on:
XT505
Result:
Works fine

Search on:
01/03/2004
Result:
All I get are na# s

Any ideas?

Many thanks,

Mark

Replace BigStr with BigNum, which you define as referring to:

9.99999999999999E+307

I know this thread is a little old, but I am trying to do something very simmilar to this sheet. I have a very large log file, and want to be able to go to a seperate sheet, imput a date, or range of dates and have all of the records from that time period populate on a clean sheet.

I have folowed the Method 1 from above, and have edited the method to define BigNum, but i am getting nothing but "#N/A" errors whenever I select a date.

Can someone help me figure out what might be the problem?

Thanks in advance!

 

[Aladin Akyurek]

...

I know this thread is a little old, but I am trying to do something very simmilar to this sheet. I have a very large log file, and want to be able to go to a seperate sheet, imput a date, or range of dates and have all of the records from that time period populate on a clean sheet.

I have folowed the Method 1 from above, and have edited the method to define BigNum, but i am getting nothing but "#N/A" errors whenever I select a date.

Can someone help me figure out what might be the problem?

Thanks in advance!

Could you post a small sample?

 

[rae30]

...wow, good to see my old problems are still current.

...

 

[thomachr]

...

I know this thread is a little old, but I am trying to do something very simmilar to this sheet. I have a very large log file, and want to be able to go to a seperate sheet, imput a date, or range of dates and have all of the records from that time period populate on a clean sheet.

I have folowed the Method 1 from above, and have edited the method to define BigNum, but i am getting nothing but "#N/A" errors whenever I select a date.

Can someone help me figure out what might be the problem?

Thanks in advance!

Could you post a small sample?

I am not firmiliar enough with the board to know how to post a sample of the file.

Basically I have a log file in which each row is a seperate entry. The sheet ranges from A2:BE5000 with row 1 being column headings.

I want to be able to go to a new sheet type a date or idealy a range of dates (say ones weeks worth), have a macro search for those dates in column D of the log sheet and populate the second sheet with only the records that correspond to the date entered.

Also idealy I would only need some of the original colums to populate into the new sheet: A,B,D,F,G,H, J, K, M, S and AB.

I have tried using the Method 1 offered in this threat, but to no avail as all I am able to return is /#NA errors instead of the corresponding records.

Alternatively, I have found a solution offered by BrianB here:
http://www.mrexcel.com/board2/viewtopic.php?t=150953&highlight=lookup which I adapted slightly to ALMOST do what I need.

The alternative Copy/Paste method returns the proper records for a single date, but I do not know how to edit the range copied to return just the columns I need.

I think this will be a workable solution, but I need a little help with the code.

Thanks all for any help you can offer!!!

 

[Dan Waterloo]

You could also take an entirely different approach and use MS Query (available from within Excel) to make the data selection. If you understand SQL, then you can use MS Query to write a parameter query that lets you focus in on a particular data or a range of dates and that also allows you to select which columns will be displayed.

For example, I made a test workbook called QueryTest. On a worksheet called Data I have the following data:

Surname First_Name Age City
Smith Adam 46 Toronto
Jones Shirley 32 Los Angeles
Smith Warren 71 San Diego
Green Dallas 27 Dallas
Smith Zulu 13 Pittsburgh
Then on a second worksheet called Query, I created a query starting on row 3 by using /Data/Get External Data/New Database Query.

The SQL itself looks like this:

SELECT `Data$`.Surname, `Data$`.`First_Name`, `Data$`.City
FROM `C:\Documents and Settings\My Documents\QueryTest
WHERE (`Data$`.Surname=?)
ORDER BY `Data$`.`First Name`

By leaving Age out of the query, this column simply does not show up in the result set.

I referred the query back to the workbook itself - you can obviously choose whatever location you want to store it in.

After the query was created I right clicked on C3 of the Query and picked Parameters to state where I wanted to pick up the parameter value from.
In the dialog box I chose "Get the value from the following cell: =Query!$A$1 " and checked the box next to "Refresh automatically when cell value changes."

So, when I enter "Green" in cell A1 of Query I get the following result set:

Green

Surname First Name City
Green Dallas Dallas

And when I enter "Smith" in cell A1 I get

Smith

Surname First_Name City
Smith Adam Toronto
Smith Warren San Diego
Smith Zulu Pittsburgh

There is no need to clear out old data. Adding or removing columns is just a matter of editing the SQL query. (Right click on C3, choose Edit Query, and then click on the SQL button.)

MS Query is worth knowing about. You can also easily suck in data from MS Access databases this way.