Find two different, unknown, names in list

```I have a football spreadsheet with different picks listed in columns.
For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc.
How can I have both names displayed at the bottom of the column: Ala
and Tex?  The first name is easy, simply select the top pick in the
column.  The second pick is the "other" name.  How can this be done?
Thanks,
Ed M.
```
 0
Ed_M
12/29/2009 12:32:10 PM
excel 39879 articles. 2 followers.

5 Replies
1139 Views

Similar Articles

[PageSpeed] 26

```On Dec 29, 6:32=A0am, Ed_M <edmto...@gmail.com> wrote:
> I have a football spreadsheet with different picks listed in columns.
> For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc.
> How can I have both names displayed at the bottom of the column: Ala
> and Tex? =A0The first name is easy, simply select the top pick in the
> column. =A0The second pick is the "other" name. =A0How can this be done?
> Thanks,
> Ed M.

If there are only two and you want them at the bottom of the column in
separate cells then, say the range holding the picks is A2:A14...
=3DA2 in A15 for the first (as you stated)
=3DINDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""),
1)) in A16 for the other. This is an array formula so must be entered
using the Ctrl+Shift+Enter key combination.

If there are only two and you want them together in the same cell
separated by a comma and a space then
=3DA2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))
+1,""),1)) in A15. This is an array formula so must be entered using
the Ctrl+Shift+Enter key combination.

If there are more than two and you want them at the bottom of the
column in separate cells then...
=3DIF(ROW(\$A1)>SUMPRODUCT((\$A\$2:\$A\$14<>"")/(COUNTIF(\$A\$2:\$A\$14,\$A\$2:\$A
\$14)+(\$A\$2:\$A\$14=3D""))),"",INDEX(\$A\$2:\$A\$14,SMALL(IF(MATCH(\$A\$2:\$A\$14,\$A
\$2:\$A\$14)<>ROW(\$A\$2:\$A\$14)-MIN(ROW(\$A\$2:\$A\$14))+1,"",ROW(\$A\$2:\$A\$14)-
MIN(ROW(\$A\$2:\$A\$14))+1),ROW(\$A1)))) in A15 filled down to accommodate
the likely maximum number of different picks. This is an array formula
so must be entered using the Ctrl+Shift+Enter key combination.

Ken Johnson

```
 0
Ken
12/29/2009 2:13:44 PM
```On Dec 29, 8:13=A0am, Ken Johnson <kencjohn...@gmail.com> wrote:
> On Dec 29, 6:32=A0am, Ed_M <edmto...@gmail.com> wrote:
>
> > I have a football spreadsheet with different picks listed in columns.
> > For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc.
> > How can I have both names displayed at the bottom of the column: Ala
> > and Tex? =A0The first name is easy, simply select the top pick in the
> > column. =A0The second pick is the "other" name. =A0How can this be done=
?
> > Thanks,
> > Ed M.
>
> If there are only two and you want them at the bottom of the column in
> separate cells then, say the range holding the picks is A2:A14...
> =3DA2 in A15 for the first (as you stated)
> =3DINDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""),
> 1)) in A16 for the other. This is an array formula so must be entered
> using the Ctrl+Shift+Enter key combination.
>
> If there are only two and you want them together in the same cell
> separated by a comma and a space then
> =3DA2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))
> +1,""),1)) in A15. This is an array formula so must be entered using
> the Ctrl+Shift+Enter key combination.
>
> If there are more than two and you want them at the bottom of the
> column in separate cells then...
> =3DIF(ROW(\$A1)>SUMPRODUCT((\$A\$2:\$A\$14<>"")/(COUNTIF(\$A\$2:\$A\$14,\$A\$2:\$A
> \$14)+(\$A\$2:\$A\$14=3D""))),"",INDEX(\$A\$2:\$A\$14,SMALL(IF(MATCH(\$A\$2:\$A\$14,\$A
> \$2:\$A\$14)<>ROW(\$A\$2:\$A\$14)-MIN(ROW(\$A\$2:\$A\$14))+1,"",ROW(\$A\$2:\$A\$14)-
> MIN(ROW(\$A\$2:\$A\$14))+1),ROW(\$A1)))) in A15 filled down to accommodate
> the likely maximum number of different picks. This is an array formula
> so must be entered using the Ctrl+Shift+Enter key combination.
>
> Ken Johnson

Oops!
The MATCH function in the last one should have its 3rd argument set to
zero...
=3DIF(ROW(\$A1)>SUMPRODUCT((\$A\$2:\$A\$14<>"")/(COUNTIF(\$A\$2:\$A\$14,\$A\$2:\$A
\$14)+(\$A\$2:\$A\$14=3D""))),"",INDEX(\$A\$2:\$A\$14,SMALL(IF(MATCH(\$A\$2:\$A
\$14,\$A
\$2:\$A\$14,0)<>ROW(\$A\$2:\$A\$14)-MIN(ROW(\$A\$2:\$A\$14))+1,"",ROW(\$A\$2:\$A
\$14)-
MIN(ROW(\$A\$2:\$A\$14))+1),ROW(\$A1))))

Ken Johnson
```
 0
