Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 21st, 2006, 01:06 PM
Authorized User
Join Date: Aug 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Lookup max with criteria in multiple columns

From To Number * Rev
A B 1 -
A C 1 -
A D 1 -
B A 1 -
B A 2 -
B A 1 A

* This column to auto assign

Here is the spreadsheet that I am working with. What this spreadsheet is used for is to check out a document number. The document number is in the format from-to-#-rev. The user select who the document is from, who it is going to and the gets the next sequential number for that from-to combinations. What I would like to do is auto assign the next sequential number. The snag comes into place when you take into consideration revs. Therefore you can not just count the from-to combination and add one for the next number. Some how I have to "match" the combination in the columns and find the max value. Any suggestions?

Old September 5th, 2006, 04:02 AM
SMI SMI is offline
Authorized User
Join Date: Jul 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

Please explain the purpose of Rev column and how it going to behave. Also note in your chart, letter A is put in Rev column but all rows above that are blank. What does it mean?

Genius is 99% perspiration and 1% inspiration
Old September 6th, 2006, 05:48 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts


If I understand correctly you are looking to auto assign the number column based on the number of preceeding instances in the list of From - To - Rev.

If this is the case then you can achieve your result in a couple of ways.

Firstly, you could add a new column alongside the existing table (assume demo table is in cells A1:D7) in column E. The formulae in this column should concatenate the From - To - Rev combination on each row (i.e. formula in cell E2 is =CONCATENATE(A2,",",B2,",",D2)). Yopu then need to count the preceeding number of instances of each unique combination plus one in each row for the number (i.e. formula in cell C2 is =COUNTIF(E$1:E1,E2)+1).

Alternatively, you could use an array formula. This will dispense with the need to add a new column but will leave the number formula more complex. In this instance the formula you need to type into cell C2 is {=SUM((A$1:A1=A2)*(B$1:B1=B2)*(D$1:D1=D2))+1}. The curly brakets indicate that the formula is an array formula, you can't type these brakets in but rather you must enter the forula as typed without brakets and then exit the cell by pressing Crtl + Shift + Enter.

Hope this helps,

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple criteria for a Report stealthdevil Access VBA 33 June 8th, 2006 10:38 AM
DTS - Multiple Column Lookup Problem asimahmed SQL Server DTS 2 April 4th, 2006 01:58 PM
Sum up columns with different criteria pontitt2 SQL Language 5 May 11th, 2004 02:31 AM
database lookup functiod (multiple rows) amitagg Biztalk 0 April 12th, 2004 07:20 PM
Calculate de max value of two columns cdias Oracle 1 November 26th, 2003 03:23 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.