Advanced Excel puzzle / problem

[xix_xix]

Hello everyone

My first post here (my knowledge is not exactly anything to shout about and not sure how much assistance I can be to others) but I do have a rather confusing problem that my brain tells me there is a solution to, I just for the life of me can not think of an answer? - Really hope someone out here is a wiz and has a solution.

Also sorry if I am posting this in the wrong section

----

Problem

I will try to explain as best I can - It is using Excel, I am using version 14.0.6112.5000 (32-bit) if that means anything to anyone

I have a list of figures in column A

I am trying to write an excel formula that will check what figure is in the relevant cell in column A and depending on the figure, will display a different result.
(hope that makes sense)

The figures in column A vary depending on a multitude of other factors, so the formula needs to be fairly adaptable to cover a fairly wide number of possibilities.

Example:

Cell A1:A50 have the variable figures (approximately from numbers 1-10)

Cells B1:B50 have names (these are fixed names)

In C1:C50+ will be displayed the Name depending on the number displayed in column A

So if A1 has 3 and B1 has John - Cells C1:C3 will display John

If A1 has 2 and B1 has John - Cells C1:C2 will display John

In addition, if A1 has 3 and B1 has John
A2 has 2 and B2 has Chris
A3 has 4 and B3 has James

C1:C9 would look like:
John
John
John
Chris
Chris
James
James
James
James

and so on

in my head it seems like it would work using lots of IF / AND / OR functions, but I cant get my head around how it should look?

Really hope someone can help me?

[DaveLembke]

Excel does support IF THEN ELSE logic.

http://wiki.answers.com/Q/How_to_write_an_If_then_else_statements_in_Excel

I was messing around with it to make a schedule application in which I had employees who were qualified on specific machinery to service, and when someone called in sick or was taking scheduled leave I wanted to make an excel spreadsheet that I would simply plug in the schedule at the top of the spreadsheet for that week , and down below the logic would assign technicians to each of the machines that required PM maintenance. However the complexity of having 70 employees to account for and having to set all the logic the idea that I had to make it much simpler to schedule was now a full blown project and the logic of IF, THEN, and ELSE was getting really complicated when you have people of varying qualifications where one guy who was qualified on one machine, but only a partial of another, now you needed to add extra logic rules that apply to the tasks of the partial of that machine etc.

Long story short. I ended up throwing the towel in on the Excel idea to use logic because even though I started small with it to handle manageable pieces of it such as to only set the logic for one specific machine and all employees who were qualified to work on that machine, the logic instructions I entered into Excel 2003 looked to me to be correct, but it would work but then not work, and when it didnt work with a specific combination the reasoning behind the malfunction was not standing out. Since I know how to program in C++ I ended up programming it up in that and imported the schedule as a CSV file to import to my C++ program that was comma delimited, and then it would display the results and I could print or save the results and that worked out much better and was correct. It also imported a CSV file of employees qualifications so it used that to pair up the correct employee to machinery that they are qualified for. I also was able to expand on this program and assign work hours to have rules so that a machine that required only a 3.4 hour route, this tech would show as available to take on 4.1 hours of additional duties in the 7.5 hour day with 2 x paid 15 minute breaks in the 8 hour day. So I then was able to assign multiple machines automatically and load balance with the logic vs one person getting tons of work and another getting an easier day to keep it fair. The employees were very happy that this program was implemented since it keep everything fair and the mismanagement problem was now gone since the supervisor was able to run this program and it spit out what everyone should be doing since it was too much for this supervisor to handle themselves on a daily basis.

*** I probably could have fixed the logic problems in my spreadsheet, but due to the frustration with the logic acting up and the cause not very clear since the logic when looked over looked correct. And I had to produce something that worked for the supervisor. I ended up switching to C++ instead and was able to hammer it out pretty quickly and when looking at the source code I knew exactly what was happening and why vs why is this logic in excel not working and searching google for anyone else with similar problems with Excel 2003 and what I was trying to accomplish.

Can you share copy/pastes of the logic instructions you are using that are malfunctioning for us to look over? Maybe there is a simple problem to point out or a better method to use for rules.

From my experience with Excel and logic like this, its best to start as small as possible and get that portion to work and then move on adding more and more logic rules, so that when a problem occurs your looking at the newly introduced logic with the prior logic and its slighly easier to manage problems. Maybe your logic wont run into the same problems I had, but I had a list of logic that was possibly exceeding the limits of Excel with single line long form logic in each of the logic cells, and then based on the output from those cells, another cell with other logic instructions were then happening and the malfunctions I had may have been pushing excel beyond what it was capable of with logic as well as with logic with dependencies on states of other calculations I was almost wondering if the malfunction was because it was getting the buggy before the horse vs the horse before the buggy which would cause for malfunction of the logic rules that looked fine in excel, but excel was running with it incorrectly to that of what the intent was.

[Geek-9pm]