Ken
12/29/2009 3:31:32 PM
```On Dec 29, 9:13=A0am, Ken Johnson <kencjohn...@gmail.com> wrote:
> On Dec 29, 6:32=A0am, Ed_M <edmto...@gmail.com> wrote:
>
> > I have a football spreadsheet with different picks listed in columns.
> > For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc.
> > How can I have both names displayed at the bottom of the column: Ala
> > and Tex? =A0The first name is easy, simply select the top pick in the
> > column. =A0The second pick is the "other" name. =A0How can this be done=
?
> > Thanks,
> > Ed M.
>
> If there are only two and you want them at the bottom of the column in
> separate cells then, say the range holding the picks is A2:A14...
> =3DA2 in A15 for the first (as you stated)
> =3DINDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""),
> 1)) in A16 for the other. This is an array formula so must be entered
> using the Ctrl+Shift+Enter key combination.
>
> If there are only two and you want them together in the same cell
> separated by a comma and a space then
> =3DA2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))
> +1,""),1)) in A15. This is an array formula so must be entered using
> the Ctrl+Shift+Enter key combination.
>
> If there are more than two and you want them at the bottom of the
> column in separate cells then...
> =3DIF(ROW(\$A1)>SUMPRODUCT((\$A\$2:\$A\$14<>"")/(COUNTIF(\$A\$2:\$A\$14,\$A\$2:\$A
> \$14)+(\$A\$2:\$A\$14=3D""))),"",INDEX(\$A\$2:\$A\$14,SMALL(IF(MATCH(\$A\$2:\$A\$14,\$A
> \$2:\$A\$14)<>ROW(\$A\$2:\$A\$14)-MIN(ROW(\$A\$2:\$A\$14))+1,"",ROW(\$A\$2:\$A\$14)-
> MIN(ROW(\$A\$2:\$A\$14))+1),ROW(\$A1)))) in A15 filled down to accommodate
> the likely maximum number of different picks. This is an array formula
> so must be entered using the Ctrl+Shift+Enter key combination.
>
> Ken Johnson

Ken,
Impressive and thorough!
I looked at it for a while.  Could you please explain: IF(C3:C50<>C3
Also, what does the last ,1)) do?
What makes this an array?
Thanks,
Ed M
```
 0
