Preventing duplicates within two columns

Hi

I have been asked by a work colleague to design a spreadsheet for the issue 
of production logbooks. We use excel 2002 at work and the site policy is not 
to use VB within excel. The logbooks are issued by number and the initial 
version is set to version 1. For example a logbook will be issued as LB1234 
Version 1. on completion of Version 1, LB1234 Version 2 is issued. So, in my 
spreadsheet Column A Header is Logbook Number and Column B is Version Number, 
there are another 15 or so columns of information with these columns. My 
colleague wants to be able to enter the next available Version Number against 
a particular Logbook Number. I have argued that it would be easier to use 
autofilters and the sort the results with Version Number but I'm wondering if 
there is a way of using the Validation function. There are already 3600 
records and the average input is about 1000 rows of information per annum. I 
know it's screaming out for a database but my superiors insist we use 
spreadsheets as it's difficult to get databases validated. Can anyone help me 
please?
0
jacey2007 (2)
6/14/2008 8:47:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
252 Views

Similar Articles

[PageSpeed] 12

On Jun 14, 6:47 pm, jacey2007 <jacey2...@discussions.microsoft.com>
wrote:
> Hi
>
> I have been asked by a work colleague to design a spreadsheet for the issue
> of production logbooks. We use excel 2002 at work and the site policy is not
> to use VB within excel. The logbooks are issued by number and the initial
> version is set to version 1. For example a logbook will be issued as LB1234
> Version 1. on completion of Version 1, LB1234 Version 2 is issued. So, in my
> spreadsheet Column A Header is Logbook Number and Column B is Version Number,
> there are another 15 or so columns of information with these columns. My
> colleague wants to be able to enter the next available Version Number against
> a particular Logbook Number. I have argued that it would be easier to use
> autofilters and the sort the results with Version Number but I'm wondering if
> there is a way of using the Validation function. There are already 3600
> records and the average input is about 1000 rows of information per annum. I
> know it's screaming out for a database but my superiors insist we use
> spreadsheets as it's difficult to get databases validated. Can anyone help me
> please?

Maybe Custom validation...

After selecting from B2 down to the bottom of the data use this
formula in the custom Formula: box

=COUNTIF(A$2:A2,A2)=B2

Ken Johnson
0
KenCJohnson (314)
6/14/2008 1:40:49 PM
Many thanks Ken, 

John (jacey2006)

"Ken Johnson" wrote:

> On Jun 14, 6:47 pm, jacey2007 <jacey2...@discussions.microsoft.com>
> wrote:
> > Hi
> >
> > I have been asked by a work colleague to design a spreadsheet for the issue
> > of production logbooks. We use excel 2002 at work and the site policy is not
> > to use VB within excel. The logbooks are issued by number and the initial
> > version is set to version 1. For example a logbook will be issued as LB1234
> > Version 1. on completion of Version 1, LB1234 Version 2 is issued. So, in my
> > spreadsheet Column A Header is Logbook Number and Column B is Version Number,
> > there are another 15 or so columns of information with these columns. My
> > colleague wants to be able to enter the next available Version Number against
> > a particular Logbook Number. I have argued that it would be easier to use
> > autofilters and the sort the results with Version Number but I'm wondering if
> > there is a way of using the Validation function. There are already 3600
> > records and the average input is about 1000 rows of information per annum. I
> > know it's screaming out for a database but my superiors insist we use
> > spreadsheets as it's difficult to get databases validated. Can anyone help me
> > please?
> 
> Maybe Custom validation...
> 
> After selecting from B2 down to the bottom of the data use this
> formula in the custom Formula: box
> 
> =COUNTIF(A$2:A2,A2)=B2
> 
> Ken Johnson
> 
0
jacey2007 (2)
6/14/2008 6:09:00 PM
On Jun 15, 4:09 am, jacey2007 <jacey2...@discussions.microsoft.com>
wrote:
> Many thanks Ken,
>
> John (jacey2006)
>
> "Ken Johnson" wrote:
> > On Jun 14, 6:47 pm, jacey2007 <jacey2...@discussions.microsoft.com>
> > wrote:
> > > Hi
>
> > > I have been asked by a work colleague to design a spreadsheet for the issue
> > > of production logbooks. We use excel 2002 at work and the site policy is not
> > > to use VB within excel. The logbooks are issued by number and the initial
> > > version is set to version 1. For example a logbook will be issued as LB1234
> > > Version 1. on completion of Version 1, LB1234 Version 2 is issued. So, in my
> > > spreadsheet Column A Header is Logbook Number and Column B is Version Number,
> > > there are another 15 or so columns of information with these columns. My
> > > colleague wants to be able to enter the next available Version Number against
> > > a particular Logbook Number. I have argued that it would be easier to use
> > > autofilters and the sort the results with Version Number but I'm wondering if
> > > there is a way of using the Validation function. There are already 3600
> > > records and the average input is about 1000 rows of information per annum. I
> > > know it's screaming out for a database but my superiors insist we use
> > > spreadsheets as it's difficult to get databases validated. Can anyone help me
> > > please?
>
> > Maybe Custom validation...
>
> > After selecting from B2 down to the bottom of the data use this
> > formula in the custom Formula: box
>
> > =COUNTIF(A$2:A2,A2)=B2
>
> > Ken Johnson

