computing a counter/counting column?

The following table (query) lists individuals once, twice, three times, or 
more.  I need to compute/derive the count (the progressive count - 
incremented by one for each person) of each entry for each person -- the 
count to be displayed in the computed/derived column which I will call 
"Attempt".  Fred is listed once.  The query should list a 1 in the Attempt 
column.  Jane is listed 3 times.  The query should list Jane's first entry 
wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the 3rd 
entry for Jane will have a 3.  Bill is listed twice.  The query should list 
Bill's first entry with a  1 in the attemp column.  The 2nd entry for Bill 
will have a 2 in the attempt column.   What would be the tSql to perform this 
kind of query?

create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), grade 
varchar(1))

--raw data
insert into #tmp1 (yr, fName, grade)
select 2007, 'Fred', 'P'
union all
select 2007, 'Jane', 'F'
union all
select 2008, 'Jane', 'F'
union all
select 2009, 'Jane', 'P'
union all
select 2007, 'Bill', 'F'
union all
select 2008, 'Bill', 'F'

select * from #tmp1

returns the following data set

1	2007	Fred	P	
2	2007	Jane	F	
3	2008	Jane	F	
4	2009	Jane	P	
5	2007	Bill	F	
6	2008	Bill	F	

I need the query to return a computed/derived column that I call Attempt:

1	2007	Fred	P	1
2	2007	Jane	F	1
3	2008	Jane	F	2
4	2009	Jane	P	3
5	2007	Bill	F	1
6	2008	Bill	P	2

Thanks,
Rich
0
Utf
12/1/2009 4:27:02 PM
sqlserver.programming 1873 articles. 0 followers. Follow

5 Replies
511 Views

Similar Articles

[PageSpeed] 45

With cte As
(Select rowID, yr, fName, grade,
  Row_Number() Over (Partition By fName Order By yr) As Attempt
From #tmp1)
Select rowID, yr, fName, grade, Attempt
From cte
Order By rowID;

Tom

"Rich" <Rich@discussions.microsoft.com> wrote in message 
news:05AC588E-38BD-4D31-B5EE-92820ADD04D0@microsoft.com...
> The following table (query) lists individuals once, twice, three times, or
> more.  I need to compute/derive the count (the progressive count -
> incremented by one for each person) of each entry for each person -- the
> count to be displayed in the computed/derived column which I will call
> "Attempt".  Fred is listed once.  The query should list a 1 in the Attempt
> column.  Jane is listed 3 times.  The query should list Jane's first entry
> wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the 
> 3rd
> entry for Jane will have a 3.  Bill is listed twice.  The query should 
> list
> Bill's first entry with a  1 in the attemp column.  The 2nd entry for Bill
> will have a 2 in the attempt column.   What would be the tSql to perform 
> this
> kind of query?
>
> create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), 
> grade
> varchar(1))
>
> --raw data
> insert into #tmp1 (yr, fName, grade)
> select 2007, 'Fred', 'P'
> union all
> select 2007, 'Jane', 'F'
> union all
> select 2008, 'Jane', 'F'
> union all
> select 2009, 'Jane', 'P'
> union all
> select 2007, 'Bill', 'F'
> union all
> select 2008, 'Bill', 'F'
>
> select * from #tmp1
>
> returns the following data set
>
> 1 2007 Fred P
> 2 2007 Jane F
> 3 2008 Jane F
> 4 2009 Jane P
> 5 2007 Bill F
> 6 2008 Bill F
>
> I need the query to return a computed/derived column that I call Attempt:
>
> 1 2007 Fred P 1
> 2 2007 Jane F 1
> 3 2008 Jane F 2
> 4 2009 Jane P 3
> 5 2007 Bill F 1
> 6 2008 Bill P 2
>
> Thanks,
> Rich 

0
Tom
12/1/2009 4:37:20 PM
Nice!   Works perfectly.  I confess that I am not up on Sql2005 (or higher) 
tsql enhancements.

In an effort to understand/appreciate what is happening here - what would be 
the workaround version of this for Sql2000 tSql?



"Tom Cooper" wrote:

