Return next unique value from list based on multiple criteria

J

jozzy

New Member
Joined
May 20, 2009
Messages
21
G'Day Excel people's,

Background:
What I have is an Excel sheet set up as a monthly schedule. Currently a file number is allocated to each job manually in a separate Excel workbook and then this is manually inputted onto the monthly schedule. This is a cumbersome timely process (and who here loves typing out the same spiel multiple times!!!).
File numbers are based on two criteria; State order is placed from (manual entry) and System name (manual entry)

Task:
What I require is when I enter the two criteria; State and System Name I require Excel to return the next unique file number to a job from a list based on meeting the two criteria.

Hopefully I have made this easier by attaching a sample workbook (not sure how to attach so provided sample below instead) with the schedule and lists of unique file numbers. My guess is that what I am looking for is some form of Lookup, but I am really unsure how I can make Excel assign the next unique number from a list if the number above it has already been used.

Date RevisedEnteredAssemblyEx-WorksFILE No.JOB NAMEStateOrder #Job No.System
15/0731/072/08TD1Insert job name hereQLDSystem 3
15/071/082/08UC2000Insert job name hereNSWSystem 2
15/0728/072/08TD2Insert job name hereQLDSystem 3
#
#
#

<tbody>
</tbody>


System Type:System 1System 1System 1System 1System 1System 1System 1System 2System 2System 2System 2System 2System 2System 2System 3System 3System 3System 3System 3System 3System 3
State:VICACTNSWQLDSAWAEXPVICACTNSWQLDSAWAEXPVICACTNSWQLDSAWAEXP
File No.SV2000SE2000SC2000SD2000SG2000SF2000SX2000UV2000UE2000UC2000UD2000UG2000UF2000UX2000TV1TE1TC1TD1TG1TF1TX1
SV2001SE2001SC2001SD2001SG2001SF2001SX2001UV2001UE2001UC2001UD2001UG2001UF2001UX2001TV2TE2TC2TD2TG2TF2TX2
SV2002SE2002SC2002SD2002SG2002SF2002SX2002UV2002UE2002UC2002UD2002UG2002UF2002UX2002TV3TE3TC3TD3TG3TF3TX3
SV2003SE2003SC2003SD2003SG2003SF2003SX2003UV2003UE2003UC2003UD2003UG2003UF2003UX2003TV4TE4TC4TD4TG4TF4TX4
SV2004SE2004SC2004SD2004SG2004SF2004SX2004UV2004UE2004UC2004UD2004UG2004UF2004UX2004TV5TE5TC5TD5TG5TF5TX5
SV2005SE2005SC2005SD2005SG2005SF2005SX2005UV2005UE2005UC2005UD2005UG2005UF2005UX2005TV6TE6TC6TD6TG6TF6TX6
SV2006SE2006SC2006SD2006SG2006SF2006SX2006UV2006UE2006UC2006UD2006UG2006UF2006UX2006TV7TE7TC7TD7TG7TF7TX7
SV2007SE2007SC2007SD2007SG2007SF2007SX2007UV2007UE2007UC2007UD2007UG2007UF2007UX2007TV8TE8TC8TD8TG8TF8TX8
SV2008SE2008SC2008SD2008SG2008SF2008SX2008UV2008UE2008UC2008UD2008UG2008UF2008UX2008TV9TE9TC9TD9TG9TF9TX9
SV2009SE2009SC2009SD2009SG2009SF2009SX2009UV2009UE2009UC2009UD2009UG2009UF2009UX2009TV10TE10TC10TD10TG10TF10TX10

<tbody>
</tbody>


Should you require more info from me please don't hesitate to ask! :)

Cheers!



Excel 2003 / Windows XP
 
Sort by date Sort by votes
Marcelo Branco said:
Are you saying that for each system Category (System 1, System 2, System 3 etc, etc) there is a list of associates system names?

Is this?

If so, i *think* we can achieve a solution with an extra column...

M.
Click to expand...

