Formula - find unique entries over several columns

Hi there

I’d first like to start out by thanking anyone who can help me out with this 
issue but that, for many of my own reasons which are very valid, I cannot use 
a VBA solution – only a formula based solution.

I have seven columns of data.  Most entries overlap throughout the columns 
but I need to find every unique entry in all of these columns.  My results 
need to be shown in one neat column with no spaces in between entries.  I 
have vastly simplified the amount of data for this example – this is usually 
over 500 lines for each column and I have about 40 columns, some of the 
columns stand alone and there are a few clumps like this one where seven or 
three or five columns relate to each other.  The stand alone columns are 
simple to obtain the unique entries but the complication comes when I need to 
extend this over several clumped columns.  For those clumped columns most of 
the unique entries are found in the first column but there can be a few 
others scattered in the other columns.  Due to the amount of data and the 
size of the spreadsheet I MUST have this in a very neat one column solution.  
I’ve given an example below of the column structure and the result I need.  
I’ve also given an example of the formulas I currently use for the stand 
alone columns.  I would really appreciate it if anyone can assist in this!!!

Column structure and desired result example:

Column A    Column B     Column C              Column D            Column E  
               Good          Very bad      Very bad               Very good   
        Extremely good        Very bad    Very good     Good                  
  Ok                      Moderate                 Ok             Ok          
      Bad                      Bad                    Very good               
Good         Good             Extremely good     Extremely good    Ok        
                  Good         Ok                 Bad                     
Moderate            Bad                        Bad           Bad              
  Ok                      Ok                      Very good               


Unique entries:
Good
Very bad
Ok
Good
Bad
Very good
Extremely good
Moderate


Stand alone column array formulas (using Ctrl+shift+enter)
{=IF(INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0))=".","EXTRA",INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0)))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI3,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI3,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI4,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI4,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI5,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI5,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI6,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI6,0)),0)),""))}
{=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI7,0)),0)),"")=".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI7,0)),0)),""))}

0
moily (55)
5/1/2009 2:30:05 PM
excel 39879 articles. 2 followers. Follow

2 Replies
365 Views

Similar Articles

[PageSpeed] 41

See example

http://cjoint.com/?fedeYkYN5W

JB
http://boisgontierjacques.free.fr/
On 1 mai, 16:30, moily <mo...@discussions.microsoft.com> wrote:
> Hi there
>
> I=92d first like to start out by thanking anyone who can help me out with=
 this
> issue but that, for many of my own reasons which are very valid, I cannot=
 use