> With cte As
> (Select rowID, yr, fName, grade,
>   Row_Number() Over (Partition By fName Order By yr) As Attempt
> From #tmp1)
> Select rowID, yr, fName, grade, Attempt
> From cte
> Order By rowID;
> 
> Tom
> 
> "Rich" <Rich@discussions.microsoft.com> wrote in message 
> news:05AC588E-38BD-4D31-B5EE-92820ADD04D0@microsoft.com...
> > The following table (query) lists individuals once, twice, three times, or
> > more.  I need to compute/derive the count (the progressive count -
> > incremented by one for each person) of each entry for each person -- the
> > count to be displayed in the computed/derived column which I will call
> > "Attempt".  Fred is listed once.  The query should list a 1 in the Attempt
> > column.  Jane is listed 3 times.  The query should list Jane's first entry
> > wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the 
> > 3rd
> > entry for Jane will have a 3.  Bill is listed twice.  The query should 
> > list
> > Bill's first entry with a  1 in the attemp column.  The 2nd entry for Bill
> > will have a 2 in the attempt column.   What would be the tSql to perform 
> > this
> > kind of query?
> >
> > create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), 
> > grade
> > varchar(1))
> >
> > --raw data
> > insert into #tmp1 (yr, fName, grade)
> > select 2007, 'Fred', 'P'
> > union all
> > select 2007, 'Jane', 'F'
> > union all
> > select 2008, 'Jane', 'F'
> > union all
> > select 2009, 'Jane', 'P'
> > union all
> > select 2007, 'Bill', 'F'
> > union all
> > select 2008, 'Bill', 'F'
> >
> > select * from #tmp1
> >
> > returns the following data set
> >
> > 1 2007 Fred P
> > 2 2007 Jane F
> > 3 2008 Jane F
> > 4 2009 Jane P
> > 5 2007 Bill F
> > 6 2008 Bill F
> >
> > I need the query to return a computed/derived column that I call Attempt:
> >
> > 1 2007 Fred P 1
> > 2 2007 Jane F 1
> > 3 2008 Jane F 2
> > 4 2009 Jane P 3
> > 5 2007 Bill F 1
> > 6 2008 Bill P 2
> >
> > Thanks,
> > Rich 
> 
> .
> 
0
Utf
12/1/2009 5:28:02 PM
Nevermind.  I kind of figured this out.

Thanks again.

"Rich" wrote:

> Nice!   Works perfectly.  I confess that I am not up on Sql2005 (or higher) 
> tsql enhancements.
> 
> In an effort to understand/appreciate what is happening here - what would be 
> the workaround version of this for Sql2000 tSql?
> 
> 
> 
> "Tom Cooper" wrote:
> 
> > With cte As
> > (Select rowID, yr, fName, grade,
> >   Row_Number() Over (Partition By fName Order By yr) As Attempt
> > From #tmp1)
> > Select rowID, yr, fName, grade, Attempt
> > From cte
> > Order By rowID;
> > 
> > Tom
> > 
> > "Rich" <Rich@discussions.microsoft.com> wrote in message 
> > news:05AC588E-38BD-4D31-B5EE-92820ADD04D0@microsoft.com...
> > > The following table (query) lists individuals once, twice, three times, or
> > > more.  I need to compute/derive the count (the progressive count -
> > > incremented by one for each person) of each entry for each person -- the
> > > count to be displayed in the computed/derived column which I will call
> > > "Attempt".  Fred is listed once.  The query should list a 1 in the Attempt
> > > column.  Jane is listed 3 times.  The query should list Jane's first entry
> > > wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the 
> > > 3rd
> > > entry for Jane will have a 3.  Bill is listed twice.  The query should 
> > > list
> > > Bill's first entry with a  1 in the attemp column.  The 2nd entry for Bill
> > > will have a 2 in the attempt column.   What would be the tSql to perform 
> > > this
> > > kind of query?
> > >
> > > create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), 
> > > grade
> > > varchar(1))
> > >
> > > --raw data
> > > insert into #tmp1 (yr, fName, grade)
> > > select 2007, 'Fred', 'P'
> > > union all
> > > select 2007, 'Jane', 'F'
> > > union all
> > > select 2008, 'Jane', 'F'
> > > union all
> > > select 2009, 'Jane', 'P'
> > > union all
> > > select 2007, 'Bill', 'F'
> > > union all
> > > select 2008, 'Bill', 'F'
> > >
> > > select * from #tmp1
> > >
> > > returns the following data set
> > >
> > > 1 2007 Fred P
> > > 2 2007 Jane F
> > > 3 2008 Jane F
> > > 4 2009 Jane P
> > > 5 2007 Bill F
> > > 6 2008 Bill F
> > >
> > > I need the query to return a computed/derived column that I call Attempt:
> > >
> > > 1 2007 Fred P 1
> > > 2 2007 Jane F 1
> > > 3 2008 Jane F 2
> > > 4 2009 Jane P 3
> > > 5 2007 Bill F 1
> > > 6 2008 Bill P 2
> > >
> > > Thanks,
> > > Rich 
> > 
> > .
> > 
0
Utf
12/1/2009 6:01:01 PM
Your DDL will not work.  IDENTITY table property is a non-relatioanl
count of phsyical insertion attempts (not even successes) into
physical storage.  It is not even a column.  If you use INSERT INTO
Foobar VALUES (..), (..),.. (..); this attempt count is non-
deterministic.  Same thing with your old-style UNION ALL list. SQL
does not work like a deck of punch cards or a mag tape.

