Jump to content
Sign in to follow this  
Cres

Excel help

Recommended Posts

So I'm trying to put together something for work.

What I'm looking to do is having a primary page with two drop down menus. Each contain a list of items, let's say, A B C.

Then on separate, hidden tabs, a list of items referred to by A, B and C. Let's say 1,2,3,4,5.

The goal being that a user selects two items, a "from" and a "to", and that the excel worksheet spits back the items contained that were gained or lost by the change.

 

Example: A includes 1,2,3. B includes 3,4,5. C includes 1,3,5.

 

User selects "A" in the "from" dropdown menu, and "C" from the "to" dropdown menu.

Page displays +5, -2.

 

How could I achieve that?

Share this post


Link to post
Share on other sites

So I'm trying to put together something for work.

What I'm looking to do is having a primary page with two drop down menus. Each contain a list of items, let's say, A B C.

Then on separate, hidden tabs, a list of items referred to by A, B and C. Let's say 1,2,3,4,5.

The goal being that a user selects two items, a "from" and a "to", and that the excel worksheet spits back the items contained that were gained or lost by the change.

 

Example: A includes 1,2,3. B includes 3,4,5. C includes 1,3,5.

 

User selects "A" in the "from" dropdown menu, and "C" from the "to" dropdown menu.

Page displays +5, -2.

 

How could I achieve that?

Hot damn! An excel question that I don't know the answer to. You're looking at three different number sets, and when a user chooses two number sets, the excluded numbers are displayed? This doesn't sound like an excel issue, but a program for something like R.

Share this post


Link to post
Share on other sites

Well, this doesn't actually need to occur in Excel, but it's the only program I could think of that I could use to do this. I can't actually code anything and it's got to be usable by any random moron, that's why I figured Excel would work.

Share this post


Link to post
Share on other sites

Well, an alternative is to use a count system followed by a logic test such as this:

http://answers.yahoo.com/question/index?qid=20090928122012AAyCebT

 

For example... two number sets, column B and C

B C

1 1

2 2

3 4

 

To determine whether the numbers 1,2,3,4, or 5 are in either set

=COUNTIF(B$1:B$3, 1) =COUNTIF(C$1:C$3, 1)

=COUNTIF(B$1:B$3, 2) =COUNTIF(C$1:C$3, 2)

=COUNTIF(B$1:B$3, 3) =COUNTIF(C$1:C$3, 3)

=COUNTIF(B$1:B$3, 4) =COUNTIF(C$1:C$3, 4)

=COUNTIF(B$1:B$3, 5) =COUNTIF(C$1:C$3, 5)

 

To determine overlap as well as which numbers over lap

 

=IF(B6*C6=1,1,0)*IF(B6+C6=0,0,1) =IF(C6*B6=1,0,-1)*IF(B6+C6=0,0,1)

=IF(B7*C7=1,2,0)*IF(B7+C7=0,0,1) =IF(C7*B7=1,0,-2)*IF(B7+C7=0,0,1)

=IF(B8*C8=1,3,0)*IF(B8+C8=0,0,1) =IF(C8*B8=1,0,-3)*IF(B8+C8=0,0,1)

=IF(B9*C9=1,4,0)*IF(B9+C9=0,0,1) =IF(C9*B9=1,0,-4)*IF(B9+C9=0,0,1)

=IF(B10*C10=1,5,0)*IF(B10+C10=0,0,1) =IF(C10*B10=1,0,-5)*IF(B10+C10=0,0,1)

Your readout should look something like this

 

1 0

2 0

0 -3

0 -4

0 0

The first column includes the common numbers between the two, whereas the second column includes the differences.

 

I'm too sure how you would put that into a button format using excel.

Share this post


Link to post
Share on other sites

Example: A includes 1,2,3. B includes 3,4,5. C includes 1,3,5.

 

User selects "A" in the "from" dropdown menu, and "C" from the "to" dropdown menu.

Page displays +5, -2.

 

What operation are you trying to perform? I'm not quite sure how/why the result of from "C" to "A" yields +5,-2.

Share this post