> a VBA solution =96 only a formula based solution.
>
> I have seven columns of data. =A0Most entries overlap throughout the colu=
mns
> but I need to find every unique entry in all of these columns. =A0My resu=
lts
> need to be shown in one neat column with no spaces in between entries. =
=A0I
> have vastly simplified the amount of data for this example =96 this is us=
ually
> over 500 lines for each column and I have about 40 columns, some of the
> columns stand alone and there are a few clumps like this one where seven =
or
> three or five columns relate to each other. =A0The stand alone columns ar=
e
> simple to obtain the unique entries but the complication comes when I nee=
d to
> extend this over several clumped columns. =A0For those clumped columns mo=
st of
> the unique entries are found in the first column but there can be a few
> others scattered in the other columns. =A0Due to the amount of data and t=
he
> size of the spreadsheet I MUST have this in a very neat one column soluti=
on. =A0
> I=92ve given an example below of the column structure and the result I ne=
ed. =A0
> I=92ve also given an example of the formulas I currently use for the stan=
d
> alone columns. =A0I would really appreciate it if anyone can assist in th=
is!!!
>
> Column structure and desired result example:
>
> Column A =A0 =A0Column B =A0 =A0 Column C =A0 =A0 =A0 =A0 =A0 =A0 =A0Colu=
mn D =A0 =A0 =A0 =A0 =A0 =A0Column E =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Good =A0 =A0 =A0 =A0 =A0Very bad =A0 =A0 =
=A0Very bad =A0 =A0 =A0 =A0 =A0 =A0 =A0 Very good =A0
> =A0 =A0 =A0 =A0 Extremely good =A0 =A0 =A0 =A0Very bad =A0 =A0Very good =
=A0 =A0 Good =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 Ok =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Moderate =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 Ok =A0 =A0 =A0 =A0 =A0 =A0 Ok =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 Bad =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Bad =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Very good =A0 =A0 =A0 =A0 =A0 =A0 =A0
> Good =A0 =A0 =A0 =A0 Good =A0 =A0 =A0 =A0 =A0 =A0 Extremely good =A0 =A0 =
Extremely good =A0 =A0Ok =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Good =A0 =A0 =A0 =A0 Ok =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 Bad =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> Moderate =A0 =A0 =A0 =A0 =A0 =A0Bad =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0Bad =A0 =A0 =A0 =A0 =A0 Bad =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 Ok =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Ok =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0Very good =A0 =A0 =A0 =A0 =A0 =A0 =A0
>
> Unique entries:
> Good
> Very bad
> Ok
> Good
> Bad
> Very good
> Extremely good
> Moderate
>
> Stand alone column array formulas (using Ctrl+shift+enter)
> {=3DIF(INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0))=3D".","EXTRA",INDEX(X2=
:X495,=ADMATCH(0,-ISBLANK(X2:X495),0)))}
> {=3DIF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),IND=
EX(X=AD$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0=
)),"")=3D=AD".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:=
X$495<>"")),=ADINDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495=
,AI$2:AI2,0)),0=AD)),""))}
> {=3DIF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:X$495<>"")),IND=
EX(X=AD$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI3,0)),0=
)),"")=3D=AD".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:=
X$495<>"")),=ADINDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495=
,AI$2:AI3,0)),0=AD)),""))}
> {=3DIF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:X$495<>"")),IND=
EX(X=AD$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI4,0)),0=
)),"")=3D=AD".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:=
X$495<>"")),=ADINDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495=
,AI$2:AI4,0)),0=AD)),""))}
> {=3DIF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:X$495<>"")),IND=
EX(X=AD$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI5,0)),0=
)),"")=3D=AD".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:=
X$495<>"")),=ADINDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495=
,AI$2:AI5,0)),0=AD)),""))}
> {=3DIF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:X$495<>"")),IND=
EX(X=AD$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI6,0)),0=
)),"")=3D=AD".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:=
X$495<>"")),=ADINDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495=
,AI$2:AI6,0)),0=AD)),""))}
> {=3DIF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:X$495<>"")),IND=
EX(X=AD$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI7,0)),0=
)),"")=3D=AD".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:=
X$495<>"")),=ADINDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495=
,AI$2:AI7,0)),0=AD)),""))}

0
5/2/2009 11:25:49 AM
Thanks JB but I already have a few solutions along these lines.  
Unfortunately, it's the size of the data that causes trouble with them - I 
often have about 8 clumped columns with 500 rows of data in each - your 
solution would mean several columns (as there are several bunches of clumped 
columns) of 4000 rows of data.  

There are other complications to the sheet as well that I haven't gone into 
but suffice it to say I really need one neat column solution - can you or 
anyone else help?

Cheers,
Ann

"JB" wrote:

