Create Search Box that returns four different responses

[sperling1349]

I have an Excel database with 4 worksheets, each worksheet is exactly the same, all 41,364 rows (A=Zip, B=city, C=State, D=days, E=carrier). The only difference in any of the worksheets is the Days(D) and Carrier(E). Each zip code is in each worksheet once, or I have a worksheet with all of the four worksheets combined. I want to create a program/search box so that the people in our office, on different computers, can type in a zip code and have the four different carriers' information show up (i.e. type 29492 into a box, hit enter and 4 boxes fill with the zip code, city name(Cainhoy), State(SC), Days and Carrier name). I don't want them to have to open the database and search it, I need to make it as user friendly as possible. Not sure if this is possible, or if Excel or Access is the best to do it in. Any help would be appreciated.

[Rabbit]

First, Excel is not a database application.
Second, I don't understand why you have 4 different sheets when you only need one sheet or table.

You can do this in either Excel or Access. I prefer Access for it's flexibility and ease of maintenance. But Excel is plausible as well for this very simple scenario, in Excel, you can just use one of the lookup functions included in Excel.

I don't understand what you mean by you don't want to open the database because whatever front end interface you build, at some point you need to open a connection to the data so you can retrieve the information you're looking for.

[zmbd]

4 worksheets, each worksheet is exactly the same,all 41,364 rows

Then you have yet another worksheet with all of this data... again?

How in the world do you maintain some 200,000 rows of data?

You should look at: A Tutorial for Access

[sperling1349]

I have four worksheets because the information came from four sources(the carriers). I combined all the information into one worksheet, manipulated and formatted it, sorted it by zip code and it works exactly like I want it to. What I mean by not wanting them to open the database is that I want a "front page" or something like it so that when they open it, the only thing they see is a box where they type a zip code and then get their information. I assumed Access would be the way to go, I know how to import the Excel into Access, I just don't know how to create the search box function that I need, or if it is possible.

[NeoPa]