You have no key.  You can never have a key, since all of the columns
are NULL-able.  What do those NULLs mean?  Think about what VARCHAR(1)
means.  If you had a relational design, it might look like this:

CREATE TABLE GradeBook
(course_yr INTEGER NOT NULL,
 test_nbr INTEGER NOT NULL
   CHECK (test_nbr > 0),
 first_name VARCHAR (50) NOT NULL, -- you did research this size?
 course_grade CHAR(1) DEFAULT 'F' NOT NULL
   CHECK (course_grade IN ('P', 'F')),
 PRIMARY KEY (course_yr, test_nr, first_name));

This is what you are trying to get as a result because of the failed
DDL.


0
CELKO
12/1/2009 7:38:26 PM
This sample does exactly what I need

Select yr, fName, grade,  
Row_Number() Over (Partition By fName Order By yr) As Attempt 
From #tmp1

note that I don't even need an identity column.  The sample ddl is just 
quicky test data to derive some Tsql to perform the desired operation.   
Eventually, this will be a subquery of a larger query for reporting purposes. 
  The actual data table(s) meet relational design standards.

Do you think that making my data set into a fact table and using mdx queries 
would be a better solution to my endeavor?





"--CELKO--" wrote:

> Your DDL will not work.  IDENTITY table property is a non-relatioanl
> count of phsyical insertion attempts (not even successes) into
> physical storage.  It is not even a column.  If you use INSERT INTO
> Foobar VALUES (..), (..),.. (..); this attempt count is non-
> deterministic.  Same thing with your old-style UNION ALL list. SQL
> does not work like a deck of punch cards or a mag tape.
> 
> You have no key.  You can never have a key, since all of the columns
> are NULL-able.  What do those NULLs mean?  Think about what VARCHAR(1)
> means.  If you had a relational design, it might look like this:
> 
> CREATE TABLE GradeBook
> (course_yr INTEGER NOT NULL,
>  test_nbr INTEGER NOT NULL
>    CHECK (test_nbr > 0),
>  first_name VARCHAR (50) NOT NULL, -- you did research this size?
>  course_grade CHAR(1) DEFAULT 'F' NOT NULL
>    CHECK (course_grade IN ('P', 'F')),
>  PRIMARY KEY (course_yr, test_nr, first_name));
> 
> This is what you are trying to get as a result because of the failed
> DDL.
> 
> 
> .
> 
0
Utf
12/1/2009 9:57:01 PM
Reply:

Similar Artilces:

How can I change the column order using VBA
I have written code that appends data to a table. Occasionally, I need to add more columns. To do this I am using a bubble sort to compare with my existing table weather i already have the column or not. Because of this, I make my new table in alphabetical order, but I would like to be able to reorder the columns into something other than alphabetical. Is there a column order property for tables? Any suggestions? Thank you. Balbina wrote: > I have written code that appends data to a table. Occasionally, I need to > add more columns. To do this I am using a bubble sort...