Ed_M
12/29/2009 3:31:37 PM
```On Dec 29, 9:31=A0am, Ed_M <edmto...@gmail.com> wrote:
> On Dec 29, 9:13=A0am, Ken Johnson <kencjohn...@gmail.com> wrote:
>
>
>
> > On Dec 29, 6:32=A0am, Ed_M <edmto...@gmail.com> wrote:
>
> > > I have a football spreadsheet with different picks listed in columns.
> > > For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, etc.
> > > How can I have both names displayed at the bottom of the column: Ala
> > > and Tex? =A0The first name is easy, simply select the top pick in the
> > > column. =A0The second pick is the "other" name. =A0How can this be do=
ne?
> > > Thanks,
> > > Ed M.
>
> > If there are only two and you want them at the bottom of the column in
> > separate cells then, say the range holding the picks is A2:A14...
> > =3DA2 in A15 for the first (as you stated)
> > =3DINDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""),
> > 1)) in A16 for the other. This is an array formula so must be entered
> > using the Ctrl+Shift+Enter key combination.
>
> > If there are only two and you want them together in the same cell
> > separated by a comma and a space then
> > =3DA2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14)=
)
> > +1,""),1)) in A15. This is an array formula so must be entered using
> > the Ctrl+Shift+Enter key combination.
>
> > If there are more than two and you want them at the bottom of the
> > column in separate cells then...
> > =3DIF(ROW(\$A1)>SUMPRODUCT((\$A\$2:\$A\$14<>"")/(COUNTIF(\$A\$2:\$A\$14,\$A\$2:\$A
> > \$14)+(\$A\$2:\$A\$14=3D""))),"",INDEX(\$A\$2:\$A\$14,SMALL(IF(MATCH(\$A\$2:\$A\$14,=
\$A
> > \$2:\$A\$14)<>ROW(\$A\$2:\$A\$14)-MIN(ROW(\$A\$2:\$A\$14))+1,"",ROW(\$A\$2:\$A\$14)-
> > MIN(ROW(\$A\$2:\$A\$14))+1),ROW(\$A1)))) in A15 filled down to accommodate
> > the likely maximum number of different picks. This is an array formula
> > so must be entered using the Ctrl+Shift+Enter key combination.
>
> > Ken Johnson
>
> Ken,
> Impressive and thorough!
> I looked at it for a while. =A0Could you please explain: IF(C3:C50<>C3
> Also, what does the last ,1)) do?
> What makes this an array?
> Thanks,
> Ed M

Hi Ed_M,

If you select 48 vertical cells then type into the active cell...
=3DROW(C3:C50)-MIN(ROW(C3:C50))+1
then press Ctrl+Shift+Enter you will see an array of number from 1 up
to 48.
If you select a different bunch of 48 vertical cells then type into
the active cell
=3DIF(C3:C50<>C3,ROW(C3:C50)-MIN(ROW(C3:C50))+1,"")
then press Ctrl+Shift+Enter you will see an array of numbers and
blanks.
The first element in the array has to be a blank because C3=3DC3.
The second element will also be blank if C4=3DC3. If C4<>C3 then the
second element will equal 2.
Similarly, the third element will be blank if C5=3DC3, otherwise it will
equal 3, etc.
So, to get to the "other" Pick in that column its just a matter of
selecting the smallest number in the array of blanks and numbers
(although it could be any of the numbers) then using the INDEX
function with that number to locate the "other" pick.

Ken Johnson
```
 0
Ken
12/29/2009 3:50:52 PM
```On Dec 29, 9:50=A0am, Ken Johnson <kencjohn...@gmail.com> wrote:
> On Dec 29, 9:31=A0am, Ed_M <edmto...@gmail.com> wrote:
>
>
>
> > On Dec 29, 9:13=A0am, Ken Johnson <kencjohn...@gmail.com> wrote:
>
> > > On Dec 29, 6:32=A0am, Ed_M <edmto...@gmail.com> wrote:
>
> > > > I have a football spreadsheet with different picks listed in column=
s.
> > > > For example, one column has picks: Ala, Ala, Ala, Tex, Ala, Tex, et=
c.
> > > > How can I have both names displayed at the bottom of the column: Al=
a
> > > > and Tex? =A0The first name is easy, simply select the top pick in t=
he
> > > > column. =A0The second pick is the "other" name. =A0How can this be =
done?
> > > > Thanks,
> > > > Ed M.
>
> > > If there are only two and you want them at the bottom of the column i=
n
> > > separate cells then, say the range holding the picks is A2:A14...
> > > =3DA2 in A15 for the first (as you stated)
> > > =3DINDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A14))+1,""=
),
> > > 1)) in A16 for the other. This is an array formula so must be entered
> > > using the Ctrl+Shift+Enter key combination.
>
> > > If there are only two and you want them together in the same cell
> > > separated by a comma and a space then
> > > =3DA2&", "&INDEX(A2:A14,SMALL(IF(A2:A14<>A2,ROW(A2:A14)-MIN(ROW(A2:A1=
4))
> > > +1,""),1)) in A15. This is an array formula so must be entered using
> > > the Ctrl+Shift+Enter key combination.
>
> > > If there are more than two and you want them at the bottom of the
> > > column in separate cells then...
> > > =3DIF(ROW(\$A1)>SUMPRODUCT((\$A\$2:\$A\$14<>"")/(COUNTIF(\$A\$2:\$A\$14,\$A\$2:\$=
A
> > > \$14)+(\$A\$2:\$A\$14=3D""))),"",INDEX(\$A\$2:\$A\$14,SMALL(IF(MATCH(\$A\$2:\$A\$1=
4,\$A
> > > \$2:\$A\$14)<>ROW(\$A\$2:\$A\$14)-MIN(ROW(\$A\$2:\$A\$14))+1,"",ROW(\$A\$2:\$A\$14)-
> > > MIN(ROW(\$A\$2:\$A\$14))+1),ROW(\$A1)))) in A15 filled down to accommodate
> > > the likely maximum number of different picks. This is an array formul=
a
> > > so must be entered using the Ctrl+Shift+Enter key combination.
>
> > > Ken Johnson
>
> > Ken,
> > Impressive and thorough!
> > I looked at it for a while. =A0Could you please explain: IF(C3:C50<>C3
> > Also, what does the last ,1)) do?
> > What makes this an array?
> > Thanks,
> > Ed M
>
> Hi Ed_M,
>
> If you select 48 vertical cells then type into the active cell...
> =3DROW(C3:C50)-MIN(ROW(C3:C50))+1
> then press Ctrl+Shift+Enter you will see an array of number from 1 up
> to 48.
> If you select a different bunch of 48 vertical cells then type into
> the active cell
> =3DIF(C3:C50<>C3,ROW(C3:C50)-MIN(ROW(C3:C50))+1,"")
> then press Ctrl+Shift+Enter you will see an array of numbers and
> blanks.
> The first element in the array has to be a blank because C3=3DC3.
> The second element will also be blank if C4=3DC3. If C4<>C3 then the
> second element will equal 2.
> Similarly, the third element will be blank if C5=3DC3, otherwise it will
> equal 3, etc.
> So, to get to the "other" Pick in that column its just a matter of
> selecting the smallest number in the array of blanks and numbers
> (although it could be any of the numbers) then using the INDEX
> function with that number to locate the "other" pick.
>
> Ken Johnson

The ",1))" part makes the SMALL function return the smallest number in
the array.
(though any of the numbers in the array would work provided there are
only 2 different Picks in the column.
It's an array function because it uses an array of blanks and numbers
resulting from the comparison of all the column values with the top
value in the column.

Ken Johnson
```
 0