Although, technically, Excel is a database application, it is certainly not RDBMS. What this really means is that it is not a good tool to use for this sort of work. It is probably possible to do (I say probably because your explanation leaves me some way short of understanding what you're actually trying to say.) but would certainly not be recommended. VLookup() would be at the heart of any Excel-based solution.

Preferable by far, would be an Access solution. That said, I have to agree with Z on advising you to get some very basic uderstanding of database work first. I mean no offense when I say that you show clear signs of being unfamiliar with some very important and basic database concepts. I would say, from experience, that embarking on such a project without first getting to grips with these concepts is likely to give you much more trouble than you need. I expect you could get there in the end, with help, but via a far less comfortable path.

[NeoPa]

I really must get into the habit of refreshing before working on a reply :-(

It makes sense to store the data in Access. A form built to filter by your Zip Code value with all the information displayed that you need, should do what you need (See Example Filtering on a Form). That you would even need to ask that though, leads me to consider you could still benefit greatly from going through the basics first (The tutorial link).

[sperling1349]

Thanks NeoPa, and no offense taken. I've taken college courses on Access and Excel and the only reason this was all done in Excel is that I am more comfortable in Excel when it comes to manipulating and formatting the data. I haven't used Access since 2000s and am very rusty. I did create a form back then for adding addresses, emails, numbers, family members, etc. into an Access database that I was keeping. I'm not a complete noob but that was more than 9 years ago. I've worked on my own to create a form that does this but have had no luck getting it to work right. It's nothing important, just something that I want to create to make life a little easier here in the office.

[zmbd]

You mean that you created the form in Excel?

If you insist on Excel, then there are a few things that can be done; however, as we'll be basically rebuilding the things that Access does natively, you'll need to have an advanced degree in VBA programming, ;) , and we'll need a lot more information about how you have things organized.

What I have now is that you have four worksheets in one workbook?
Worksheet Names?
Range names set?
We'll need the exact column header names.
Open the VBA editor, insert the form
Ideally, we'd have a drop down list or a combobox that is tied to the zipcodes within the four worksheets. That will take some doing as you'll want to avoid duplicates, othewise, we'll have to do searches against each of the worksheets.
Then we can start pulling data from the worksheets into the form by going thru each worksheet, and each row (or maybe a subfilter against the range name) to find which rows contain the information you desire.

Oh, what else, I'll go dig that ancent asset tracking workbook out of the archives as I had something like this about 10 years back... then I move it to access.

Personally, IMHO, better that you review the Database link. As you've stated, you're a tad rusty with using a DB.

In the database, depending on the data you have, I would have a table with the zipcodes, a table with carrier information, and possibly a table with cities (as some cities have multiple zipcodes) and then a table to relate all of these.

You then would have a form that you can pull all of the information via zipcode-filtering on that related table. This is in effect what you'll need in the Excel too; however, it's much more difficult.

[NeoPa]

I think 9 years away would explain the impression I picked up. On the other hand having taken some courses probably gives you a better starting position than many.

The form you mention - Is that in Excel or Access? Although not so well known, Excel has forms too.

The link I included in post #6 should help if you go the Access route. It would help us all to know if you have a strong preference for Excel though, for whatever reason. The two approaches have many similarities, but equally many fundamental differences.

[sperling1349]

The form I built was in Access.

I only used Excel for this project because the data was sent to me in Excel and it was easier to manipulate the data in Excel (at least for me). I planned on building the form in Access since I assumed Excel wouldn't be the right program to do forms in. I just wasn't sure how easy it would be to do a form in Access that would return 4 different results.

All of my information is in one worksheet:

A B C D E
Zip City State Days Carrier

Every Zip, even if the city has multiple zips, will only be in the database 4 times, one time for each of four carriers. The only two data that changes for each zip is the Days and Carrier, so in the form I want to create I'll type in a zip code and the 4 instances of that zip code are what I want displayed in the form:

Type 50380 in a box and get this returned:

Des Moines, IA 4 ODFL
Des Moines, IA 4 RRTS
Des Moines, IA 5 Redd
Des Moines, IA 5 YRC

I am a lot better with Excel than Access and only use Excel to manipulate the data. I want to use Access because I know it's a really good program and does what I need. I appreciate you replying and helping me out. I will use your link and learn all I can.

[NeoPa]

Sperling:
I will use your link and learn all I can.

Very good idea, as it sounds like your requirement is a perfect match for what's in there.

If you get stuck just post back in here explaining where you're up to and we can help further :-)

[sperling1349]

I am doing all of this at work and I am working on an XP computer without Access now. Figures. So here's what I've done:
I used a Vlookup function and modified it over four cells -
Type a Zip in B2 and cells C2, D2, E2 and F2 populate with the correct data the way I want. I've typed in a lot of different zips and it works perfectly.

My question now is: how do I get the Vlookup function to return the next 3 lines of data? I've been reading about array functions, is that my only option?

I don't have Access on this computer because of the version the boss bought, I was using a trial version of 2010 Professional but that expired on the 28th.

[Rabbit]

Use the Match function to return the relative row number of the matching value and couple it with the Index function to return the value at a row, column intersection.

[sperling1349]

I have Index and Match working, but running into the same problem as Vlookup, it returns the first value. I know I am on the right track, just need to get it to return the next three instances of that zip code. Here is the code that is working:
=INDEX(All!B2:B165453,MATCH(B3,All!A2:A165453,0)). All is obviously the name of the sheet, column A has all the zips that I am searching. I have it set up so that it returns the correct info, tried it many times, works great. Problem is that it found the first instance of that zip, but right underneath that zip the same zip is in the spreadsheet 3 more times and I need to return those three also. How do I accomplish that?

I am enjoying this a lot, thanks for all the replies.

[NeoPa]

Me <== confused!

I thought you just explained you were looking to continue in Access using form filtering. Did I miss you saying you weren't planning to after all?

[Rabbit]

Sorted by zip code right? Just add 1/2/3 to the row returned by the function.

@Neo, in post #12, they said they're on a new environment that doesn't have Access.

[NeoPa]

Thanks Rabbit. I interpreted that to mean they were temporarily at the wrong type of computer (IE. One without Access), rather than fundamentally without access to Access (as it were). Anyway, that seems to explain my confusion :-)

[sperling1349]

Rabbit, thanks! That worked, for a while. The problem I ran into was with my formula - I had the zip in B2 and in C2 I had the formula that returned the correct city based off the zip, D2, E2 returned the rest of my info based off the previous cell (C2 off B2, D2 off C2, etc.) This worked until there was more than one city name that the zip returned (since D2 was based off the City in C2 it found the first instance of that city, and then E2 returned off D2). I'm having fun doing this so I will figure this out, with your guys' help of course.

Question is (I think) How do I do a index/match formula that can return the next adjacent cell, and also the next row down? Is that possible? This is the formula that returns the next cell over and returns the correct info

=INDEX(All!E2:E165453,MATCH(B2,All!A2:A165453,0)+4 )

How do I add to that so that I can return the next row down? I want to be able to have cell C3 return the next instance of the correct city based off the zip input in B2, and then D3, E3, F3 return the next instance down from the the previous cells. I hope this makes sense, I'm almost confused!

Row 2 does exactly what I want: zip is typed in B, C returns city, D returns state, E days, F carrier. In row 3 I want to return the next instance of that zip from B2(sheet is sorted by zip so the next line down has the next instance). Is there a way to do that, along with moving to the right cell by cell?

Thanks for all the help.

[Rabbit]

To get the next row down, add one to the row returned by match.

index(range of data, match() + 1)

To get the next column, use the optional column parameter.

index(range of data, match(), 2)

[sperling1349]

Can I use those together in the same function? (match()+1,2)?

[NeoPa]

Yes indeed. They are merely offset values for the row and the column respectively. The resultant values in there determine how far to go up/down and left/right. Don't omit the [range of data] parameter though ;-)