Next question from me. I have a list of up to nine different spellings for each of a few hundred names, with the different spellings in columns A through I, the names in rows. There're quite a few repetitions and quite a few blanks because of how I got those data in the first place.



Then I have a sheet (called List) which has a spelling of many of these names (in A:A), and a number next to each (in B:B). I want to VLOOKUP the number in List, so I need to know which spelling it's using. I'm doing this by having nine more columns (K to T) containing:

etc. Then to see which one has the right spelling, I'm doing:

In an ideal world, I wouldn't have columns K to T at all, I'd put it all in one formula. I was hoping I could do something like:

And so on. But LARGE only accepts one value, and it wouldn't have worked with MATCH anyway. Any ideas? Some way to have a temporary array in memory or similar? (Concatenate?)

Can't you use an array function? Ie, LARGE(COUNTIF($A:$A,A2:K2),1).

Instead of using MATCH you can also use an IF statement, something like LARGE(IF(COUNTIF($A:$A,A2:K2)>0,COLUMN(A2:K2),0),1 ), which should return the numeric value of the column contianing the value you want.

You guys are the ****ing best

I'm trying to combine IF, AND and OR statements in a function. Firstly, I want to search for any instance of text within a cell. If this text is present, I want it to also search for a list of other words within that cell.

Example:

1 cat
2 cats and dogs
3 cat, terrier
4 dog
5 cat, pug

I want my statement to find any instance of cat and then also check for terrier and pug. I've combined the following statements to search for cat/pug successfully:

=IF(SEARCH("cat",B2)*OR(SEARCH("pug",B2)),"Dog & Cat","")

But when I add an additional SEARCH into the OR, it breaks for both.

=IF(SEARCH("cat",B3)*OR(SEARCH("pug",B3),SEARCH("t errier",B3)),"Dog & Cat","")

I'm sure I need to nest them and I'm just f'ing it up. There's also probably an easier way to add multiple secondary queries than repeating a SEARCH, but I'm not that advanced yet.

Here's what I tried. I seem to have the "cat" query within AND and the secondary within OR, but it won't work unless an entire cell contains cat, pug and terrier.

=IF(AND(OR(SEARCH("pug",B3),SEARCH("terrier",B3)), SEARCH("cat",B3)),"cat-dog","")

I'm sure there's better ways to do this but this works:

=IF(IFERROR(SEARCH("cat",B3),0)*OR(IFERROR(SEARCH( "pug",B3),0),IFERROR(SEARCH("terrier",B3),0)), "Dog & Cat","")

When it is evaluating to an error inside the OR, then it messes up and that's why you're comping up with your problems. Iferror fixes that, although makes the function a bit hard to understand (esp if someone else is looking at it).

Edit: 'Evaluate Formula' is going to be your best friend when learning to write complicated excel functions since you can know exactly where the function is evaluating incorrectly.

Thanks, aditya. I'll be playing with that later. It is appreciated!

Another Pivot Chart question WRT displaying as %'s. I'm hoping there's some chart experts here. I'll try and make this as clear and quick as possible:

I have store inventory records of fruits sold throughout the past 5 years. We sell 5 fruits: apples, oranges, bananas, pineapple and pears. We only want the data for apples, oranges, and bananas. I can do this, no problem.

However, this only displays the amount sold each year, relative to nothing. I want to chart the fruit sold as a % of the overall amount of sold fruits. I can't seem to accomplish this without representing all 5 fruits on the chart via Legend Fields.

Any ideas?

It looks like I need calculated field items. I should have this done myself now, so ignore it for now unless I come back bitching tomorrow. Thanks again, y'all.

wandering would anyone know how to fix this issue please




http://forumserver.twoplustwo.com/48...oblem-1580463/

Hi, does anyone know any good way to create Excel tables/graphs for linear programming? I can use Solver, but I also need to represent the calculations graphically.

Is there a quick way to transfer info from multiple rows into multiple columns?