> See example
> 
> http://cjoint.com/?fedeYkYN5W
> 
> JB
> http://boisgontierjacques.free.fr/
> On 1 mai, 16:30, moily <mo...@discussions.microsoft.com> wrote:
> > Hi there
> >
> > I’d first like to start out by thanking anyone who can help me out with this
> > issue but that, for many of my own reasons which are very valid, I cannot use
> > a VBA solution – only a formula based solution.
> >
> > I have seven columns of data.  Most entries overlap throughout the columns
> > but I need to find every unique entry in all of these columns.  My results
> > need to be shown in one neat column with no spaces in between entries.  I
> > have vastly simplified the amount of data for this example – this is usually
> > over 500 lines for each column and I have about 40 columns, some of the
> > columns stand alone and there are a few clumps like this one where seven or
> > three or five columns relate to each other.  The stand alone columns are
> > simple to obtain the unique entries but the complication comes when I need to
> > extend this over several clumped columns.  For those clumped columns most of
> > the unique entries are found in the first column but there can be a few
> > others scattered in the other columns.  Due to the amount of data and the
> > size of the spreadsheet I MUST have this in a very neat one column solution.  
> > I’ve given an example below of the column structure and the result I need.  
> > I’ve also given an example of the formulas I currently use for the stand
> > alone columns.  I would really appreciate it if anyone can assist in this!!!
> >
> > Column structure and desired result example:
> >
> > Column A    Column B     Column C              Column D            Column E  
> >                Good          Very bad      Very bad               Very good  
> >         Extremely good        Very bad    Very good     Good                  
> >   Ok                      Moderate                 Ok             Ok          
> >       Bad                      Bad                    Very good              
> > Good         Good             Extremely good     Extremely good    Ok        
> >                   Good         Ok                 Bad                    
> > Moderate            Bad                        Bad           Bad              
> >   Ok                      Ok                      Very good              
> >
> > Unique entries:
> > Good
> > Very bad
> > Ok
> > Good
> > Bad
> > Very good
> > Extremely good
> > Moderate
> >
> > Stand alone column array formulas (using Ctrl+shift+enter)
> > {=IF(INDEX(X2:X495,MATCH(0,-ISBLANK(X2:X495),0))=".","EXTRA",INDEX(X2:X495,­MATCH(0,-ISBLANK(X2:X495),0)))}
> > {=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X­$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0)),"")=­".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI2,0))<COUNT(1/(X$2:X$495<>"")),­INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI2,0)),0­)),""))}
> > {=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X­$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI3,0)),0)),"")=­".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI3,0))<COUNT(1/(X$2:X$495<>"")),­INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI3,0)),0­)),""))}
> > {=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X­$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI4,0)),0)),"")=­".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI4,0))<COUNT(1/(X$2:X$495<>"")),­INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI4,0)),0­)),""))}
> > {=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X­$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI5,0)),0)),"")=­".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI5,0))<COUNT(1/(X$2:X$495<>"")),­INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI5,0)),0­)),""))}
> > {=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X­$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI6,0)),0)),"")=­".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI6,0))<COUNT(1/(X$2:X$495<>"")),­INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI6,0)),0­)),""))}
> > {=IF(IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:X$495<>"")),INDEX(X­$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI7,0)),0)),"")=­".","EXTRA",IF(COUNT(MATCH(X$2:X$495,AI$2:AI7,0))<COUNT(1/(X$2:X$495<>"")),­INDEX(X$2:X$495,MATCH(0,(X$2:X$495<>"")-ISNA(MATCH(X$2:X$495,AI$2:AI7,0)),0­)),""))}
> 
> 
0
moily (55)
5/5/2009 8:53:01 AM
Reply:

Similar Artilces:

