Excel

[Idoit40fans]

I have a list of about 900 accounts that need to be divided up amongst myself and 10 other people. The excel document has them listed along with the zip code they are listed in. Each of us has like 30 or so zips that we cover. Is there a code I can write that will look at the zip code column and then return someone's name based on which zip code the account is under? I really know it can be done, not sure how though. It will take a long time to sort through who these need to be assigned to. This is gonna happen like 5 more times in the near future so being able to run a code would be very helpful. Does anyone know the base format for doing this?

[thehockeyguru]

I have a list of about 900 accounts that need to be divided up amongst myself and 10 other people. The excel document has them listed along with the zip code they are listed in. Is there a code I can write that will look at the zip code column and then return someone's name based on which zip code the account is under? I really know it can be done, not sure how though. It will take a long time to sort through who these need to be assigned to.

V-Lookup

[AlexPKeaton]

Export to .csv then import it into Python haha.

[stinky]

VLOOKUP would be correct and learn to use it and its tricks, it is in the top 5 of tools in my world.

[mac5155]

yep VLOOKUP. my world was changed forever once I discovered how to use it.

[ulf]

Hey I think VLOOKUP will work

[count2infinity]

did you try WLOOKUP yet?

[PensFanInDC]

You can select the zip code column and sort it.

[JeffDFD]

if you don't know vlookup, you can use nested if statements.

basically it would be IF 'cell reference where zip code is' = 'the zip code Bob covers' THEN, 'what you want if it is true (probably bob's name)' ELSE 'this is where the what you want if it is false goes but you just start a new if statement with the next persons zip code and name.

[shmenguin]

I just load everything into SQL Server and query away

/supernerd

[Kraftster]

Please help. I am preparing cap sheets for my fantasy hockey league and I am running into a problem.

This is what the spreadsheet looks like:

Spoiler:

In all of the years to the right of 2012-2013, I have the following: =IF((C9=" ")," ",IF((C9=1)," ",IF((C9>1),(C9-1))))

As you can see, the total cap hit for years after this year is not calculating. I'd rather not display a 0 in all the cells where this is no contract, but is that the only way to avoid this?? I assume it is having a problem adding the years in future years because of the blank cells?

Any suggestions?

[Letang Is The Truth]

What is your formula in the total cell?

[Letang Is The Truth]

Do sumif and make your criteria >0

[Kraftster]

I stole that IF formula from Mr Colby's baseball cap sheet, so I don't know what any of what you said means. My total cell is just a simple sum w/ data range.

[Rylan]

I stole that IF formula from Mr Colby's baseball cap sheet, so I don't know what any of what you said means. My total cell is just a simple sum w/ data range.

It means it will add any cells with numbers > 0.

[Letang Is The Truth]

I stole that IF formula from Mr Colby's baseball cap sheet, so I don't know what any of what you said means. My total cell is just a simple sum w/ data range.

Do the sumif

[Kraftster]

How is it written? I need to add X5:X25 and subtract X28.

[canaan]

sure is dweeby in here

[King Sid the Great 87]

How is it written? I need to add X5:X25 and subtract X28.

=Sum(X5:X25)-X28

If all you are doing is summing a column, SUMIF > 0 does the same thing as SUM, assuming you have no negative values

[Kraftster]

That is what I have now. I think I need to introduce this whole if >0 concept but I can't seem to write it correctly.

[King Sid the Great 87]

=SUMIF(X5:X25,">0")-X28

[Kraftster]

Working now, thanks.

[Mr. Colby]

Alright this is very advanced Excel, if anyone can help me this I will be infinitely impressed. Can't find the answer anywhere on google.

On my "chart/relevant data" sheet I have 3 drop-down lists (Project, Month, Impact) that refer to columns in my raw data table.

What I want to do is pull the relevant data to the "chart/relevant data" sheet, but filter based on the selections made in the 3 drop downs. So that is to say, I want to only populate the data on that sheet that is relevant to the 3 drop downs (i.e. meets all 3 criteria).

I found one way to do this: I created a hidden sheet that populates based on Project choice, then a 2nd hidden sheet that pulls the list from the first hidden sheet and filters down based on Month choice, then I can populate the sheet of interest using an extensive IF formula based on the 2nd hidden sheet as well as the Impact choice (pulling the twice-filtered data in to the main sheet and filtering on the Impact choice).

My question: Is there VBA to write that allows this to be done via a Macro? My problem is that with the 2 hidden sheets and the extensive amounts of data I have, it makes the sheet too big and slow.

The reason I want to do this is so I don't have to select from the 3 drop-downs and then manually filter 3 times, and re-filter every single time I change my drop-down selection.

HELP!

[Juice]

I bet if we did a Vcardlookup of the posters in this thread true would get returned more than false boosh suck it nerds

[columbia]

I bet if we did a Vcardlookup of the posters in this thread true would get returned more than false boosh suck it nerds

This is why I load data into MySQL and just write queries.
Macros and Excel are for wusses.