You're welcome John.
Thanks for the feedback.

Ken Johnson
0
KenCJohnson (314)
6/14/2008 11:17:55 PM
Reply:

Similar Artilces:

Filtering a column to exclude any repeated entries.
Hi, I am working on a column that has the same entries 2 or more times. Is there a way (copy-paste or other) to get only unique entries from this column (that is to exclude repeated entries)? Any help apprieciated, Thank you. If you go to Data/Filters/Advanced Filters there is a box at the bottom left that you can tick to return unique entries only. >-----Original Message----- >Hi, > >I am working on a column that has the same entries 2 or more times. Is there >a way (copy-paste or other) to get only unique entries from this >column (that is to exclude repeated...

column width setting
Hello all. I'm currently working in Excel and have a spreadsheet made up of lots of smaller sheets. Within one 'tab' (sheet) I have set up multiple page breaks. I adjusted the column and row width on the first page (as defined by my breaks). I do not want the second and third and fourth and so on pages to have the same column/row width/height as the first page however, when I am in the second page and go to ajust these it also changes the first page- despite the page breaks (which are set up to break horizontally and vertically). Any suggestions? Column widths and row heights...

Difference between two dates
Is there a way in which i can determine the difference in date between the date stored in a field and the end date of a query request? example: Field has date of 1/1/07. Query asks for a date range. Date range given is 1/1/07 - 1/31/07. I want to be able calculate the difference between the end date and the field date If the field name is Field1, and the query asks for a parameter named [Wot End Date], use: DateDiff("d", [Field1], [Wot End Date]) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply t...

Combo Columns
I've created a combo box on a form in Access using 2 columns. The first column is hidden so the second column is the only one displayed in the combo box. When I then use that combo as the source in a separate text box the answered returned is the first column. Any idea how I get the second column information instead? -- Cheers. Paul ...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

outlook 2007 monthly calendar six column?
Just converted to Outlook 2007 from 2003, where I could print a monthly calendar with 6 columns: Mon Tue Wed Th Fri Sat/Sun. This freed up some width per column, b/c the weekend days were consolidated. Can't seem to do this with '07. The columns are too skinny (even on landscape) and I can't read appts. Advice? Thx Try the calendar printing assistant or word template- see http://slipstick.me/calprint for links. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ ...

How to prevent auto-open
Hello all--just a quick question from a frustrated Excel user. How do I prevent files from automatically opening when I initially open the Excel Program. I consistently have 2 files that open whenever Excel is initiated!!! Driving me crazy! :eek: Short of deleting these files, which I would prefer not to do, does anyone know how I can prevent this from happening? Thanks a bunch!!! christa0 -- christao ------------------------------------------------------------------------ christao's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26889 View this thread: h...

Combine 2 rows if name is same in Column B & C on both
Combine 2 rows if name is same in Column B & C on both 2 spreadsheets - Sheet 1 is bigger with extra names in column B & C Lastname Firstname Both - Column B & C Lastname Firstname - both sheets Sheet 1 has data in Col. D & E Sheet 2 has data in Col. F & G Sheet 1 has extra names not in Sheet 2 If Sheet 1 B&C = Sheet 2 B&C , then add F&G columns from sheet 2 , behind D& E columns on sheet 1 , for the match of names in Column B & C Thanks kerns.walter@epa.gov On Nov 13, 12:50 pm, wk <kerns.wal...@epa.gov> wrote: > Combine 2 rows if name...

Password for rows and columns
Is there a way to hide a row & column with a password in a worksheet, in such a way that the other user should be able to work on the same worksheet but with the remaining rows & columns, without being able to open the hidden row & column Hi As standard all cells are set to be protected, when you protect a sheet. Select the cells, rows or columns where other users shall be able to enter data, then goto Format > Cells > Protection : Remove the check in "Locked". Hide the desired column / row. Protect the sheet (with password). Now the user can enter data as ...

stacked column with total
I created a stacked column chart with 2 series. I'd like to show the total value on top each bar. Right now, show value displays each value of independently. For example, I have a bar showing 3 and 2 stacked but I would like to show 5 (3+2) on the top bar. I've seen on someone graph before. I can't recreate it. Pls help. Thanks Mat Mat Check here http://andypope.info/charts/StackColTotal.htm -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "matelot" <matelot@discussions.microsoft.com> ...

Outlook 2002: Duplicate incomming e-mails from ISP
Hello fellow MCP's: I have a problem with both of my wifes e-mail addresses from my ISP in that she gets 2 identical e-mails when she accesses her accounts using Outlook 2002 (at level 10.4712.4219 SP-2). If I go to the Web Mail for her accounts at Earthlink, it shows only one message out there so it looks like either Outlook is picking it up or some setting is wrong. I also have my own 2 e-mail accounts on Earthlink as well. I access my e-mail from a different computer with no problem using the same level of Outlook. The system that is having the porblem os running W2K with...

Preventing Recipient Names from Showing
I have a long list of people I am addressing a message to. I want the recipient to see that it is coming to them, but not be able to see anyone else's name/address that it's being sent to. I've tried the BCC field but then even the recipient does not see that it was sent to them. I'm afraid they will think it is junk mail if they don't see it addresses to themselves. Check out http://www.slipstick.com/addins/mail.htm#massmail for other options -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick...

Duplicate payees #3
In Money 2002, when I select a payee already on my list to add a transaction (ex. 'Wal-Mart'), the application will sometimes, not always, add another instance of that payee. In other words, if I add 5 transactions for Wal-Mart, I end up with 5 separate listings of Wal-Mart under my payee list! I never had this problem with Money 98 . . . Any ideas would be appreciated. I'm using an OEM (Gateway) supplied version. .. On Thu, 18 Dec 2003 12:13:24 -0800, "Carl" <cmaynard1@cox.net> wrote: >In Money 2002, when I select a payee already on my list to >a...

Max of value from DataGroup2 within DataGroup1
Hello - I have a table with data as follows below. I am trying to build a query that will give me the record with MAX of specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum [DataGroup1]. ID TestNum TestFreq specDiffMax -------------------------------------------- 4889683 Test 1 1710 3.669998 5123289 Test 1 1710 2.882999 4817314 Test 1 1710 3.102001 5134007 Test 1 1710.2 3.573002 4896056 Test 1 1710.2 3.355 4914480 Test 1 1710.2 3.515999 4889685 Test 2 1710.4 3.333 4896057 Test 2 1710.4 3.450001 4914481 Test 2 ...

Lowest entry in a column
Hi everyone, Can anyone tell me how to automatically use the last/lowest entry in a column? I don't want to sort the cells, or choose the Maximum or Minimum - I just need to use the bottom entry in a column automatically in a formula I'll create somewhere else on the spreadsheet. It thought it would be in the functions list somewhere, but it has eluded me! Thanks, Astley Suppose A is the column in question, use the following formula to refer to the last cell: =INDIRECT("A"&COUNT(A:A)) Mangesh "Astley" <ast@exemail.com.au> wrote in message ne...

unhidden columns keep hidden when opening file
When working in an Excel 2002 spreadsheet that had hidden columns. I unhide a column and enter info into that column. I then save and exit Excel. Upon opening of the spreadsheet again, that columns that I had unhidden are now hidden again. This happens not every time, but ofter enough to be problem. As a side note, the files all seem to be ones that were converted from Lotus. Any help would be appreciated. I've never seen this in xl2002. (But I don't have workbooks that were once Lotus 123 files, either.) Any chance you have a macro that adjusts the columnwidth? Randy Lyle ...