Question about ordering columns based on date
Hi, I have the following table and a slight ordering problem. Each of the rows A, B, C, etc has data associated with 5 fixed dates - from 20-Dec-09 to 20-Dec-18. In addition there is one "floating" column added to the end. In this example, it is the data for column 20-Dec-10. 20-Dec-09 20-Dec-11 20-Dec-13 20-Dec-15 20-Dec-18 20-Dec-10 A 84 49 88 91 26 82 B 9 68 39 89 50 59 C 45 5 46 69 41 38 D 35 28 70 69 47 59 The dates for the "floating" column always change - it could be any date before, after or in between the five fixed dates. I'd like to sort this data so that...

Need to put a name in the column heading
I am a new user and I cannot seem to put titles in the column headings. Thanks for your help. The help section was not of any use for the searches I made. If you mean that you want to replace the "A", "B", etc., the borders are not customizable. -- Jim Rech Excel MVP "windsorhartung" <windsorhartung@discussions.microsoft.com> wrote in message news:7C556A73-3230-4FB8-A9AA-EAA36E5B15C3@microsoft.com... |I am a new user and I cannot seem to put titles in the column headings. | Thanks for your help. The help section was not of any use for the searches ...

Counting a single value in a cell?
I want to count how often a single value (the dash symbol) occurs in a single cell than rank it. Showing two examples, AUS-IAH/PTY-IAH/AUS is in cell A1 and AUS-ORD-LHR/WAW-LHR-ORD/AUS is cell A2. Cell A1 has 2 dashes and A2 has 4 dashes. Is there a way to do this? Thank you. =LEN(B10)-LEN(SUBSTITUTE(B10,"-","")) for data in B10 -- Gary''s Student - gsnu200789 "Greg" wrote: > I want to count how often a single value (the dash symbol) occurs in a > single cell than rank it. Showing two examples, AUS-IAH/PTY-IAH/AUS is in > cell A...

Splitting Numbers in a Column #2
I certainly appreciate all the responses.... I have learned so much an thanks again... this site is so amazing.. -- jer10 ----------------------------------------------------------------------- jer101's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1076 View this thread: http://www.excelforum.com/showthread.php?threadid=26625 ...

Formula that returns column header after comparison
I have numeric data in 4 different columns. Each column has a different label. I want to compare the numbers in each column on a row by row basis and have the column label of the lowest value inserted in a different column at the end of the row. Anyone have suggestions on the best way to accomplish that? Thanks in advance! Cheers Drew Say your data is in A1 to D100, with labels in Row1. In say E2, try this formula: =INDEX($A$1:$D$1,MATCH(MIN(A2:D2),A2:D2,0)) Copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep ...

multipule calculations in a single column
Trying to total sum a range of cells in a column and subtract from a total in another cell in the same column, getting a value error ex sum cell a10 to a20 then subtract from the number in a5 excel 2007 -- Max Hi there. You may want to try: =SUM(A10:A20)-IF(ISNUMBER(A5),A5,0) Anyway, you should be sure that the value in A5 IS a number and not a string/text that 'SEEMS' to be a number. Tip: text/string are left aligned by default. Regards, Otávio "mexmex" wrote: > Trying to total sum a range of cells in a column and subtract from a tot...

Linking computer dates (time) to spreadsheet dates that have formu
Aloha I am hoping someone is out there who can assist me. I have spent two days trying to do two simple things in a medicine sheet I made up. It involves the dates. They need to change by the month, we have figured out the formulas that in itself was difficult. But NOW HOW do I TIE IN my computers Calendar (where the time clock) is TO the dates I have in the spreadsheet? So when the computer calendar changes the spreadsheet dates change per the formula that is in there? THank you very much Antoinette We probably need a bit more information on what you are trying to do, but ...

Outlook 2003 "none" in the received column
I was reading through a couple of posts about this but they all say this is caused by importing messages from outlook express. We do not use outlook express at all in our environment. Could there be another cause to this? I believe this is caused by one of the recent updates to Outlook, Outlook Express, and/or Internet Explorer. This problem is similar to one a client is having with .EML drafts stored on his desktop -- where the "To" field used to be blank (allowing entry of an address), it now says "none" in that field (and does not allow changes). I wish I could ...

