Organizing and simplifying a table

**sasdas** · 08-13-2013, 10:55 AM

Hi, so I made a table for a shipping company, but the end result was a bit to large for there tastes, so im wondering if there is any way to better organize this data, one of the problems I had with it is that
they have a port of loading, a city, and then different crate sizes, which are the 20' and the 3 40's, but to make the table smaller, some of the columns have the 20' and 40's together, and some columns do not even apply to some of the rows.
What im hoping to do is create a sort of search where they can look up by port of loading, or destination, or coast, and then pick if they want a 20' or 40's crate size, and then the relevant information for those will appear, so that way they can get a sort of quote or estimate of the price and makes things much more organized. I dont know if im making much sense, really new to excel and was asked by a friend who works there.
Thank you for any help and assistance offered.
By the way, I had already made a topic, but after reading the moderators post, decided to just make a new one.

Here is a sample:
I know that some columns are actually the same thing, as such they will be combined, but even with that its still to big.
Sample work.xlsx Sample work.xlsx

**zumbalj** · 08-13-2013, 02:30 PM

What is this sheet's intended use? Does all this information have to be included? Did the company the sheet is created for clarify what their expectation for the sheet is?

**sasdas** · 08-14-2013, 10:08 AM

Ok, so this sheet will be used sort of like a quote, for example they want to find the cost of shipping from this port to this location with a specific carrier and specific crate size. Some of the information, the columns are in the process of being removed. What they would like is to make the information much more faster to find, as they could currently filter information, like only a specific carrier and specific shipping port and destination, but in the end, they have to scroll to the right and calculate the price.
So instead they can pick like I want shipment from this port, carrier, or destination, and then when they pick the crate size, it will show all the relevant information for that.

**zumbalj** · 08-14-2013, 10:20 AM

Maybe leave this table as is (may have to add a few columns for functionality), but create a dashboard that allows that info to be input and use VLOOKUPS to output just the info you want.

**sasdas** · 08-14-2013, 10:25 AM

Can you create an example excel for me? Im very much a newbie in this and viewing it helps me out.

**zumbalj** · 08-14-2013, 10:28 AM

You learn more if you do it yourself. Start out by making another sheet with places to input all the variables they'll need to enter and places for all the variables you'll want to output. From there, enter some sample input data and take a stab at some formulas for the outputs. When you get stuck, upload the workbook again and we'll go from there.

**sasdas** · 08-14-2013, 10:30 AM

Sample work.xlsxOk, thank you. So I started looking into the formulas and such, I found a tutorial from this website http://spreadsheets.about.com/od/loo...s-tutorial.htm

Anyways, I used it for the table but it involves typing in each info and being exact, Im uploading the sample excel again, Im wondering if theres a way to make a drop down list instead of typing each information.
The formula is at the bottom.

**sasdas** · 08-14-2013, 05:12 PM

Sorry for double posting, cant for some reason, anyways, I wanted to know, what if I could hide the columns that have a 0 value automatically, So ill have the data in like worksheet 2, then in worksheet 1 I would have all the columns again, and have a formula, and then when I search for a specific criteria, and one of the columns value is 0 or blank, it will hide the column and compact all the information better.

**zumbalj** · 08-15-2013, 07:23 AM

1. Yes, it's possible using VBA.

2. That would probably be a lot more time consuming than it's worth.

3. It may causae the spreadsheet to run slower.

4. It would be much harder to update - you now have two places to update, one of which may be hidden.

Just saw your first post... you can use Data Validation to allow entry into certain cells only from a list of your choosing.

**sasdas** · 08-15-2013, 10:53 AM

Ok, so ive been told they want to use this as a sort of quote, they input the information they want, the port of loading, city, carrier, and crate size, and then the information they want will appear and they can use it as a quote so the vendor can know the price easier.

**zumbalj** · 08-15-2013, 11:22 AM

Like I said before, start with the inputs and outputs without worrying about how they get there. Just lay out the sheet with cells for all the information that you'll need to enter and cells for all the information you want to get out. From there, the tutorial you cited looks like a good place to start.

**sasdas** · 08-20-2013, 11:32 AM

Hi, so im still working on this, so I had an issue since I had duplicates in the table, so I made a pivot table in sheet 2 to use it in a drop down list since it wont show duplicates, the idea would be three drop down list, but I realized that they would need to be dependent on each other, to sort of filter out the next drop down list, but unfortunately it wont be able to filter correctly since it needs the info from the data, Im not sure im making sense, but heres my sample.