Easy Formula Question
easy I'm sure if you know how - but I don't. Value of cells N26 and O26 are added together and result shown in P26 if both values in these two cells are zero (£0.00) I want cell P26 to be blank and not show a £0.00 value any ideas ?? thanks Use an IF function: =IF(SUM(N26:O26)=0,"",SUM(N26:O26) Andrea Jones http://www.allaboutoffice.co.uk http://www.stratatraining.co.uk http://www.allaboutclait.com "Anthony" wrote: > easy I'm sure if you know how - but I don't. > > Value of cells N26 and O26 are added together and result shown in...

Nested functions in conditional formatting formulae
I am trying to format a range of cells as a function of cell value compared with other cells. My attempts have failed so far. For e.g.: =OR(ADDRESS(ROW(),COLUMN())=$B$18,ADDRESS(ROW(),COLUMN())=$B$17) =OR(INDIRECT(CONCATENATE("R",(ROW()+1),"C",COLUMN()),FALSE)=B17) I do not wish to write VB code. Thanks in advance for any ideas. Maybe you could post what you are trying to achive it's hard to guess what you are trying to do -- Regards, Peo Sjoblom "Joseph" <Joseph@discussions.microsoft.com> wrote in message news:3331CF9E-E84E-44D0-8186-6F9E19DEE2...

MS 2003
I have calendar entires that are automatically forced to reoccur when I open and change the date. When I go looking for them on the date that I changed them to, they are not there. Instead, they are hidden in a reoccurance category section of the calendar system, not easily visible by the view that I use. These calendar entries do in fact reoccur regularly but I do not have an OL reoccurance setup on them - I like to manually change the date after I read it to a month forward for example. Oddly, after installing Outlook 2003 on my new Vista Ultimate machine this reoccurnce is being forced to...

formula in different worksheets
If I have the following formula in a worksheet to bring up a message box =IF((F4-E4)>=15,PromptPayment()+F4-E4,F4-E4) How can I transfer some information from different cells to a new worksheet if the formula above works? Thanks Monty Hi formulas can't change other formulas. If you need such things maybe the following is interesting for you: http://www.dicks-blog.com/archives/2004/12/22/functions-that-do-things/trackback/ -- Regards Frank Kabel Frankfurt, Germany "Monty" <Monty@discussions.microsoft.com> schrieb im Newsbeitrag news:30A14E15-4B1D-47AA-8DDE-7969...

Select a random value from a column of values
Hi, I have never programmed Excel before, but do use it a fair amount, including use of built-in formulae. I would like to be able to select a random value among a group of values - is this possible? So for example, in a spreadsheet I have in column A a list of names from cell 1 to cell 20. then I want to pick a name from random from that list. How would I do? Any input would be appreciated. -- Paul paulastya@gmail.com PS I do not have visual Studio/VB installed on my machine. someone else will give you a simpler formula, but, if not, the following will work: =INDIRECT("A"&a...

Find Distribution Lists that a user is the owner
Hello, Is there a quick way to list out all the Distribution Lists that a particular user is the owner? When we have users leave the company, we constantly get requests to transfer ownership. Thanks, Sun Sun <Sun@discussions.microsoft.com> wrote: >Is there a quick way to list out all the Distribution Lists that a >particular user is the owner? When we have users leave the company, we >constantly get requests to transfer ownership. Not without writing a small script to query the AD. And, if the user account is deleted from the AD before you ask the question, you wo...

Tab spacing in formula...
I have a sheet that pulls info from several other fields to create a text string. The problem is that I need all of the values from my other cells to be a tab space apart. Right now they are separated by several spaces, but it is throwing off my numbers. example, what I have: Name Date Value John 2.20.07 1 Dan 3.20.07 2 example, what I need: Name Date Value John 2.20.07 1 Dan 3.20.07 2 Franky 4.05.07 3 I imagine you have a formula like: =A1&" "&B1&" "&C1 so you are putting a fixed number of...

How To Simplify Formula with SUMIFs
Code Double Feature Senior Pass Redemption Hubble COMP- staff Double Feature General IMAX- General Pass Redemption Hubble COMP Companion Gen Companion Snr Double Feature Child Double Feature General IMAX- Child* IMAX- Child* IMAX- General IMAX- General IMAX- Senior IMAX- Senior Pass Redemption Hubble Pass Redemption Hubble 2 for 1 DF- General 2 for 1- General COMP Companion DF Gen Csc- Corporate Single $5 Daycare- Adult Daycare- Child IMAX- Child* IMAX- Child* IMAX- General IMAX- General Pass Redemption Hubble COMP Companion DF Chd Double Feature Child Double Feature General Double Feature Gen...

Changing a formula in xlt
I posted in another group but I don't think they understand what I am saying. Maybe you can assist me. I am using a template that is in xlt and in the shaded column I need the formula in L20 that is =if(D20<>"",D0*K20,"") I get an error stating that the shaded area is pre formatted by excel. How can I get my formula into the place where I need it? When I try to open the template to see the formulas it wants a password and I have never had a password. Question 2. How do you make your own template? I need one for a convention that has discounts deducted as wel...

What does these simbols { } means in a formula???
Please somebody explains me what does this simbols means in a formula like this one:{=Sum(IF((B4:B7)=C17);G4:G7))} Please help me!! Tanx Alfredo, It means that it is an array formula. The formula is being used to resolve each cell in the array presented. So in your example, each cell in B4:B7 is tested against C17, and if true, the corresponding value in G4:G7 is returned, which gets summed by the SUM formula. You don't enter {} yourself, Excel does it when you tell it (Excel) that you want to use array formulae by typing the formula in and using Ctrl-Shift-Enter rather than just Ente...

Lookup when value is between 2 columns
Hi, I need to lookup a column if the value is between the first 2 columns Col 1 Col2 Col3 Col 4 1 5 Yes 10 6 10 Yes 20 11 15 No 30 If the value I am looking up is 7, I need to return Yes from Col 3. So if value is between col 1 and col 2, return Col 3. I was able to get Col 4 using Sum (if..) but do not know how to return the text in Col 3 Thanks in advance.. If column 1 is in E2 and Col 2 is F2 Col 3 = =IF(OR(E2<=$G$1,F2<=$G$1),"Yes","No") For column 4 I used =(INT(MAX(E2:F2)/$G$1)+1)*10 G1 is the number to di...

Help with grouping columns
Maybe I'm overlooking the obvious...but I'm having a hard time groupin columns. Here's the goal: Columns A - L are the 12 months... Jan-Dec. I want to group 3 columns (3 months) at a time. So I'd have 4 colum groups side by side. I select the entire first 3 columns...data...group/outline...an group...creates the group fine. I then select the next 3 columns (next 3 months)...to be in their ow group. When I try to group them...it just ads them to the firs group...making one large group. The first 3 columns are not selected when I do this. I've also tried playing with the s...

macro
I have several columns in an income sheet. One column has a sum at the bottom. I want to be able to run a simple macro that will copy the last row of formatted cells, just above the sum total, and insert it as a new row just above the sum total so another line of data can be input. But I want the sum total to sum also the newly inserted row. Presently I can't figure out how to do it. Example A B C 1 24 Oct Dollars In $100 2 25 Oct Dollars out $25 3 Total ...

How do I find 2004 forums
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC I can't believe that the navigation on this site is soooo bad that I have to ask this but how do I get to the old office 2004 forums that I used to get to from the old mactopia? Thank you irchamandy@officeformac.com wrote: > I can't believe that the navigation on this site is soooo bad that I > have to ask this but how do I get to the old office 2004 forums that > I used to get to from the old mactopia? The old Mactopia never hosted its own forums. Instead, it pointed folks to Google Groups <h...

Column Heading Display
All of a sudden, I have noticed that my column headings are 1,2,3, rather than A,B,C. How do I return them to A,B,C so that I can enter formulas? Tools > Options > General tab > uncheck R1C1 HTH George Gee "KAS" <KAS@discussions.microsoft.com> wrote in message news:B42F3AF3-80FD-486E-AFF6-B9AEA74858CB@microsoft.com... > All of a sudden, I have noticed that my column headings are 1,2,3, rather > than A,B,C. How do I return them to A,B,C so that I can enter formulas? ...

Running Total Column
Hello Group Excel, I am using Excel 2003. My computation Columns are "E" to "J". Column "I" is the Total. Column "J" is the Running Total. With the following data: E F G H I J ..12 2.12 .09 -3.25 -.92 -.92 ..08 1.99 .03 -2.99 -.89 -1.81 ..10 2.05 .07 -3.15 -.93 -2.74 ((In the "J" Column when there is no)) -2.74 ((amount in Columns "E...

Find & Replace Cell Colors
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there any way to find and replace cell colors in Excel? I know you can do this in Excel for PCs but is it an option for Mac users? > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3347540289_42178790 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 1/28/10 2:34 PM, in article 59bb1c81.-1@webcrossing.JaKIaxP2ac0, "bbagby@officeformac.com" <bbagby@of...

How to change a formula reference?
I need to create a formula that references a range of cells. The range is supposed to be determined by what the user enters into another cell. Can a formula have a variable in it that will grab a number from another cell? For instance: =COUNTIF(N[B18]:N[B18+B18],"Y")? Where you see B18 the user has entered in a number, we will say it was 5. I need the function to reference the cells in the N column from N5 (cell B18 has a 5 in it) to N10 (B18+B18 or 5+5). I appreciate any help you all can give me. Thanks so much in advance. Brockettb Hi! Assume you enter in cells: A1 = 5...

How to compare two column of data and sort them out according
Hi, I have this problem, What i have on hand is two column of data containing part number of two different devices. What i am trying to do is to compare this two column and find out all the part number that is common to both device and also which part number is unique. And organise and display this in excel. Anyone know any way to accomplish it please help. -- kuansheng ------------------------------------------------------------------------ kuansheng's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30658 View this thread: http://www.excelforum.com/showthread.p...

Finding cells with leading characters
My spreadsheet looks like this: A 1 000000 2 182068 3 198506 4 200081 5 368010 6 580905 7 680001 8 686850 9 690060 10 700012 11 701068 12 802054 13 920211 14 968902 Using EDIT/FIND, when I enter '68' in the "Find What:" box, Excel finds A2, A5, A7, A8, A11 and A14 because their values contain '68'. What do I enter in the "Find What:" box to find the cells whose values BEGIN with '68'? (In this example, ...

Formula #14
I am trying to add up a column, but some of the cells have a space inserted (which need to remain there), however this is giving me #Value! instead of the correct answer - any ideas, please help - it's driving me mad! Tina "Tina" <anonymous@discussions.microsoft.com> wrote in message news:06196CE6-E1B4-4E19-A197-467B5B16D585@microsoft.com... > I am trying to add up a column, but some of the cells have a space inserted (which need to remain there), however this is giving me #Value! instead of the correct answer - any ideas, please help - it's driving me mad!! > &...

Formula help #43
I have lots of groups with subtotals. I need to multiply the various subtotals by a factor (factors change depending on the group) that's in its own column and then get the product (which needs to be an even number (rounded up). Those products need to be added to the subtotal - which should now give a new subtotal for the group. Then those new group subtotals need to be added to get a Total. All I get are "0" when I try to do this. And I'm under a deadline. Is there anyone out there who can help???? Thanks. url:http://www.ureader.com/gp/1027-1.aspx It sounds as if ...

Fill formulas down in macro
I'd like to create a macro that fills a formula down to the final row that has any data. -- Just filling to adjacent cells (e.g. double-clicking on the autofill button) doesn't work because some of the rows don't have data in the cells the formula is looking for. -- I know I can select all the cells below the one I entered the formula in and hit ctrl-D to fill the formula, but I need this macro to be able to run no matter how many rows of data are present. -- I tried filling the fomula all the way past the highest row number I'd ever thought I'd reach but then ...

Protecting formulas in cells
I need to know how to protect only the cells that have a formula in them, and still allow editing of other cells. How. You can select special ranges by: ctrl-A to select the whole sheet Edit|goto|Special click on Formulas (or constants or blanks) Now you've just selected all the formulas (or constants or blanks). Format|Cells|Protection Tab Check Locked or uncheck Locked (depending on what you selected) Now protect the worksheet. Tools|Protection|protect sheet. ==== Depending on how many cells I want different, I'll select everything and unlock them. Then come back and lock ju...

Preserving Formulas
I have some simple raw data corresponding to five variables. I am constructing a table where, given a value for one of the variables and using the FORECAST function, the predicted values for the other four variables are calculated given the correlation of the raw data. However, I would like to make this table dynamic - so that I could input a value for ANY one of the five variables and return the expected values for the other four. As of now I do not know how to do this. Perhaps if there was a way to preserve the formula in each of the cells while still being able to input an arbitrary value....