Locating row with no duplicate matching amount
I have a spread sheet with 4 columns and 900 rows I am trying to identify the rows that do not contain a duplicate amount. For example I have rows with 100 100 97 97 96 96 98 101 I am trying to pull the list of 98 and 101. The spreadsheet is a list of invoices paid vs invoices unpaid if it is paid there are two rows with the same data if it is unpaid there is only one row. I am trying to find the unpaid invoices. Any help would be appreciated. Here is one way... Assuming your invoice numbers are in column A, in the first free column enter =COUNTIF(A:A...

Search for DUplicates
I have excel spreadsheet that has 3000 names in one column . Is there a way to have it search for duplicates and move the duplicates to another sheet ? Thanks Ken Take a look at http://cpearson.com/excel/duplicat.htm In article <ODy0fzZkDHA.360@TK2MSFTNGP12.phx.gbl>, "Ken" <kenmal@hotmail.com> wrote: > I have excel spreadsheet that has 3000 names in one column . Is there a way > to have it search for duplicates and move the duplicates to another sheet ? > Exactly What I wanted Thank you very Much "J.E. McGimpsey" <jemcgimpsey@mvps.or...

Summing Duplicates
Following is a sample of data exported from our DOS-based order entry system in CSV format: PART# QUANTITY LIST SALE PRICE PRICE OP-AHS-100 2 Each $46.75 $37.40 OP-AHS-025 1 Each $13.00 $13.00 OP-PRG-100-H 2 Each $44.50 $44.50 OP-PRG-025-H 1 Each $12.50 $12.50 AP-AHS-025 1 Each $75.75 $75.75 AP-PRG-025 1 Each $12.50 $12.50 AP-SSS-025 1 Each $0.00 $0.00 * 0 $0.00 $0.00 * ...

looking for duplicates & unmatched between two columns
Hi - I'm looking for a function that will look at a cell (containing text) in one column and then look to a range of cells in another column and if a match is found to indicate "1" if not found to indate "0" ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** Hi have a look at http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon -- Regards Frank Kabel Frankfurt, Germany "Michael" <mweber@ulr-ins.com> schrieb im Newsbeitrag news:4072cbf6$0$202$75868355@news.frii.net...

Duplicate calendar entries
Hi, I recently tried to delegate my calendar requests to my assistant, and selected that she receive a copy of my meeting invites. However, when doing so, i ended up getting duplicate entries in my calendar. I suppose this is fair enough given she had a copy and so did I. But I would have expected that when she has accepted on my behalf, that Outlook would be smart enough to remove the second invite, but obviously not. This being the case, what the heck is the point of this feature? Cheers, Pete ...

How do you password protect a single cell or column of cells
I am trying to password protect a column of single cells but it keeps protecting the whole work sheet can ant one help? Select all the column not to be protected; Use Format | Cells | protection and deselect the Lock box Now Tools | Protect | worksheet Does this give you what you want? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Kinnaird" <Kinnaird@discussions.microsoft.com> wrote in message news:0D46DB91-B014-492A-94D8-24D5190CD154@microsoft.com... >I am trying to password protect a column of single cells bu...

How to Unhide and Use Columns to the right of the worksheet
I wanted to hide columns AP and everything to the right of it in my worksheet. So I selected column AP hit CTRL-SHIFT-RIGHT KEY and I could confine the visible area of my worksheet to A-AO So far so good. I now want to be able to insert columns between A and AO. I get a message saying that it cant shift non-blank columns off the worksheet (which is now supposedly confined to A-AO. I inserted the line ActiveSheet.Range("AP1:AZ1").EntireColumn.Hidden = False in my Worksheet_SelectionChange macro and found that only column AP was visible, but that I couldnt select it. I still can...

compare two versions of project
using project 2007, 1- open two versions of same project with slightly different names 2- if i click on the right side compare projects toolbar, i get the add/remove buttons option, but the compare project versions is grayed out. Why is this grayed out and how do i fix? thx stevo Hi stevo, I'm not sure I follow. Does the compare project versions button on the left side of the toolbar not work? I don't believe you can customize the compare project versions toolbar. The toolbar should have five buttons: Compare Project Versions Go to Task/Resource in projec...

make a Year to date column
Can someone please suggest how I can get the year to day column display Nothing if there is not anything in the preceding column? Eg... Column K is YTD. Column J is Invoice total. I figured out how to hide the zeros in the J column... but can't figure out how to make K show nothing until there is a figure in J. Its a simple sheet. Just adds invoices.. and provides a YTD figure. But looks DUMB when the last YTD figure is carried all the way down the column. Thank you ... Try something like this in say, K2, copied down: =IF(J2="","","<yourCumuYTDformul...