Shared database not showing on all computers
Hi, thanks in advance for your assistance. We have 4 computers sharing a main database maintained on our server thru the Microsoft Exchanger Server, using Outlook 2007. I have full permissions to add/update remotely from the server . Problem is: each computer shows a different number of contacts in that same folder and recently added contacts are not visible. I have tried restarting Outlook and the computer, did not help. An extensive look online for assistance with this brought up nothing to even guide me in the right direction. I have checked sharing permissions and...

computer shutoff before online download of Money2005 standard
What do I do to download the program? I waited forever for Customer Support and they couldn't help. I did this last year with 2004. They can get you back to the original download location. It just takes forever. I will never upgrade online again. I insist on a CD for everything now. How do you feel about a career as a trained circus dog jumping through hoops? You'll find out soon. Good Luck "Bonnie" wrote: > What do I do to download the program? I waited forever for Customer Support > and they couldn't help. You have up to 5 download if you go to t...

how to count instances
hi there I have a report of about 10,000 training activities and I need to group/count them by individual i.e. i need to know how many times each person took training. i am completely new to access - any idea how to do this?? Creat a new query, add the required table(s) and fields. From the View menu while in design mode choose Totals. This will add a line in the lower portion called Totals. In each field that a grouping is needed mke sure the Totals line says Group y for that field. In the field you wish to count choose Count. You need to think this through first. Each grouping field will...

Display a list of unique values from a column with multiple instances
Hi- I have a list in a column (column A) that has many duplicate values. I am looking to create a list of the unique values from column A into another column (column F). I just want to display the values from col A into col F without deleting any values from col A. From there I am planning on doing a countif based on the value in col F to see how many duplicates I have. I want to do this dynamically because anyone can add a new value in col A but I don't want to have to manually compare unique values in col A against col F to do the counts. Col A contains ID numbers that can be repeat...

replicate column across worksheets
I am setting up a a shared workbook that will have a different user populating each of the different worksheets (6 worksheets in all). The first worksheet will contain at least two of its columns that will need to be replicated in the other worksheets so that the other users can then add extra data associated with their specific area of responsibility. Is there a way of replicating an entire column from one worksheet into another. I have tried using the =Sheet1! function in sheet2 but if I use this I need to paste the formula down across vacant cells in sheet2 so that when an new entry is m...

every second column
Hello Is there a possibility to show only every second column of the sheet in the chart - without hiding the columns in the sheet? Thank you Sincerely Lore Select the first column, hold CTRL down, select the second, third.... Make you chart best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lore Leuneog" <Lore.Leuneog@gmx.net> wrote in message news:d18ot4$kk$04$1@news.t-online.com... > Hello > > Is there a possibility to show only every second column of the sheet in > the > chart - without hiding the columns in the sheet...

Is there a way to use a multiple column array in SUMPRODUCT?
I am attempting to use SUMPRODUCT to pull values froma multiple column spreadsheet and add them together. I am able to get the SUMPRODUCT to work if I am only looking for values in one column, but as soon as I select all columns, the function no longer works. example: column A holds accounts, column B holds subaccounts, columns D - F (possibility of over 100 columns) hold companies I am looking for all values to be added together that hold values for both column A and column B =SUMPRODUCT(--(A2:A61=A44),--(B2:B61=B44)--(F2:F61)) works for just column F, but when I try ...

excel case changes on a column of first names
Could somebody please tell me how to work the "proper" formula so that it will change a column of names from all caps to just first letter cap? I can get the formula to work for the first cell, but I can't get it to copy or paste, or paste special to the column below it. (I don't use excel very often and therefore have limited knowledge of these functions.) Thank you! jim_in_mich Jim, One easy way is to insert a new column next to the column that has the non-proper (upper or lower) case words or names. Presuming that you'd have the names such as JOHN DOE in A1 a...