Link to post
Share on other sites
=IF(B12=1,1," ")&" "&IF(B13=2, 2, " ")&" "&IF(B14=3, 3, " ")&" "&IF(B15=4,4, " ")&" "&IF(B16=5,5, " ")

=IF(C12=-1,-1," ")&" "&IF(C13=-2, -2, " ")&" "&IF(C14=-3, -3, " ")&" "&IF(C15=-4,-4, " ")&" "&IF(C16=-5,-5, " ")

These equations would give you a more manageable readout. I'm thinking that you want to use the "Control Tool" to make buttons that would run a macro, but I have no experience with it.

1 2

-3 -4

Share this post


Link to post
Share on other sites

In the interest of speed, I'd think that for a reasonably finite number of columns (button values), you'd be better off using a macro to pre-calculate the exclusive values and put them into a third hidden sheet. This would be particularly true for a small number of columns which large numbers of values in each.

 

This would also make the lookup function trivial.

Share this post


Link to post
Share on other sites

I don't understand what you just said at all, but I would say there are about 30 columns of 150-300 items each.

 

What operation are you trying to perform? I'm not quite sure how/why the result of from "C" to "A" yields +5,-2.

 

First, it's from A to C, and what it says is, you now have "5", but you've lost "2".

 

Just to be clear, none of the items in the lists are actually numbers. I just used that to demonstrate an example.

Share this post


Link to post
Share on other sites

Well, that puts a lookup table right out.

 

You're going to have to use a macro to do this, I think. Loop through the A elements with COUNTIF(C:C,ELEMENT_A)=0 to find all of the things specific to A, and loop through the C elements with COUNTIF(A:A,ELEMENT_C)=0 to find all of the things specific to C.

 

I don't know of any way to do this with a formula alone. It's a pretty easy macro to write, however.

Share this post


Link to post
Share on other sites

You could do it without macros using the IF and INDIRECT functions, but it would look like a mess visually. If I remember my schooling correctly, I believe what you want can easily be accomplished in Microsoft Access, but I would have to look up stuff to remember how to do it.

Share this post


Link to post
Share on other sites

Thing is that he wants words in there. Not numbers.

It's still doable.

COUNTIF works on strings. You'd have to be careful about formatting, however.

 

And yes, this would be really straightforward to do with SQL in Access. SELECT COL_A WHERE COL_A NOT IN COL_B, etc. Even with a horrible-looking nested INDIRECT function, I don't know that you could implement it in excel without using a macro. Could be wrong though, I try to avoid excel for anything more then adding column A to column B.

 

Plus nobody uses Access because it's a horribly castrated "SQL" "database".

Share this post


Link to post
Share on other sites

Access would definitely make this an easier option, though I'm far from an expert. As for Excel, it's doable. You could dumb it down and create lists with all possible answers and depending on whats in CHOICE1 and CHOICE2, you get it to show the relevant answer.

Share this post


Link to post
Share on other sites

Access would definitely make this an easier option, though I'm far from an expert. As for Excel, it's doable. You could dumb it down and create lists with all possible answers and depending on whats in CHOICE1 and CHOICE2, you get it to show the relevant answer.

Right; that was my earlier suggestion, but with 30 columns that ends up being a 30!/(28!*2!) set of interaction columns. If it was 5 columns with 100,000 elements in each that'd definitely be the way to go.

Share this post


Link to post
Share on other sites

Although I started off in Excel, learned VBA macros, graduated to Access blah blah at the beginning of my career, and I still think Excel is a reasonable starting point, there really is no reason not to solve this via HTML and work your way up to php/mysql.

 

You can use excel to generate HTML/js and then do this very trivially in javascript with a couple dropdowns. I can build a prototype in a couple minutes later for you, if I don't forget.

Share this post


Link to post
Share on other sites

Well the idea is that this tool would be available to whoever wants to use it within the workspace. If it's manipulated through a web browser, for instance, it really shouldn't be hosted externally. And I wouldn't be able to expect it to be hosted internally either, not at first. This is why I thought excel -- I can just pass the file around by e-mail.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×