Marcelo, that is correct!.......(Not sure who set this file naming database up but clearly they didn't think about it) :P
If worst comes to worst I can always request that each System has it's own unique file number.

Here is a true example what is required below.

When either of the System; 'Sys45, 'Sys30' or 'E45' is input into the System column the file number needs to continue on from the previous. What were you thinking when you mentioned the addition of another column?

Worksheet_1:

EnteredAssemblyEx-WorksFILE No.JOB NAMEStateOrder #Job No.System
15/0731/072/08SV2000Example<example< em=""></example<>VICSys45
15/071/082/08SV2001ExampleVICSys30
15/0728/072/08SV2002ExampleVICE45
SV2003ExampleVICSys45
#

<tbody>
</tbody>


Worksheet_2:

System Name:Sys45Sys45Sys45Sys45Sys45Sys45Sys45
System Name:Sys30Sys30Sys30Sys30Sys30Sys30Sys30I70I70I70I70I70I70I70
System Name:E45E45E45E45E45E45E45I90I90I90I90I90I90I90Sys70Sys70Sys70Sys70Sys70Sys70Sys70
State:VICACTNSWQLDSAWAEXPVICACTNSWQLDSAWAEXPVICACTNSWQLDSAWAEXP
File No.SV2000SE2000SC2000SD2000SG2000SF2000SX2000UV2000UE2000UC2000UD2000UG2000UF2000UX2000TV1TE1TC1TD1TG1TF1TX1
SV2001SE2001SC2001SD2001SG2001SF2001SX2001UV2001UE2001UC2001UD2001UG2001UF2001UX2001TV2TE2TC2TD2TG2TF2TX2
SV2002SE2002SC2002SD2002SG2002SF2002SX2002UV2002UE2002UC2002UD2002UG2002UF2002UX2002TV3TE3TC3TD3TG3TF3TX3
SV2003SE2003SC2003SD2003SG2003SF2003SX2003UV2003UE2003UC2003UD2003UG2003UF2003UX2003TV4TE4TC4TD4TG4TF4TX4
SV2004SE2004SC2004SD2004SG2004SF2004SX2004UV2004UE2004UC2004UD2004UG2004UF2004UX2004TV5TE5TC5TD5TG5TF5TX5
SV2005SE2005SC2005SD2005SG2005SF2005SX2005UV2005UE2005UC2005UD2005UG2005UF2005UX2005TV6TE6TC6TD6TG6TF6TX6

<tbody>
</tbody>
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I was expecting something like this...

A possible solution

1. Keep the big table exactly as your post #1 (same headers in row 2, i.e., System 1, System1....,System 2, System 2....)


2. Create a table like this

FileName
Category
Sys45
System 1
Sys30
System 1
E45
System 1
I70
System 2
I90
System 2
Sys70
System 3
...
...
...
...

<TBODY>
</TBODY>

Name the range, say, Files

**This will you give flexibility to add new File Names and new Categories, if needed***


3. In worksheet1 create a new column between the columns Job No. and System
Insert a VLOOKUP formula to get the Category of the file. Something like (assuming System will be column J)

=VLOOKUP(J2,Files,2,0)


Then you can use the first formula i suggested in #2 - maybe you need to adjust the ranges (columns)

Hope this helps.

M.
 
Last edited:
Upvote 0
correction: i meant

1. Keep the big table exactly as your post #1 (same headers in row 1, i.e., System 1, System1....,System 2, System 2....)

Not row 2

M.
 
Upvote 0
Marcelo Branco said:
correction: i meant

1. Keep the big table exactly as your post #1 (same headers in row 1, i.e., System 1, System1....,System 2, System 2....)

Not row 2

M.
Click to expand...


Thank you yet again!

This makes sense :)
............I will try this out when I get a spare minute later on, shall let you know the results!


Cheers!
 
Upvote 0
Marcelo Branco said:
1. Keep the big table exactly as your post #1 (same headers in row 2, i.e., System 1, System1....,System 2, System 2....)


2. Create a table like this

FileNameCategory
Sys45System 1
Sys30System 1
E45System 1
I70System 2
I90System 2
Sys70System 3
......
......

<tbody>
</tbody>

Name the range, say, Files

**This will you give flexibility to add new File Names and new Categories, if needed***

3. In worksheet1 create a new column between the columns Job No. and System
Insert a VLOOKUP formula to get the Category of the file. Something like (assuming System will be column J)

=VLOOKUP(J2,Files,2,0)

Then you can use the first formula i suggested in #2 - maybe you need to adjust the ranges (columns)
M.
Click to expand...


Perfect solution......works a treat! :D

Many thanks again Marcelo.


Cheers!

Sean
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
293
DanteAmor
DanteAmor
Z
  • Question
Count Distinct Values in Un-Filtered Table across Multiple Columns with 1 Criteria
Replies
0
Views
190
zero269
Z
M
  • Question
return multiple matching values into one cell based on multiple criteria in Excel
Replies
3
Views
291
MMM_84
M
W
  • Solved
TEXTJOIN return UNIQUE values with INDEXMATCH multiple criteria
Replies
2
Views
688
WhimsicalRaptor87
W
J
  • Solved
How to aggregate data based on multiple criteria?
Replies
8
Views
563
Jurez
J

Forum statistics

Threads
1,215,453
Messages
6,124,930
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top