Can I split my worksheet and have different size columns and rows.
I like the main part of my spreadsheet, buy would like to add information with different size columns and rows from the original sheet. I want the original rows to remain the same but would like the addition to be smaller rows to get more info in there. Can I do it? tommy Row heights and Column widths are properties of the entire row and column and cannot be altered for different sections of a worksheet. I hesitate to mention the dreaded "merge cells" feature which allows you to merge two or more cells into one big cell. If you're just doing this for entering text you wo...

? CListCtrl::GetColumn To Get Column Name
Hi, I've written an app that's kind of like WinSpy/Spy++ to make adjustments to list controls. It can correctly set an app's list control's modes, alignments, sorting, styles, and extended styles. It can also get the widths of the columns and count of items. The problem is that I cannot seem to get the columns' names. My app uses code like this, where lc is a pointer to the target app's list control: . lc->GetColumnWidth(.); lc->GetItemCount(.); lc->ModifyStyle(.); lc->SetExtendedStyle(.); lc->GetHeaderCtrl()->GetItemCount(); . That a...

GP 9.0 Stock Count table update
What tables are updated in a Great plains stock count? Thanks. Stock Count ivStockCount IV10300 Stock Count Stock Count Line ivStockCountLine IV10301 Stock Count Stock Count Serial Lot ivStockCountSerialLot IV10302 Stock Count Stock Count U of M ivStockCountUofM IV10303 Stock Count Stock Count History IV30700 Stock Count Line History IV30701 Stock Count Serial Lot History IV30702 Stock Count IV10300 Stock Count Line IV10301 Stock Count Serial Lot IV10302 Stock Count U of M IV10303 Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get...

Copying Every Other Column
In Excel 2007, I have a spreadsheet with 14302 columns and I would like to copy everyother column without having to select them one-by-one... Any help would be greatly appreciated. TFTH, That's a job for VBA. Ask the nice folks down the hallway in the Excel 2007 for PC forum how to do that. This is the Excel for Macintosh group... Come to think of it, Mark Grimes has a solution here: http://www.peculiarities.com/code/excel.html Cheers On 24/05/10 12:37 PM, in article 24BC4CC1-80C8-47BD-8D98-7FE56E634EB0@microsoft.com, "tom" <tom@discussions.micro...

Please provide a statistic counter feature in publisher
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=91bfd504-640f-4a4c-bb4c-6b227cf5fd80&dg=microsoft.public.publisher You know, it might be a lot more helpful if you: a) Posted your suggestion in the co...

Select every Nth cell in column? Help please
3 columns A2:C100 contains data. In E2:E14, I want a formula or function that will get every 3rd cell from Column A, F2:F14 every 3rd cell from Col B, G2:G14 every 3rdcell from Col C E.g.: A B C D E F G Days Week Mon RDays RWeek RMon 1 2 3 7 8 9 4 5 6 4 5 6 7 8 9 9 8 7 10 110 12 3 3 3 1 2 3 4 5 6 7 8 9 12 11 10 9 8 7 6 5 4 3 2 1 3 3 3 I'm guessing Vlookup somehow, but I sure cant figure out how to do it. Appreciate any help you can provide. Basically, I'm just trying to get a smaller set of data to work with in Cols E:G versus using the data in Col A:C which currently i...

count colors in bitmap
Hi Is there a way that I can quickly count the number of unique colors in a bitmap using VB6? I have found some examples on the web which seem to work fine but they are all quite slow, taking about two seconds to count the number of unique colors in a 1600 x 1200 pixel bmp. Can it be done any faster than this? Charles "charles" <cbabbage59@yahoo.com> wrote in message news:273aa1c5-fb8a-47c5-b1ee-8fd191777a1b@u7g2000yqm.googlegroups.com... > Hi. Is there a way that I can quickly count the > number of unique colors in a bitmap using VB6? Yes. > ...

show first few letters of a column in another cell
I know how to use left to show the first character of a particular column in another column. What I need to do is show the first seven characters. For Instance: Column A has a value of "McDonald" I want Column B to show "McDonal" Thanks. =LEFT(A1,7) if A1 is your reference cell. -- HansM "Glenn" <nospam@yahoo.com> wrote in message news:O1l1Z7TOFHA.1040@TK2MSFTNGP12.phx.gbl... >I know how to use left to show the first character of a particular column >in > another column. > > What I need to do is show the first seven characters. >...