I run a football game, and the scores are displayed at the top of each player's column:



In order to create a leaderboard, I need to transfer this info into columns like so:



As you can see, the formula for cell A2 is =Sheet1!J1
Cell A3 is =Sheet1!M1
Cell A4 is =Sheet1!P1
Cell A5 is =Sheet1!S1
etc

Column B follows the same pattern, starting with the location of Player 1's score and going 3 rows across for each subsequent column.
Sheet1!K2
Sheet1!N2
Sheet1!Q2
etc

Is there an easy way to copy this formula down both columns for, say, 100 players? Or do I have to laboriously enter each cell individually?

If I only had to do this once I would do something like the following:

1) Copy the two rows in question (player names and points totals)
2) Paste values and transposed (under paste special)
3) Move player name column down one to align player names with points (it looks like this is necessary based on the image you posted)
4) Use remove duplicates to get rid of most of the rows that don't have player and points
5) Manually remove the one remaining non player-points row

was not aware of transpose; thanks a lot!

There is a transpose function I seem to recall, where you link ranges with the function so it automatically transposes the references. Not needed if you only want to do it once as has been said.

Lost Ostrich

If you want it as a formula this works, but feels unnecessarily complicated.

Assuming Player 1 data is in a1 in sheet 1:

In sheet 2 col c have a running total going up in 3s. C1=0, c2=3, c3=6 etc

In sheet2 a1:
=indirect(address(1,1+$c1,,,"Sheet1"))

Then copy that into b1 and add an offset, so:

=offset(indirect(address(1,1+$c1,,,"Sheet1")), 1,1)

Copy a:c down as far as required.

There must be a more elegant formula though.

Edit: any smiley faces are close brackets.

thanks for the help guys. I've had a play around with it and simply transposing the info works as long as the formulas use absolute values (ie $C$4 as opposes to C4) and I'm then able to extract the values I need from there. Always fun to learn new tricks and shortcuts!

This thread is a great resource for amateurs/enthusiasts like me btw

Say you are in charge of sending all NBA emails. Here are the categories, obv Adam Silver gets copied on everything.

1. Player (Tim Duncan)
2. Coach (Greg Popovich)
3. Head of Division (Bob HeadofSouthwest)

You are given a random list of X players and need to send emails to only the coaches and heads of divisions. How does this logic look? My mind wants to give the coaches and heads of divisions a field named 'Affected Players List' but this would be a huge field with hundreds of players per person for heads of divisions! Thanks.

Why not have three seperate sheets for each category?

This would work but would still need the 'Affected Players List' field, right? This implies you automatically know the hierarchy.

Right now the data structure is a master list of all categories with about 500k total rows, one row for each person. Most are players so that logic is easy when given a list of players. However I need a formula that pulls the senior guys based on being given a list of players.

Spurious, a better answer to your question is we can only query / filter off one master table / sheet in our software.

Probably need to append master sheet with an "Affected Players" column. It'll be huge and there is probably a better way but at least I know it'll work.

I don't fully understand how you know if someone is an affected player. This is based on a list, isn't it?

So a COUNTIFS in a separate column would work. If there's a more efficient way is impossible to tell since we don't know the data structure and how the setup works.

Got it by creating the affected players column. Thanks spurious

I am stuck here with something simple and its driving me nuts...

I have a sheet called PROJECTIONS and I pull data from a sheet called FEB DATA.

Column B, line 78 ='Feb Data'!J3-'Feb Data'!J4

I need Column B, line 79 to ='Feb Data'!J5-'Feb Data'!J6

I cant get it to skip, it will give me ='Feb Data'!J4-'Feb Data'!J5, with everything that I try. help...

How do I manually adjust the width of one column without affecting all the other columns?

This should work:

=OFFSET('Feb Data'$J$3,(ROW(A1)-1)*2,0)-OFFSET('Feb Data'$J$4',(ROW(A1)-1)*2,0)

I copy and paste that in and get a value error. What is this formula saying? What is:

(A1)?