Ken
12/29/2009 3:58:17 PM

Similar Artilces:

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Steps to Share Outlook on 2 different drives
This is a problem created by a dual boot of XPPSP2 on one drive C:\ and Vista on another drive E:\ that I use to format for new builds of Vista on the same box. I want to take my Outlook 2003 in box and folders on two different drives on one box and combine them so that all the emails go to one account and all the folders can be shared or used on each drive. I want to do the same with Outlook Express as well, and since this is an *Outlook newsgroup, I'll just take what I learn here and apply it to the ..dbx folder(s) in it. The idea is to receive email on both drives in one in one...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

unknown .edb file
I have error messages in the log files that references a corrupt database named C:\Program Files\exchsrvr\Exchangeserver_Outlook.edb. "Outlook" is the name of the server. The problem is that this database is not listed in any of the Mail Stores or Public stores. I want to run the repair utilities on it, but since I'm not sure what it is used for, I don't know how I can restore it if the repair goes bad. Does anyone know what this is used for or how I can determine that myself? BTW, the database always has a current time stamp, so I assume that it is used by something....

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

Mailboxsize is different
hi! i've got a crazy problem. when you look at the mailboxes in the exchange system manager, then you see, e.g. user xy with 220 mb mailboxsize. but when you look at the outlook, then the mailbox size is only 150 mb. but there are no filters or something else activated. and the problem is on the local outlook and on the terminalserver outlook. i hope someone has a solution about my problem. thanks. stefan ...

Indirect Method for Name Box Variable? #2
DARN The reason I want to do this is that I have to collect data on a dail basis but I have to display it on a graph on a weekly basis. I select the entire week of cells and give it a name like week52. The following week I have to select the entire week and name i week53. Everything is set up that I can just copy and the numbers will progres for whatever length of time I need to graph (months, years, etc) excep this. Maybe I'll just go back to using the range of cell numbers. Thanks guys -- Chris Brenna ----------------------------------------------------------------------- Chris Bre...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Exchange 2003, Two Sites, Two Smarthosts, One Domain, No Internal mail!!
Hi, I hope there is someone out there who can point me in the right direction. We have two sites, Brisbane and Sydney, connected by a frame realy connection. Sydney is currently running exchange 2003 (upgraded by migration from exchange 5.5 about a month ago). Brisbane is currently running their mail through sendmail and sendmail only. We have just installed exchange 2003 on server 2003 box in Brisbane and are trying to get the two sites working in two routing groups. The topology will be the same in both Brisbane and Sydney, in that each city will have an external mail server running sendma...

If a worksheet name is = to test then a msgbox appears
I'm looking for a macro that will display a msgbox if a worksheet is = to test. For example, if the name of a sheet in a workbook is equal to test then display msgbox saying sheet already exists. Thanks Vick dim ws as worksheet set ws = nothing on error resume next set ws = worksheets("test") on error goto 0 if ws is nothing then msgbox "doesn't exist" else msgbox "already exists" end if Vick wrote: > > I'm looking for a macro that will display a msgbox if a worksheet is = to test. > > For example, if the name of a sheet in a w...