Also, VBA can be used for very complex things.
http://msdn.microsoft.com/en-us/library/ee814737%28v=office.14%29.aspx
http://www.excel-easy.com/vba.html
http://www.youtube.com/watch?v=t1vdt0FddsE

[soybean]

... I cant get my head around how it should look?

I'll admit I may be missing the something but I can't get my head around the requirements and the logic as stated by you.  Perhaps you need to re-think how this should work. 

[xix_xix]

Hi all

Sorry I have not replied in a while

I have been working away on it and I think I found a solution (although a very long winded one)

Had several issues with it (under statement)

Problem 1 - How to only show unique prizes won and not total prizes and then display the names the number of unique prizes not total prizes
Answer - Was easier than I thought after thinking about it really. On sheet one of my workbook where I had all the individual tables for finishing positions I added a column shown below in column BK to count cells containing text, which basically counts unique prizes.

Then the subtotal of this count was inserted onto the main worksheet showing the total unique prizes per person shown in Column E

Using the same formula that was given to me by a user on another forum, I ran his formula from this new column instead of the total count which resulted in names being displayed the number of times relative to unique prizes won and not total (Thank you shg for setting me on the right path!) (formula's shown below)


Problem 2 - How to get the prize ID's and number of prizes won to show in columns M2:N500
Answer - This was insane the more I thought about it, I naively thought I could do it all from the variable table on the main worksheet, but of course this would not work as when the figures changed in the variable table, so then would the prize ID's and number of prizes also change.

So.... I had to go the very very long way around of creating replicas of the prize ID's and number of prizes won in a descending table as shown below


From this set of information, on the main worksheet I had intended to use the simple enough formula to again replicate it to allow me to copy and paste as values on a blank worksheet, then highlighting and deleting the blank rows, but of course the rows were not blank even if they looked so (stupid excel)

I was stuck on this for ages, probably because my head hurt - but then I simply added to the formula to show text (the letter t) if a cell contained nothing as shown below:

Now with cells all containing something visible, I am able to now copy and paste as values these columns, then push f5, select special, then 'constants' and untick everything other than text!

That way I can delete all the rows containing text and shift the rows up so all the Prize ID's and number of each prize are in order.

Then I simply copy paste this into the main workbook along side the names of the prize winners and tada!!!

Magic



Worksheet 1 part 1
Only variable shown is the Buy-in amount and the number of entrants - Prizes are calculated automatically depending on these figures


Worksheet 1 part 2
These individual tables are the Prize ID calculations depending on the size of overall prize won

Formula in Cell BB5:BB15 is - =IF(BA>=1,$S$2-(AZ5*BC5),$S$2) - a basic formula to look up overall prize remaining and deduct prize ID amount if remaining overall prize is enough, then move on down the smaller amounts.

Cells BD16 / BJ16 etc were important to display prize winners the number of times on worksheet 2



Worksheet 2
This is where prize winners and their prizes won are displayed


Cells B:2:B500 are variable and manually copy pasted in from competition results
Depending on the competition buy in and number of entrants selected on worksheet 1, the prizes for these players is automatically displayed in cells C2:F500
Column E is the unique prizes taken from worksheet one (shown above)

Formula in cell F2 is - =SUM(E1, F1) and then dragged down

The table in I1:L12 is for review purposes only and just checking everything matches up with prize allocated to player in Column C
Formula in J2:K12 is very!! long winded but does the job - Cells in formula obviously adjusted for each different cell - very time consuming as could not drag formula

=IF($H$2=1,DATA!BB18,IF($H$2=2,DATA!BH18,IF($H$2=3,DATA!BN18,IF($H$2=4,DATA!BT18,IF($H$2=5,DATA!BZ18,IF($H$2=6,DATA!CF18,IF($H$2=7,DATA!CL18,IF($H$2=8,DATA!CR18,IF($H$2=9,DATA!CX18,IF($H$2=10,DATA!DD18,IF($H$2=11,DATA!DJ18,IF($H$2=12,DATA!DP18,IF($H$2=13,DATA!DV18,IF($H$2=14,DATA!EB18,IF($H$2=15,DATA!EH18,IF($H$2=16,DATA!EN18,IF($H$2=17,DATA!ET18,IF($H$2=18,DATA!EZ18,IF($H$2=19,DATA!FF18,IF($H$2=20,DATA!FL18,IF($H$2=21,DATA!FR18,IF($H$2=22,DATA!FX18,IF($H$2=23,DATA!GD18,IF($H$2=24,DATA!GJ18,IF($H$2=25,DATA!GP18,IF($H$2=26,DATA!GV18,IF($H$2=27,DATA!HB18,IF($H$2=28,DATA!HH18,IF($H$2=29,DATA!HN18,IF($H$2=30,DATA!HT18,IF($H$2=31,DATA!HZ18,IF($H$2=32,DATA!IF18,IF($H$2=33,DATA!IL18,IF($H$2=34,DATA!IR18,IF($H$2=35,DATA!IX18,IF($H$2=36,DATA!JD18,IF($H$2=37,DATA!JJ18,IF($H$2=38,DATA!JP18,IF($H$2=39,DATA!JV18,IF($H$2=40,DATA!KB18,IF($H$2=41,DATA!KH18,IF($H$2=42,DATA!KN18,IF($H$2=43,DATA!KT18,IF($H$2=44,DATA!KZ18,IF($H$2=45,DATA!LF18,IF($H$2=46,DATA!LL18,IF($H$2=47,DATA!LR18,IF($H$2=48,DATA!LX18,IF($H$2=49,DATA!MD18,IF($H$2=50,DATA!MJ18,0))))))))))))))))))))))))))))))))))))))))))))))))))

In Cells M1:N500 the formula is - =IF(DATA!MN2=0,"t",IF(DATA!MN2="","t",DATA!MN2))
Basically a look up on worksheet 1 where all the individual tables for prize winners is put into one long table and shows everything for all the prizes won (or not won)
(The reason there is t in some cells is explained below)

As you can see, in Column P, the number of names of each person is displayed the number of unique prizes they have won.

Whew - Anyone else exhausted yet?!

The biggest issue I was having was with columns Q and R - how to find a way to display the prizes that each person had won.

The way I got around it was with correlating all the individual prize tables into one long column and the results displayed in columns M and N

I had planned to simply copy paste these columns and then highlight blank cells and delete, shifting cells up - but of course even though the cells looked blank, they were not (the wonders of excel!)

I was driving myself mad, but solution was simple enough - as in the formula above that was used - =IF(DATA!MN2=0,"t",IF(DATA!MN2="","t",DATA!MN2)) - adding "t" to any blank cells meant I could / can now copy paste (as values) and then push f5, select special, select constants, then untick everything other than text which would allow me to delete every cell that contained a "t" - shifting cells up and BOOM! prize ID's and the number of prizes was now in line with Column P and the names of the prize winners.

Phew!!

It is not exactly pretty, it was not easy but it works

[DaveLembke]

=IF($H$2=1,DATA!BB18,IF($H$2=2,DATA!BH18,IF($H$2=3,DATA!BN18,IF($H$2=4,DATA!BT18,IF($H$2=5,DATA!BZ18,IF($H$2=6,DATA!CF18,IF($H$2=7,DATA!CL18,IF($H$2=8,DATA!CR18,IF($H$2=9,DATA!CX18,IF($H$2=10,DATA!DD18,IF($H$2=11,DATA!DJ18,IF($H$2=12,DATA!DP18,IF($H$2=13,DATA!DV18,IF($H$2=14,DATA!EB18,IF($H$2=15,DATA!EH18,IF($H$2=16,DATA!EN18,IF($H$2=17,DATA!ET18,IF($H$2=18,DATA!EZ18,IF($H$2=19,DATA!FF18,IF($H$2=20,DATA!FL18,IF($H$2=21,DATA!FR18,IF($H$2=22,DATA!FX18,IF($H$2=23,DATA!GD18,IF($H$2=24,DATA!GJ18,IF($H$2=25,DATA!GP18,IF($H$2=26,DATA!GV18,IF($H$2=27,DATA!HB18,IF($H$2=28,DATA!HH18,IF($H$2=29,DATA!HN18,IF($H$2=30,DATA!HT18,IF($H$2=31,DATA!HZ18,IF($H$2=32,DATA!IF18,IF($H$2=33,DATA!IL18,IF($H$2=34,DATA!IR18,IF($H$2=35,DATA!IX18,IF($H$2=36,DATA!JD18,IF($H$2=37,DATA!JJ18,IF($H$2=38,DATA!JP18,IF($H$2=39,DATA!JV18,IF($H$2=40,DATA!KB18,IF($H$2=41,DATA!KH18,IF($H$2=42,DATA!KN18,IF($H$2=43,DATA!KT18,IF($H$2=44,DATA!KZ18,IF($H$2=45,DATA!LF18,IF($H$2=46,DATA!LL18,IF($H$2=47,DATA!LR18,IF($H$2=48,DATA!LX18,IF($H$2=49,DATA!MD18,IF($H$2=50,DATA!MJ18,0))))))))))))))))))))))))))))))))))))))))))))))))))

Whew - Anyone else exhausted yet?!

YUP... This is the madness I was going through to get mine to work and the above logic x about 200 or so formula's of lengths like this. One little typo and your digging for a while to find that needle in the haystack that is the problem. Frustrating for me was that it was sometimes working while other times malfunctioning. I decided to pull the plug on it and just go the route of importing data from CSV file to C++ program instead.

Glad you got yours to work. 

Be sure to save multiple copies of it on a backup media etc as for this is something you will likely never want to have to recreate from scratch ever again    ... Trust me