find instance of IE for server socket in activex component
Hi I have an activex component that has a server socket (using casyncsocket class) ..The problem is that when I have multiple instances of the IE open only the first instance (created first) recieves the messages the rest do not recieve the message. Is there a way to solve this problem .(i want the server send the reply to that instance of ie that sent the message) .Do I need to pass something like a pointer of hte javascript object .If so how Thanks When you say "server socket", that suggests a socket that does a Listen followed by an Accept. Otherwise it is a client socke...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...

If statment with two conditions
I have an IF statement with two condtions as noted below. I want it to work so that if column G has a y in it, it will be hidden, and also if column G has an N in it and column O has a 0 then it will be hidden. The second part doesnt work. Any thoughts? Sub Hide_new() Dim cell As Range Dim rngisect As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rngisect = Application.Intersect(ActiveSheet.UsedRange, Range("G19: G4061")) For Each cell In rngisect If cell.Value = "Y" Then cell.EntireRow.Hidden ...

List the UNIQUE certain fields from the database
Dear Experts, Please help related to the above subject. From this discussion, got the following formula which is work well and faster for small of database. =OFFSET(\$G\$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(DATA)=I\$1:I1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1) where \$G\$1, the first row of DATA. how to convert it to VBA as this time i have a huge database. for Tab Data, Advanced, Unique Record only (works well also, but i need the VBA). basically we would like to do similar like this: Data Sample Vendor Name A B...

distibution list / updating
when we update our contacts how can we get outlook to update our distribution lists at the same time. What do you mean exactly? Can you give an example? Also what version of Outlook are you using? T. >-----Original Message----- >when we update our contacts how can we get outlook to >update our distribution lists at the same time. >. > That's what the Update Now button is for. -- Russ Valentine [MVP-Outlook] "wayne" <wayne@anandasacramento.org> wrote in message news:05ba01c3d4be\$e83a5e80\$a301280a@phx.gbl... > when we update our contacts how can we g...

difference between 2 lines in a scatter chart
I have created a scatter chart with two lines. The x axis is divided up into 10 parts and labeled 10, 20, 30, 40, etc. What I want to do is calculate the distance between the two lines between the x values of 40 and 60. Does anyone know how to to that? Thank you in advance. -- prd02003 ------------------------------------------------------------------------ prd02003's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25762 View this thread: http://www.excelforum.com/showthread.php?threadid=391727 How did you generate the lines? Are they from the data directly...

Print a list of movies
I have a spreadsheet with 2 columns (No and Name of Movie). There are 322 rows. I would like to print this so that I have the first 44 rows on the left side of the page and the next 44 rows on the right side of the page. I would like to keep this setup on all pages. These cells have color formatting and numbers that I need to keep with each cell. How do I do this? -- Terri Sprague www.tlsprague.com Hi Teri, See http://www.mvps.org/dmcritchie/excel/snakecol.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/...

changing the application name
hi, i have developed a project and after completion of that project there is need to change that applicatin name. I will explained clearly i have created a document./view project , which named has "aaaa" afte completion the project, by running that application, there is a frame which has title "aaaa" Now there is need to change that title to "bbbbb" how can we do that? i am using VC++.net plz let me know how to do that by, koti "Koti" <koti@nannacomputers.com> wrote in message news:OqHUbvNQFHA.1236@TK2MSFTNGP14.phx.gbl... >...

How to export users list from MS-Exchange AD Users&Computers?
Hello, I want to export my exchange mailbox-enabled users list with their Given Name+Surname and e-mail address.. I get this kind of list from ActiveDirectory Users&Computers but I can not export it.. I know that there is a program somewhere that does what I want, but I can't remember it's name.. Note: I don't mean LDIFDE tools.. Thanks for your help. Capricorn As always...be careful and test things in a lab first, please. LDIFDE tools can be pretty ugly... CSVDE will work and it is possible to limit the columns that are output by CSVDE. Note: If you have a large ...

Check if date is between two dates, then sum only those rows
I have tab1 with begin date in column A, end date in column B. Basically they are weeks, Mon-Sun (e.g. 01/04/2010 and 01/10/2010 in A/B resp.). Tab2 has data I'd like to count and sum. The transaction date is in column B. I want to sum tab2 column G (and a few other columns). Tab2 will be dynamic as I add records to it. Tab1 remains static with each week comprising one row. -- streetcar The best tool for it is a pivot table. Put in your layaut col A & B in row section, col G in Data section (double click to change it to sum - "sumarize by") Click yes...